I have these membership data where each member are able to register their family members under them:
| Name | Status |
|---|---|
| John Doe | Member |
| Mary Jonas | Member |
| Edith Jonas | Child |
| Arthur Benjamin | Member |
| Jennifer Lauren | Wife |
| Moore Troops | Child |
| Erica Maxine | Child |
| Erin Benjamin | Child |
The family registration for the membership are as such: John Doe has no family registered under him Mary Jonas has 1 family member registered under her (Edith Jonas) Arthur Benjamin has 3 family members registered under him (Jennifer Lauren, Moore Troops, Erica Maxine, and Erin Benjamin.)
I need to assign IDs for them in which: Member = (number) "a" Wife = (Member Number) "b" 1st Child = (Member Number) "c" 2nd Child = (Member Number) "d" 3rd Child = (Member Number) "e" and so on....
I would like to be able to input a formula to automatically produce such IDs:
| ID | Name | Status |
|---|---|---|
| 1a | John Doe | Member |
| 2a | Mary Jonas | Member |
| 2b | Edith Jonas | Child |
| 3a | Arthur Benjamin | Member |
| 3b | Jennifer Lauren | Wife |
| 3c | Moore Troops | Child |
| 3d | Erica Maxine | Child |
| 3e | Erin Benjamin | Child |
What I did for now is to number each of the employee first and then created helper column to produce the letters using IF formula, but I only manage to do up to the letter 'c'. I could not produce label for 'd' and 'e' using the IF formula.
Is there anyway I can create these IDs with formula and using as little helper column as possible? Thanks in advance!
CodePudding user response：
Try:
Formula in A2:
=LET(x,C2:C9,y,x="Member",SCAN(0,y,LAMBDA(a,b,a b))&CHAR(97 SCAN(0,y,LAMBDA(c,d,IF(d,0,c 1)))))
Works up to 'z', but you have clarified this should be fine as there is a limit up to 3 children.
CodePudding user response：
JvDV made such a nice formula, but I couldn't understand it, so I tried to solve it my way:
you need one helper column for this. It changes the number when there is a new Member in the list.
E2 =COUNTIF($C$2:C2;"Member")
The other formula is divided in two parts, to understand it a bit easier:
We need to know if the Member of the Familie is the 1st, 2nd, 3rd and so on member. This is done by this formula:
F2 =ROWS(FILTER($E$2:$E$9;($E$2:$E$9=E2)))-ROWS(FILTER(E2:$E$9;(E2:$E$9=E2))) 1
Now we convert the number into letters. 1 for a, 2 for b, 3 for c and so on
G2 =CHOOSE(F2;"a";"b";"c";"d";"e";"f")
Extend the formula if you need.
Now we put the last two formulas together and add the number from the helper column:
I2 =E2&CHOOSE(ROWS(FILTER($E$2:$E$9;($E$2:$E$9=E2)))-ROWS(FILTER(E2:$E$9;(E2:$E$9=E2))) 1;"a";"b";"c";"d";"e";"f")
