Finding unique table values and assigning an UID without extracting or removing data

[terriertrip]

Much about extracting unique values into collection and arrays but I don't need the data extracted. I need to have each unique value in column (D) to have an associated identifier in column (A). I want to retain the data integrity (no duplicate removal) but have Excel VBA loop through column (D) and assign a UID to column (A) in the row that unique value in D resides. The UID would start with 1, then 2, and 3, and so forth (i +1). It seems like it would be a simple process, however since there are potential for many duplicates not in sequential order, I don't know the best way to approach this.

[hrlngrv]

In general, if you have a table, say B5:Z10004 with column labels in B4:Z4, one of the simpler ways to handle duplicates would be to enter 1 in AA5, 2 in AA6, select AA5:AA6 and double-click the fill handle in the bottom-right corner of AA6 to fill sequential integers into AA5:AA10004. Then sort B5:AA1004 on column D in ascending order. Then in column A,

A5: 1
A6: =A5+(D6<>D5)

Fill A6 down into A7:A10004. Select A5:A10004, copy, and paste special as values on top of itself. Then sort A5:AA10004 on column AA in ascending order. That's assuming you want the same UID for each column D value which appears multiple times.

[terriertrip]

I would prefer to not use helper columns and all that sorting. I don't want the UID for each record (row) either, just for the first occurrence.
It's looking like I might have to use a collection to gather the variables x, remove the duplicates, then loop the collection back into the table for the first occurrence of variable x, then offset (to A) a value of i + 1, next x....etc.

[MarvinP]

Hi Terriertrip,

See if this kind of formula in column B does what you want.

Please Login or Register  to view this content.

UID from Duplicates.xlsx
Press F9 a few times to recalculate my random column A to check.

[hrlngrv]

If the records start in row 5,

A5: 1
A6: =IFERROR(MAX(A$5:A5)+ERROR.TYPE(MATCH(D6,D$5:D5,0))^0,"")

Fill A6 down as far as needed. Once that's done, consider copying the col A range and pasting it special as values on top of itself.

ADDED: too broad, change the formula to

A6: =IFERROR(MAX(A$5:A5)+1/ISNA(MATCH(D6,D$5:D5,0)),"")

[terriertrip]

After researching this evening, I was able to get a better result using an array as opposed to a collection. The code below loops through a range storing the unique values in an array. I included blanks in the array because my data is time based and the end result needed to remain preserved to the original dimension, but one could set any parameter one chooses. The array then is spit back out to a helper column (I wrote back to the original column to save a step, but the blank values overwrote the data, so had to use the helper column). I applied a simple loop to apply a UID when x <> "".

Code:

Please Login or Register  to view this content.