Hi. I have a list of names that need to be grouped and numbered. I have a problem with the formula that assigns the number in each group where the names are not listed in consecutive rows.
I have attached a sample sheet.
Thank you.

Attached Files

NameCoding.xlsx (26.5 KB, 10 views)

Match kind of works for the name code as it will return the first match

=MATCH([@Name2],[Name2],0)

And for the name try

=IF([@Name3]<>"N","",INDEX([Name],MATCH([@Name3]&[@GroupName],[Name3]&[GroupName],0)))

Added extra columns to sheet tNames (2)

Attached Files

NameCoding_Purfleet.xlsx (36.5 KB, 6 views)

Thank you Purfleet.

The MATCH formula must return 1, 2, 3, 4, 5, 6, 7, 8...... It left out numbers 2,3, 6, 17 and 18.

The formula IN THE GroupNameX must populate all the rows with the chosen name for each group by Name3.

You have the UNIQUE function available? In cell E2:
=MATCH([@Name2],UNIQUE([Name2]),0)
copied down.

I think Purfleet's solution will work for you if you use his
=MATCH([@Name2],[Name2],0)
in E2 (copy down if it doesn't happen automatically).
Then refresh your qUnique table.
Leave your VLOOKUP as it is in column G.

The numbers don't have to be contiguous values for this.


Without the qUnique table, try in column G
=INDEX([Name],MATCH([@NameCode]&"N",[NameCode]&[Name3],0))

Thank you p45Cal. I adopted the Unique formula because it is not skipping some numbers.