Match two and more condition and creat unique value

[MaBend]

Hello, I have excel with two columns: one is a city, the seconde one is a name of river. I need to find a intersection and get an unique value for each group. Each group is created from the same river and particular cities. If city has two rivers, this should be in the same group. But also the second river and respective city should have the same value as the first river. ... and again. If there is a city with another river. This should be same number as the first and second river. For the second group with unique number in third column (e.g.2), the same rules apply...the same river=same number. Same city = same number. One city has two river. The second river has the same number but also cities where the second river is...and so on. Here is and example. I have more then 10 000 values. Any idea how to do it? Many thanks.

 

[teylyn]

It's not quite clear to me what the result should look like. Could you manually mock that up and post it here? The description is a bit confusing.

maybe a pivot table is what you are after?

 

[MaBend]

I manually write the results in the third column. Hope it will be more helpful. E.g the first group 1: river Danube + all cities, where Danube runs. But see Belgrad, there is Sava aldo. Therefore also river Sava and all respective cities, i.e. Zagreb should have number 1. Then there is group 2: River Spree + Berlin+ Usti, But also river Elbe. And third group is made from Tiber and Rome because there are no more cities where Tiber runs through it. As I said I have a lot of data, so do it manually is really hard. Many thanks for your reply.!

 

[teylyn]

Let me rephrase your question:

If the city has been listed before the current row OR the river has been listed before the current row, keep the same group number in the current row.
If the city has not appeared before AND the river has not appeared before the current row, increment the group number.

That is my interpretation of your question and this is the formula that I have come up with.

=IF(COUNTIF($A$1:A2,A3)+COUNTIF($B$1:B2,B3),C2,C2+1)

Manually type the first group number into cell C2. It will always be a 1. (I could build that into the formula, but that would make it more complex than it needs to be).

Put the formula into cell C3 and copy down.

 

[MaBend]

Yes, this is the point. But what about the case, when the group is not together. I have tried your formula a it works. But I have realized I have some cases in the middle of my dataset. So I am not able to indentify them manually. So I need to use formula like vlookup to find cities or river that should be in one group. See example:

 

[teylyn]

That is difficult. A number can only be incremented from a start value. If the start value is looked up with a VLookup, then several new combinations could result in the same incremented number.

The easiest way to solve this is to sort the data, so the formula can work from top to bottom.

Your screenshots are not from Excel for desktop. If you can use Excel for Windows or Mac, then this could be solved with VBA code, but if you use Excel online or on a mobile device, then you can only use formulas and that will require massaging the data so the formula can work.