Transpose Rows into Columns and keep Data in a concatenated row

[afmathieson]

I am looking to take the following (see image below):

Note: F2 is really the column header Title/Desc (except for the @P1@ which is a record indicator). The rows within the Person's record are also not in a standard order (In the first overall record, Gender is the first row and in the second overall record the Gender is the 4th row).

Also, an overall person's record can vary in the number of rows for that individual (i.e. a living person vs. one that has passed and therefore has death information in additional rows).

And get the following results...

Record #

P#

SEX

BIRT

BIRT DATE

BIRT PLAC

NAME

FAMC

FAMS

ADOP

ADOP DATE

ADOP PLAC

DEAT

DEAT DATE

DEAT PLAC

1

@P1@

M

1-Jun-66

Lunenburg, Lunenburg, Nova Scotia, Canada

Andrew Foster /Mathieson/

@F2@

@F1@

2

@P2@

F

2-Aug-09

Plano, Denton, Texas, USA

Taylor Lynn /Mathieson/

@F1@

2-Aug-09

Plano, Denton, Texas, USA

3

@P3@

M

20-Nov-05

Dallas, Dallas, Texas, USA

Drake Alan /Mathieson/

@F1@

4

@P4@

M

12-Jul-04

Dallas, Dallas, Texas, USA

Prescott Foster /Mathieson/

@F1@

5

@P5@

M

8-Mar-02

Dallas, Dallas, Texas, USA

Hunter Hart /Mathieson/

@F1@

6

@P6@

F

27-Dec-69

Dallas, Dallas, Texas, USA

Angel Claudine /Generes/

@F1@

7

@P7@

F

2-Aug-42

Saint John, Saint John, New Brunswick, Canada

Carol Ann /Foster/

@F5@

@F2@

Passed away at Bethany Lodge as a result of complications due to Alzheimer's.

19-Aug-20

Unionville, York, Ontario, Canada

[christine_assaad · Accepted solution]

Hi @afmathieson 

Is this what you are looking for? if not, it may be helpful to share your input file - if possible. Cheers!

[afmathieson]

Attaching the excel data input file...

[afmathieson]

Thanks Christine,

I will take a look at your solution...