This is one way. It requires a helper column in D to complete the list of row headers (Name and Surname).
The formula in D2
Then this array formula in F2 filled down and across until you get blanks.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
1 |
Name and Surname |
Child Name |
Child age |
|
Name and Surname_1 |
Child Name_1 |
Child age_1 |
Child Name_2 |
Child age_2 |
Child Name_3 |
Child age_3 |
2 |
Ned Stark |
Sansa |
2 |
Ned Stark |
Ned Stark |
Sansa |
2 |
Rob |
1 |
Brandon |
3 |
3 |
|
Rob |
1 |
Ned Stark |
Tywin Lanister |
Cersei |
7 |
Jaimie |
6 |
|
|
4 |
|
Brandon |
3 |
Ned Stark |
|
|
|
|
|
|
|
5 |
Tywin Lanister |
Cersei |
7 |
Tywin Lanister |
|
|
|
|
|
|
|
6 |
|
Jaimie |
6 |
Tywin Lanister |
|
|
|
|
|
|
|
7 |
|
|
|
|
|
|
|
|
|
|
|
Bookmarks