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.

[Divinus Arma]

Have you tried using Auto filter on Excel?
Go to data, then go to filter, then check autofilter. Once the drop down arrows show up, pull down the selection menu for the specific column you want to check and select 'x'.

Auto filter only works in the columns. So it depends on how you need them grouped.

For example:



The first excel spreadsheet shows the complete list of units and provinces.

The second excel spreadsheet has "hastati" with x selected in autofilter, and shows all provinces where that may be recruited.

The third excel spreadsheet has "barbarian" with x selected in autofilter, and shows all provinces where that may be recruited.

By doing this, you show only the provinces where that specific unit may be recruited.


Alternatively, if you have units in rows and provinces in columns, Then your spreadsheet would look like this:



Then your auto filter would create groups by unit.

The first spreadsheet shows all units and provinces available.

The second spreadsheet shows Province 2 with x selected in autofilter, and shows all units that may be recruited.

The second spreadsheet shows Province 4 with x selected in autofilter, and shows all units that may be recruited.

In this case you show units that may be recruited in that province.


Now, if you want to group by a third category,

[ScionTheWorm]

the x'es is a problem, since there is no way to show skipping a province. Is this how the spreadsheet is?



[EMPTY] A B C D E F G H I J ....
UNIT U x x x
UNIT V x x x
.
.
.

Can there be:
1.) Multiple words in province name or unitname? If so it will be problematic when working in a textfile. Unless everything is separated by a TAB and only a tab.

Also, about the optimizing I thought of this simple algorithm:

for each unit do {
current_group = group1
for each provinceassignment do {
have group1 assigned this province already to another unit? if yes, current_group = group2 (make a new group if all is taken)
}
assign unit to current_group
}

there should probably be some specific sorting for the units, so it's optimized. it's just the first way i could think of...

I would choose python for it's fast implementation and it's support for strings

[Divinus Arma]

As I was saying, if you want to group by a third category, just change 'x' to a letter, with that letter representing a combination of buildings.

For example:



In this example, each letter is a combination of buildings

a = barracks level 1
b = barracks level 2
c = barracks level 3
d = barracks level 4
e = barracks level 1 and stable level 1

Now just autofilter by province and select the building combination (the letter: a, b, c, etc.) you want and it will show you the unit that may be recruited in that province with that building combination.


Let me know if this helps at all or if I am way off the mark.

[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.