Excel question - Transposing cells

Soldato
Joined
6 Nov 2002
Posts
9,785
Location
London UK
Hi all, quick Excel question. I have a large dataset where the data is arranged as such:-

ROW 1 : UniqueItemA, SubValue1, SubValue2, SubValue3
ROW 2 : UniqueItemB, SubValue1, SubValue2, SubValue3

Which I need transposed into the following layout:-

ROW 1 : UniqueItemA, SubValue1
ROW 2 : UniqueItemA, SubValue2
ROW 3 : UniqueItemA, SubValue3
ROW 4 : UniqueItemB, SubValue1
ROW 5 : UniqueItemB, SubValue2
ROW 6 : UniqueItemB, SubValue3

I've tried a few things including pivots but no joy as yet. Any simple/quick way of doing this as I'd prefer not to hire someone to manually slog through it.

Thanks.
 
Associate
Joined
16 Oct 2003
Posts
170
Location
Brum
As mentioned, a fairly simple macro could be set up to do this (even if a large varying number of sub fields are present).

However, if it is a one off task, and the most sub value associated with any one item is reasonably small (say less than ten), then the below method is a quick and dirty way of doing it...

Although an obvious thing to say, prior to doing this make a backup copy of the original data just in case!

Copy all the values in column A (the UniqueItems), and paste a copy on the same sheet directly below the original data (you may wish to leave a single blank row between the original and newly pasted data, to make section identification easier), then copy all the values from Column C to Column N (or whichever column is the highest containing a value) and paste this into column B next to the recently pasted UniqueItems.

Repeat this process, but this time copy the data only from the section you have just created (which should have one less column of data than the original data).

Repeat this process until its logical conclusion.

Columns A and B should now contain all the data, so the other columns may be deleted.

Sort the data by column B and delete all the rows which have no value in column B.

Finally, sort the data by Column A, and the data should be in the format as you initially described.

Hope that makes sense, once you understand the process it is quicker to do than it is to explain!
 
Soldato
OP
Joined
6 Nov 2002
Posts
9,785
Location
London UK
You know that makes logical sense, will give it a bash in the morning when back onsite.

Thanks.

EDIT: Just tried it on some sample data, yep works perfectly, such a simple solution. Knew it would be worth me asking. :p
 
Last edited:
Soldato
Joined
23 Oct 2002
Posts
5,719
Location
Various
Looks like you've already done it but I'll throw in a Pivottable solution anyway...

  1. Highlight the data and create a pivot table
  2. Add every field (i.e. UniqueItem and SubValue123) to the ROWS area of the pivot
  3. PivotTable Tools (in the ribbon) > Design > Report Layout > Show in compact Form
  4. Right click one of the unique items > field settings > layout & print > show items in tabular form
  5. And while you're in that menu tick 'Repeat Item Labels'
  6. Then get rid of all the totals and subtotals.
 
Soldato
Joined
30 Sep 2004
Posts
5,382
Location
Belfast/Edinburgh
Copy each row. Select the cell you want to paste to. Go up to the paste options and click paste special. There's an option in there to transpose.

This is assuming you're just wanting it transposed.
 
Last edited:
Back
Top Bottom