Search code examples
excelfilterexcel-formulacountif

Assign a number to the univocal rows that conform to a certain filter in array

I have the following table with the following columns: Column 1: Account Nr. Column 2: Partner

In Column 3 I added my desired result: Univocal Partner for each Account Nr., numbered

Account Nr. Partner Desired Result
2 C 1
2 B 2
2 B 2
3 B 1
3 C 2
4 F 1
4 F 1
4 G 2
4 A 3

I need a formula to insert in Column 3 that assigns a number to the univocal 'Partner' that each 'Account Nr' has. Explained in words: For Account Nr. 2 there are two partners (C, B) so assign 1 to C, 2 to B. For Account Nr. 3 there are two partners (B and C) so assign 1 to B and 2 to C. For Account Nr. 4 there are three partners (F, G and A) so assign 1 to F, 2 to G, 3 to A.

The table is structured as a hierarchy so I can't sort the column for any value.

I tried combining COUNTIF and FILTER but apparently this does not support arrays.

Solution

  • Try using the following formula:

    enter image description here

    =XMATCH(B2,UNIQUE(FILTER(B$2:B$10,A$2:A$10=A2)))

    If not using Structured References aka Tables then could spill the formula for the entire data using the following:

    =LET(
     a, A2:B10,
     MAP(SEQUENCE(ROWS(a)),LAMBDA(x,
     XMATCH(INDEX(a,x,2),
     UNIQUE(FILTER(DROP(a,,1),
     INDEX(a,x,1)=TAKE(a,,1)))))))