I have a file with rows of permit data for home addresses. There are 574 rows of permits for 186 unique addresses. I am trying to create a unique ID for each address that excludes duplicate values, so that each row of permit data references a specific ID number for the address it relates to. I’ve looked around this website and YouTube but can’t find a solution that works for my case.
I’ve tried the formula mentioned in the reply to this post, but it is returning ID values greater than 186. Given that I only have 186 unique addresses, this should not be the case.
=TEXT(IF(COUNTIF(B$2:B2,B2)>1,MATCH(B2,$B$2:$B$13,0),COUNTA(UNIQUE(B$2:B2))),"00")
4
With 365 this formula in cell B1 and drag down:
=VLOOKUP(A1,HSTACK(UNIQUE(A$1:A$21),SEQUENCE(COUNTA(UNIQUE(A$1:A$21)),1,1)),2,FALSE)
You could try
=XMATCH(B2:INDEX(B:B, COUNTA(B:B)), SORT(UNIQUE(B2:INDEX(B:B, COUNTA(B:B)))), , 2)
or
=LET(
adr, B2:INDEX(B:B, COUNTA(B:B)),
u_adr, SORT(UNIQUE(adr)),
XMATCH(adr, u_adr, , 2)
)