CODEWITHSUNDEEP
excelunique
Kresten
Kresten

Reputation: 838

HOWTO ad a unique id to each unique row?

I have data in two columns: 

a  1
a  1
a  2
b  3
b  4

In the list there is 4 unique rows. I would like to ad a unique id to each unique row. Like this:

1  a  1
1  a  1
2  a  2
3  b  3
4  b  4

Of course I have many more rows and columns and date are more complex than in this example.

Anyway to do this i excel? Mvh Kresten Buch

Upvotes: 0

Views: 6957

Answers (4)

user9842437
user9842437

Reputation: 11

I deal with this issue all the time when structuring a data set into panel data. say you have multiple columns of data, and each are unique based on the name of someone, like:

ANNE
ROSE
ANNE
FRANK
TOM
ROSE
ANNE

but instead of having each column related to Anne, Rose, Frank, or Tom, you want it to look like this:

1 ANNE
2 ROSE
1 ANNE
3 FRANK
4 TOM
2 ROSE
1 ANNE

So that each name now has a unique numerical identifier that can be used in place of the name.

Make a pivot table of your data and only place the column that has the names (or whatever the identifier may be) into the Rows section. This will single out all the different names used within the dataset. Copy and paste this pivot table anywhere on the sheet so that the names are in actual cells and not off of a pivot table. To the right of the names, enter 1 next to the first name, and then =B1+1, and so on so that you number each name with a unique value; then copy and paste this column as numbers so that their formulas are erased. Finally, just go to your original dataset and perform a VLOOKUP so that the names get attached with whatever unique value was assigned off of the pivot table. Make sure to copy and paste as numbers once done to remove the VLOOKUP formula.

Takes literally 2 minutes to do, depending on size of dataset, and is very easy. It will work perfectly every time.

Upvotes: 1

thomas
thomas

Reputation: 21

I have the same issue, I have developed a three formula approach to this. I could probably concatenate it if I nested them, but whatevs, this works.

Assume the data you want to 'number' is in column A, and the first row of the table is row 3. The first column (in column B) counts occurrences of the 'value' and the range expands from the top of the table downwards as the table grows: =COUNTIF($A$3:A3,A3)

the second column's formula also expands as the row count does, and simply adds 1 to the transaction count every time it encounters a 1 (ie first occurrence of a new unique value) in column B =IF(B3=1,MAX($C$2:C2)+1,"") This one worked for me even in the first row of the table btw - i was expecting to have to manually input a 1 to start the list. Having it work without the manual entry is a good thing, it means the formmulas all work even if you resort the table data into a different order.

The third one in column D uses a vlookup to find the value. Note that when vlookup finds more than one number, it always pulls the first occurrence. =VLOOKUP(A3,$A$3:C3,3,FALSE)

Note that this will renumber all the data outcomes dynamically if you do resort the entire thing. ie the formulas all work, but the number 'assigned' to a praticular set of data might be different, as it all works from whatever order the list of items is in.

My use case for these formulas assumes that every month i paste a new set of data to the bottom of the table, some items of which are repeats from previous months - ie are already in the table, and some of which are new.

if the dynamic renumbering is a problem, use a 'row key' so you can resort back to the original order at the end.

Upvotes: 2

Jerry
Jerry

Reputation: 71538

If your data is not sorted, it's more complicated... but you can use something like this in A2:

=IF(COUNTIFS($B$1:B2,B2,$C$1:C2,C2)>1,INDEX($A$1:A1,IFERROR(MATCH(B2&"-"&C2,$B$1:B1&"-"&$C$1:C1,0),1)),MAX($A$1:A1)+1)

I'm assuming that there are no headers and you have already put 1 in cell A1 for the first record.

It basically checks the whole columns above the formula and if there's already a similar record, it'll assign the previously given unique ID and if not, it'll give a new ID.

This is an array function and as such will work if you use Ctrl+Shift+Enter and not Enter alone.

The IFERROR() is there because MATCH(B2&"-"&C2,$B$1:B1&"-"&$C$1:C1,0) would return an error if it is on row 2 (the first record to check).

Once you put that in the first cell, you can fill down the formula.

Upvotes: 1

pnuts
pnuts

Reputation: 59450

Assuming your data is in B2:C6 please try =IF(AND(B1=B2,C1=C2),A1,A1+1) in A2, copied down

Upvotes: 1

Related Questions