Unstack 2 columns of differing data into a single table

[ricky419]

Hi all, I've looked around and haven't seen anybody address this problem. I have a large amount of text data that isn't quite structured enough to be useful yet. Maybe Excel isn't the right tool for it. Anyway, I have boiled it down to 2 columns of data like this:

Purchaser: Joe Blow
Date: 1/1/2013
Place: Paris
Price: $100
Item: Scarf
Purchaser: Betty Boop
Place: New York
Item: Belt
Purchaser: David Mish
Date: 1/2/2013
Place: Los Angeles

I have many thousands of records like that, in groups of about 100 rows each, but the number varies. Most of the groups are missing at least one of the descriptors, but they all start with "Purchaser". I would like to convert all this into a table with one row per group with every field in the right place, such as

Purchaser	Date	Place	Price	Item
Joe Blow	1/1/2013	Paris	$100	Scarf
Betty Boop		New York		Belt
David Mish	1/2/2013	Los Angeles

I could select each group of data, say 100 rows x 2 columns and then transpose them to a new table and then move columns around so they line up right, but there are way too many data to do this manually.

If I had a clean way to export all this to Access or Filemaker Pro or Paradox, and make each of my groups into one record in a database, it would be even better, but that's also beyond me.

Any help would be greatly appreciated!

[MickG]

Try this:- Results start "D1".

Please Login or Register  to view this content.

Regards Mick

[ricky419]

Thanks, Mick. I sort of see what the code does, and I'll study it to understand better.

Ricky