Converting multiple records (rows) into one record (row) using a unique identifier

[bbgun]

Hi,

I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.

For example:

COL1 COL2 COL3 COL4 COL5
ID TITLE NAME DATE STATUS
100001 Mr Smith 3/07/2006 Pending
100001 Mr Smith 2/08/2006 Referral
100001 Mr Smith 1/12/2006 Probation
100001 Mr Smith 6/07/2007 Excluded
100002 Mr Jones 1/12/2006 Pending
100002 Mr Jones 3/04/2007 Probation
100002 Mr Jones 6/07/2007 Excluded

Result should look like:
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11
ID TITLE NAME DATE_1 STATUS_1 DATE_2 STATUS_2 DATE_3 STATUS_3 DATE_4 STATUS_4
100001 Mr Smith 3/07/2006 Pending 2/08/2006 Referral 1/12/2006 Probation 6/07/2007 Excluded
100002 Mr Jones 1/12/2006 Pending 3/04/2007 Probation 6/07/2007 Excluded


Sorry, the formatting is not aligned, but I hope you can understand what I mean. Any help or advice would be very much appreciated.

Many thanx

[oldchippy]

Hi,

Welcome to the forum, I've assumed each ID only having a maximum of four records - see attached.

Does that help?

[bbgun]

Hi,

Welcome to the forum, I've assumed each ID only having a maximum of four records - see attached.

Does that help?

Thank you for sending me such a quick reply. This was a very clever tip and you have given me a way of reducing over 25,000 records to less than 5,000 in a flat file I can analyse in a statistical software package. Thankyou!!!

Kind regards,
Brad

[oldchippy]

Glad to hear it helped Brad - thanks for the feedback

[atizaz446]

Hi,

Welcome to the forum, I've assumed each ID only having a maximum of four records - see attached.

Does that help?

Hi,

Thanks a bundles for the questioner and lots of prayers for the one who answered, I was struck at the same problem in excel, tried many macros to convert but since I had more than 1 gb excel file, macro failed to process. The technique you mentioned has exactly solved my big problem.

Atizaz Ali
University of Seoul, Korea