Hi, we're undertaking a data collection drive for a community project. We have a data collection software that records data in below format.
We need that data to be segregated in the below format.
The problem is that the data columns from E would vary based on the number of members, so we need a formula that ignores blanks and segregates data in the above format.
We tried a couple of things, but weren't able to achieve the desired results. Any help is greatly appreciated.
EDIT:
Would also like to add that the number of columns after E are not fixed. There could be n number of members for each gender type.
| Book1 | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 1 | GROUP ID | FLAT | BUILDING NAME | STATUS | MALE1 | MALE1 ID | MALE2 | MALE2 ID | MALE3 | MALE3 ID | FEMALE1 | ID1 | FEMALE2 | ID2 | FEMALE3 | ID3 | CHILD1 | ID1 | CHILD2 | ID2 | CHILD3 | ID3 | ||
| 2 | 1234 | 105 | ABCD | YES | MR. X | 123456 | MR. Y | 123457 | MR. Z | 123458 | MS. X | 11111 | MS. Y | 8888 | MS. Z | 7777 | MSTR X | 9999 | MSTR Y | 7777 | MSTR Z | 5555 | ||
| 3 | 1235 | 106 | ABCD | YES | MR.X | 122336 | MS. X | 22222 | MSTR X | 79654 | ||||||||||||||
Sheet1 | ||||||||||||||||||||||||
We need that data to be segregated in the below format.
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | GROUP ID | FLAT | BUILDING NAME | STATUS | ID | NAME | GENDER | ||
| 2 | 1234 | 105 | ABCD | YES | 123456 | MR. X | MALE1 | ||
| 3 | 123457 | MR. Y | MALE2 | ||||||
| 4 | 123458 | MR. Z | MALE3 | ||||||
| 5 | 11111 | MS. X | FEMALE1 | ||||||
| 6 | 8888 | MS. Y | FEMALE2 | ||||||
| 7 | 7777 | MS. Z | FEMALE3 | ||||||
| 8 | 9999 | MSTR X | CHILD1 | ||||||
| 9 | 7777 | MSTR Y | CHILD2 | ||||||
| 10 | 5555 | MSTR Z | CHILD3 | ||||||
| 11 | |||||||||
| 12 | 1235 | 105 | ABCD | YES | 122336 | MR. X | MALE1 | ||
| 13 | 22222 | MS. X | FEMALE1 | ||||||
| 14 | 79654 | MSTR X | CHILD1 | ||||||
Sheet2 | |||||||||
The problem is that the data columns from E would vary based on the number of members, so we need a formula that ignores blanks and segregates data in the above format.
We tried a couple of things, but weren't able to achieve the desired results. Any help is greatly appreciated.
EDIT:
Would also like to add that the number of columns after E are not fixed. There could be n number of members for each gender type.
Last edited by a moderator:
