Inserting Unique Identifier

[odonovanc]

Ok, I have a large set of data and the values in the rows repeat themselves but they mean something different based on other factors. Here is a sample of what I am looking at:


Loc Item
1 A
1 B
1 A
1 B
2 A
2 B
2 A
2 B
2 A

So I want a different value assigned to the first row that has 1 and A vs the second time 1A shows up. Is there a formula that I can write, say in column c that will add some kind of unique identifier?

 

[Joe4]

Welcome to the Board!

Why not just each record its own ID? (If so, you can just use the ROW() function to return the row number, which will be unique for each one).
Or should some be the same (if so, which ones, and why)?
It might be helpful if you also post your expected output, so we can see the pattern.

 

[Fluff]

How about
=A1&B1&COUNTIF(B$1:B1,B1)

 

[odonovanc]

This will be coming from a data source I do not control, but I want to run reports off it every month and am trying to keep the unique identifier the same. So in my above example, the first 1A is in row 2, next month the report may have it in row 3. So the row formula will not work.

The 1A will always be in the same order though. So the first instance of 1 and A represents one data point and will always be before the second instance of 1 and A. I don't really care what the output looks like, as long as its unique and I can run a sumif or vlookup off of it. It can be 1A1, 1A2, etc. Each of these repeating cells will happen 5 times for roughly 20 times for roughly 150 total groups (15000 rows).

Hope that makes sense.

 

[odonovanc]

How about
=A1&B1&COUNTIF(B$1:B1,B1)

This seems to be working. You are awesome. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[odonovanc]

This seems to be working. You are awesome. Thank you.

Second thought, this is not exactly what I am looking for.

Your formula is returning 1A1, 1A2, but then 2 will be 2A3. Any way I can make that 2A1?

What is B this time may be C next time so the reference will not hold up in future months reports. I need the rows to have unique identifiers that stay consistent month to month without relevance to the order they appear in the workbook.

 

[Fluff]

How about
=A1&B1&COUNTIFS(A$1:A1,A1,B$1:B1,B1)

 

[odonovanc]

How about
=A1&B1&COUNTIFS(A$1:A1,A1,B$1:B1,B1)

Looks perfect. Thanks again. I think I got it now.