Complicated excel transpose

S

silverdollar

New Member
Joined
May 11, 2017
Messages
2
Hello,

I have a set of data that contains data using a combination of columns and rows. I would like to transpose the columns into the rows so I would be able to create a load file. I'm not quite sure how to explain this but please see the screen print below to see what I am trying to accomplish. The actual data set has over 200 rows and 200 columns, the end result would have roughly 40,000 (200*200) rows of data. If anyone has a good suggestion on creating this data set, please let me know. I would appreciate any suggestions. Thanks everyone!!

[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl64, width: 65"]BEFORE:[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl63, align: right"]Unit A[/TD]
[TD="class: xl63, align: right"]Unit B[/TD]
[TD="class: xl63, align: right"]Unit C[/TD]
[/TR]
[TR]
[TD]GL-1000[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]GL-1001[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]GL-2000[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]GL-2002[/TD]
[TD="align: right"]-400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]GL-3000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]GL-3001[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]


<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</style><![endif]-->[TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl66, width: 65"]AFTER:[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Account[/TD]
[TD="class: xl64, align: center"]Unit[/TD]
[TD="class: xl64, align: right"]Amount[/TD]
[/TR]
[TR]
[TD]GL-1000[/TD]
[TD="class: xl63"]Unit A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]GL-1001[/TD]
[TD="class: xl63"]Unit A[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]GL-2000[/TD]
[TD="class: xl63"]Unit A[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]GL-2002[/TD]
[TD="class: xl63"]Unit A[/TD]
[TD="align: right"]-400[/TD]
[/TR]
[TR]
[TD]GL-3000[/TD]
[TD="class: xl63"]Unit A[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]GL-3001[/TD]
[TD="class: xl63"]Unit A[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD]GL-1000[/TD]
[TD="class: xl63"]Unit B[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]GL-1001[/TD]
[TD="class: xl63"]Unit B[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]GL-2000[/TD]
[TD="class: xl63"]Unit B[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]GL-2002[/TD]
[TD="class: xl63"]Unit B[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]GL-3000[/TD]
[TD="class: xl63"]Unit B[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]GL-3001[/TD]
[TD="class: xl63"]Unit B[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]GL-1000[/TD]
[TD="class: xl63"]Unit C[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]GL-1001[/TD]
[TD="class: xl63"]Unit C[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]GL-2000[/TD]
[TD="class: xl63"]Unit C[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]GL-2002[/TD]
[TD="class: xl63"]Unit C[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]GL-3000[/TD]
[TD="class: xl63"]Unit C[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]GL-3001[/TD]
[TD="class: xl63"]Unit C[/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
silverdollar,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider, that will adjust to the number of raw data rows, and, columns, and, that uses two arrays in memory, and, should be very fast.

You can change the raw data worksheet name in the macro.

The macro will create a new worksheet Results.

Sample raw data:


Excel 2007
ABCDE
1Unit AUnit BUnit C
2GL-1000100500
3GL-10012007575
4GL-20003000-100
5GL-2002-4000100
6GL-3000500-100200
7GL-300160050300
8
Sheet1


And, after the macro in worksheet Results:


Excel 2007
ABC
1AccountUnitAmount
2GL-1000Unit A100
3GL-1001Unit A200
4GL-2000Unit A300
5GL-2002Unit A-400
6GL-3000Unit A500
7GL-3001Unit A600
8GL-1000Unit B50
9GL-1001Unit B75
10GL-2000Unit B0
11GL-2002Unit B0
12GL-3000Unit B-100
13GL-3001Unit B50
14GL-1000Unit C0
15GL-1001Unit C75
16GL-2000Unit C-100
17GL-2002Unit C100
18GL-3000Unit C200
19GL-3001Unit C300
20
Results


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorganizeData()
' hiker95, 05/11/2017, ME1004903
Dim w1 As Worksheet, wr As Worksheet
Dim lr As Long, lc As Long
Dim a As Variant, i As Long, c As Long, n As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Worksheets("Results")
wr.UsedRange.Clear
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.Count(.Range(.Cells(2, 2), .Cells(lr, lc)))
  ReDim o(1 To n + 1, 1 To 3)
  j = j + 1: o(j, 1) = "Account": o(j, 2) = "Unit": o(j, 3) = "Amount"
End With
For c = 2 To UBound(a, 2)
  For i = 2 To UBound(a, 1)
    j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(1, c): o(j, 3) = a(i, c)
  Next i
Next c
With wr
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorganizeData macro.
 
Upvote 0
silverdollar,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
silverdollar said:
Thank you hiker95!! This works perfectly.
Click to expand...
Here is another macro you can consider. There is no speed advantage between hiker95's code and my code, but I did take a different approach with respect to the result sheet. Rather than creating or, if this is run 2 or more, simply clearing the result sheet, I let you specify an empty worksheet to output the results to... that way, you can maintain copies of multiple runs of the program without having to manually transfer the result of one run to a different worksheet before running the program again. The red highlighted rows of code control where to look for the data and where to output the results (the variable names should make it obvious which is which). Anyway, my coding approach is different as well as shorter from hiker95's, so I thought others might find it interesting to dissect if you are into that sort of thing.
Code: 
[table="width: 500"]
[tr]
	[td]Sub ReorganizeData()
  Dim c As Long, LastRow As Long, LastCol As Long, Data As Variant
  Dim DataSheet As Worksheet, ResultSheet As Worksheet
  Set DataSheet = Sheets("Sheet1")
  Set ResultSheet = Sheets("Sheet2")
  LastRow = DataSheet.Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = DataSheet.Cells(1, Columns.Count).End(xlToLeft).Column
  Application.ScreenUpdating = False
  DataSheet.Cells(2, LastCol + 1).Resize(LastRow - 1, LastCol - 1) = DataSheet.Range("B1").Resize(, LastCol - 1).Value
  Data = Range(DataSheet.Cells(2, "A"), DataSheet.Cells(LastRow, 2 * LastCol - 1))
  ResultSheet.Range("A1:C1") = Array("Account", "Unit", "Amount")
  For c = 2 To LastCol
    ResultSheet.Cells(2 + (c - 2) * UBound(Data), "A").Resize(UBound(Data), 3) = Application.Index(Data, Evaluate("ROW(1:" & LastRow - 1 & ")"), Split("1 " & c + LastCol - 1 & " " & c))
  Next
  ResultSheet.Columns("A").Resize(, LastCol).AutoFit
  DataSheet.Columns(LastCol + 1).Resize(, LastCol).Clear
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
I wonder if anybody could please help with a variation on the above query.
My situation is very similar to the one above, but instead of the cells containing numbers, they contain text. I tried the code shown above and although it works perfectly when the cells contain numbers, it reveals an error if I change the details in the cells to text. I have shown a couple of tables below with some sample text to show what I would like. If anybody could help with this, it would be greatly appreciated. The complete table I wish it to work on is much larger than this, but i just wanted to show an example of what I would like to achieve.

Table Before

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Colour 1[/TD]
[TD]Colour 2[/TD]
[TD]Colour 3[/TD]
[TD]Colour 4[/TD]
[TD]Colour 5[/TD]
[TD]Colour 6[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Red[/TD]
[TD]Yellow[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Blue[/TD]
[TD]Purple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Yellow[/TD]
[TD]White[/TD]
[TD]Black[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Purple[/TD]
[TD]Orange[/TD]
[TD]Blue[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Purple[/TD]
[TD]Black[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[TD]White[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table After

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Colour ID[/TD]
[TD]Colour[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Colour 1[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Colour 1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Colour 1[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Colour 1[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Colour 1[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Colour 1[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Colour 2[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Colour 2[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Colour 2[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Colour 2[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Colour 2[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Colour 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Colour 3[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Colour 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Colour 3[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Colour 3[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Colour 3[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Colour 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Colour 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Colour 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Colour 4[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Colour 4[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Colour 4[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Colour 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Colour 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Colour 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Colour 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Colour 5[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Colour 5[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Colour 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Colour 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Colour 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Colour 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Colour 6[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Colour 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]Colour 6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Many thanks in anticipation
Paul
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Need to Filter, Match and Offset
Replies
3
Views
297
Fluff
Fluff
V
  • Solved
Suitable formula needed
2
Replies
16
Views
452
Joe4
Joe4
S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
207
RobP
R
T
  • Solved
Excel formula: how to sum multiple columns on a single criteria
Replies
3
Views
351
TT123
T
M
  • Question Question
valid data input
Replies
5
Views
297
mwvirk
M

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top