Unique ID Filter

[DStayman]

Hi All,

I have run into a wall on a data base I am trying to complete. I have a mass of data but I am primarily working with 3 columns that will finish up my workbook. For the purpose of this question I have scrubbed erroneous data. I have cell A with Name, cell B with Serial to Name and cell D with Description. In cell C I have my unique Identifier which was derived by the following formula:

=IF(B2="","",B2&COUNTIF($B$2:$B2,B2))

This works terrific for issuing a unique ID to every line; however, that is where I am trying to do other wise. What I am trying to do is have the work book look at the description and if it has previously been used to look the given cells and issue a new unique ID or repeat the Unique ID if there is more than one instance of the description (possibly Index/Match Array). Ideally, would look as follows where rows 6-9 repeat the ID from rows 1-4; however, issue a new ID in 10 because it is different from row 5. Any help is greatly appreciated!!! (PS I can send the example workbook if works better but I am I have never posted before in MrExcel and not sure how to share the file).

​

[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]S/N[/TD]
[TD]Unique ID[/TD]
[TD]Desciption[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC Inc[/TD]
[TD]123456789[/TD]
[TD]1234567891[/TD]
[TD]ABC Inc - CRE - 123 ABC Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XYZ Inc[/TD]
[TD]987654321[/TD]
[TD]9876543211[/TD]
[TD]XYZ Inc - CRE - 123 XYZ Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC Inc[/TD]
[TD]123456789[/TD]
[TD]1234567892[/TD]
[TD]ABC Inc - CAL - 123 ABC Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]XYZ Inc[/TD]
[TD]987654321[/TD]
[TD]9876543212[/TD]
[TD]XYZ Inc - CAL - 123 XYZ Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John Smith[/TD]
[TD]100000000[/TD]
[TD]1234567891[/TD]
[TD]John Smith - ALI - $1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC Inc[/TD]
[TD]123456789[/TD]
[TD]1234567891[/TD]
[TD]ABC Inc - CRE - 123 ABC Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]XYZ Inc[/TD]
[TD]987654321[/TD]
[TD]9876543211[/TD]
[TD]XYZ Inc - CRE - 123 XYZ Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ABC Inc[/TD]
[TD]123456789[/TD]
[TD]1234567892[/TD]
[TD]ABC Inc - CAL - 123 ABC Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]XYZ Inc[/TD]
[TD]987654321[/TD]
[TD]9876543212[/TD]
[TD]XYZ Inc - CAL - 123 XYZ Ln,Anytown, USA[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]John Smith[/TD]
[TD]100000000[/TD]
[TD]1000000002[/TD]
[TD]John Smith - ALI - $2[/TD]
[/TR]
</tbody>[/TABLE]

 

[DStayman]

Any one who looked to solve, I was able to solve, was staring me in this face, was just not connecting dots. The formula I came up with that works is as follows as an array "{}":

{=IF(ISNA(MATCH(D2,$D$1:D1,0))=TRUE,IF(B2="","",B2&COUNTIF($B$2:$B2,B2)),INDEX($C$1:C1,MATCH(D2,$D$1:D1,0)))

Hope this can help others!