Transpose sets of rows into columns

Question

Trying to avoid myself hours of manual work by finding a way to transpose sets of data to columns.

Currently my data has been cleaned up and separated into two columns: Column A shows the column name, while Column B shows the actual value. So you have a set of values, then two blank rows, then the next set.

Column A | Value A1
Column B | Value B1
Column C | Value C1
Column D | Value D1
Column E | Value E1


Column A | Value A2
Column B | Value B2
Column D | Value D2
Column E | Value E2
Column F | Value F1

My problem is that the sets do not all contain the same number of rows, and the values in the left column do not always match (as per the above, some sets will have a Column C entry, others will have a Column F entry, and others will have neither).

One thing that may help is that the first value is identified by the same Column Name, i.e Column A.

How do I make Excel transpose the sets into distinct rows? I want to end up with this:

Column A | Column B | Column C | Column D | Column E | Column F
Value A1 | Value B1 | Value C1 | Value D1 | Value E1
Value A2 | Value B2 |          | Value D2 | Value E2 | Value F1

I'm open to using VBA but not very familiar, so GUI options are preferred but not required. If you offer a VBA solution, I'd appreciate as many details as you can give me.

Thank you :) Regards, Kate.

Answer 1

How feasible a GUI solution is depends on how much data you're talking about. If it's a reasonable amount of data you can do it in the GUI by hand. Step 1 is to copy each of the values so that it looks like:

Column A | Value A1 | Value A2
Column B | Value B1 | Value B2
Column C | Value C1
Column D | Value D1 | Value D2
Column E | Value E1
Column F | Value F1

Then you can copy the entire area and Paste Special -> Transpose to swap the row and columns. You could speed up the copying around of data by using the Excel sort feature first to sort based on the first column so that all the column A entries would appear first followed by all the column B entries, etc. As I said, the feasibility of this depends on how much data you're talking about.

If you want to automate this further I suspect you will need to use VBA, at least I can't think of a more graphical way to do it right now.