Transpose multiple duplicate rows into relevant column data

[SimonN]

Hello Everyone,

Hopefully someone will be able to help with a data set I am trying to manipulate. I have searched this forum and the internet but not been able to find anything which matches my requirements.

I am working on a data set which originates from an HTML page containing multiple repeated tables of data. I have manipulated this data in notepad++ to give me a tab separated text file which I can import into excel. The format is now a 2 column data set in the format:

HeadingA	DataA
HeadingB	DataB
HeadingC	DataC
HeadingA	DataA
HeadingB	DataB
HeadingC	DataC

What I want to do is transpose the headings to a set of columns and populate with the relevant data. ie:

HeadingA	HeadingB	HeadingC
DataA	DataB	DataC
DataA	DataB	DataC
DataA	DataB	DataC

It is important that each row represents the data from each original table of data from the HTML. If it helps to explain the data represents file details of individual files, with each table giving the details of an individual file, ultimately I want to be able to get it into a format whereby I can sort and filter by the fields such as date created etc.

Hopefully I have explained well enough. Many thanks in advance

Simon

[:) Sixthsense :)]

Assum that the below data is present in A1:B6 Cell

HeadingA DataA
HeadingB DataB
HeadingC DataC
HeadingA DataA
HeadingB DataB
HeadingC DataC

In D1 Cell

Please Login or Register  to view this content.

In D2 Cell

Please Login or Register  to view this content.

Drag both the formula's down.. and right...

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[SimonN]

Hi there Sixthsense,

Thats fantastic and after tweaking the numbers, and my dataset slightly (there were some variations in headings I hadn't noticed before) it has worked perfectly.

I'd be interested how the functions work if you have time to explain, I assume the first function in cell D1 is just looking for the matches in the column A data in order to just display the 1 heading. I'm not sure how the formula for D2 works though.

many thanks for your help and the speedy response.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep

To know how the formula works, then refer the Post #6 of the below thread
http://www.excelforum.com/excel-form...-workbook.html