Request for assistance - Data comparison tool?

[khelvan]

Ok guys, I'm having a bit of a problem, maybe you can help.

I have a spreadsheet with a list of units, and a list of provinces. Each row is a unit, and each column is a province. In every province the unit may be recruited, I have an 'x.'

What I need to do is compare these rows against each other, and come up with groupings where the provinces don't overlap. Let me explain:

Unit U can be recruited in provinces A, B, and C.
Unit V can be recruited in provinces D, E, and F.
Unit W can be recruited in provinces G, H, and I.
Unit X can be recruited in provinces A and F.
Unit Y can be recruited in provinces B and H.
Unit Z can be recruited in provinces E, F, and I.

I need to group as many together as I can. So group 1 will have units U, V, and W. Group 2 will have units X and Y, and group 3 will have unit Z.

Now let's assume I have 200 units, and 200 provinces. Trying to match them into groups by eyeballing a comparison is tiring, tedious, and extremely time consuming. Does anyone know of any way using Excel, or by exporting to another program, that I can run this comparison automatically? To find the most efficient way to stick these units together in groups?

Thanks!

[khelvan]

Failing finding a program to do it, if someone has the time and inclination to help me complete this before the weekend is out, I could use the help.

[Batahr]

If I were to do this, I would export the data to a program like Matlab and write a little algorithm to find a solution to the problem. I don't think Excel has enough tools to solve a problem like this, but I not an Excel expert.

About the problem: you're aware that the problem as you stated it has multiple solutions? In the example, for example, the grouping {U, Z}, {V, Y} and {W, X} is another solution that only has 3 groups. I don't know if this is an issue though (because I have no clue where you need the groupings for).

I'm more than willing to help you out on this, as I have the required tools, knowledge and time (and love for EB ).

[jerby]

and a new member was born

[ScionTheWorm]

I guess Batahr is already helping, but if not I can easily do it. Just press the PM

[Divinus Arma]

I am going to assume that each spreadsheet represents a faction and that the grouping is a third category qualifier, most likely your building complex recruitment criteria.

The question is: Do you need the grouping to occur within the province data category, or do you need it to occur across the spreadsheet?


I have time available if you need a slave. I work only two days in the next week and have a break before my last class.

[Moros]

Ok guys, I'm having a bit of a problem, maybe you can help.

I have a spreadsheet with a list of units, and a list of provinces. Each row is a unit, and each column is a province. In every province the unit may be recruited, I have an 'x.'
...
Thanks!

I don't understand what you're trying to say but what progral do you use or in what type of file is the data stored, .txt?

[khelvan]

The second scenario Divinus Arma describes is most accurate. I'll see if I can use the option to group things appropriately.

If not, please contact me via PM if anyone thinks they can handle this problem quickly and efficiently.

[Divinus Arma]

Khelvan and Co,

I sent you a couple emails. I have a few important questions to confirm that I understand the grouping correctly. I can't really dig in until I get these questions answered. And I sure as hell won't post the questions here. I like to keep my balls, thanks very much.

I would have used the hidden forum, but I am not a member.

Thank you. Proud to help.