Transposing Data

[conleyle]

I have hundreds of rows and columns in a table formatted like table A. I want it to be structured like table B. so I can summarize. How do I do that? Not sure if this is best in Access or Excel.

Thank you for your help.

Table A-What I have
Item 123 456 789
ABC 1 2 3
LMN 4 5 6
XYZ 7 8 9

Table B-What I want
Item
ABC 123 1
LMN 456 2
XYZ 789 3
ABC 123 4
LMN 456 5
XYZ 789 6
ABC 123 7
LMN 456 8
XYZ 789 9

Cross-posted here: http://www.mrexcel.com/forum/excel-questions/853440-transposing-data.html

 

[hiker95]

conleyle,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


We can not tell where your raw data is located, sheet name, cells, rows, columns, and, we can not tell where the results should be, sheet name, cells, rows, columns.

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[conleyle]

I'm using Excel 2010. Windows 7, and a PC.
I would like the data to start in Sheet 1 starting in cell A1. and the new data to be in Sheet 2 starting in cell A1.

I'm working on a screenshot or uploading the document.

Thank you for taking the time to reply. Hopefully we can figure it out b/c I will have 60 spreadsheets to do this separately in and about 600 rows and 250 columns in each spreadsheet. Geesh. Makes my head spin just thinking about it.

 

[conleyle]

Excel 2010
	A	B	C	D
1	Item	Customer 1	Customer 2	Customer 3
2	Item 1	1	2	3
3	Item 2	4	5	6
4	item 3	7	8	9
Sheet1

Excel 2010
	A	B	C
1	Item	Customer	Qty
2	Item 1	Customer 1	1
3	Item 2	Customer 2	2
4	item 3	Customer 3	3
5	Item 1	Customer 1	4
6	Item 2	Customer 2	5
7	item 3	Customer 3	6
8	Item 1	Customer 1	7
9	Item 2	Customer 2	8
10	item 3	Customer 3	9
Sheet2

This is totally cool how to paste like this, btw. Thanks for sharing the links.

 

[hiker95]

conleyle,

What you have posted in the above two screenshots does not make any sense to me? And, I can not see your logic from going from Sheet1, to, Sheet2?

In Sheet2, Item 2, Customer 2, should be equal to 5, and, not a 2?


I assume that Sheet1, and, Sheet2, already exist in your workbook.

Here is a macro solution for you to consider, using two arrays in memory.

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E
1	Item	Customer 1	Customer 2	Customer 3
2	Item 1	1	2	3
3	Item 2	4	5	6
4	item 3	7	8	9
5
Sheet1

After the macro in Sheet2:

Excel 2007
	A	B	C
1	Item	Customer	Qty
2	Item 1	Customer 1	1
3	Item 1	Customer 2	2
4	Item 1	Customer 3	3
5	Item 2	Customer 1	4
6	Item 2	Customer 2	5
7	Item 2	Customer 3	6
8	item 3	Customer 1	7
9	item 3	Customer 2	8
10	item 3	Customer 3	9
11
Sheet2

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.

Sub ReorgData()
' hiker95, 05/06/2015, ME853441
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, c As Long, cc As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr - 1) * (lc - 1) + 1, 1 To 3)
End With
j = j + 1: o(j, 1) = "Item": o(j, 2) = "Customer": o(j, 3) = "Qty"
For i = 2 To lr
  For c = 2 To lc
    j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(1, c): o(j, 3) = a(i, c)
  Next c
Next i
With Sheets("Sheet2")
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Cells(1, 1).Resize(, 3).Font.Bold = True
  .Cells(1, 2).Resize(UBound(o, 1)).Font.Bold = True
  .UsedRange.HorizontalAlignment = xlCenter
  .Columns("A:C").AutoFit
  .Activate
End With
Erase a: Erase o
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 ReorgData macro.

 

[conleyle]

I should probably explain. I don't have any formulas transferring the data. That's what I'm looking for. I will have about 60 spreadsheets in the sheet 1 format, but I need to transfer it to the sheet 2 format. I'm not even sure what format to use. I did the copy and paste special transpose, but I'm not sure how to replicate it. Does that make sense?

I used the code you gave me and it is EXACTLY what I needed. OMG. You are a lifesaver!!! I can't even begin to say thank you enough.

 

[hiker95]

conleyle,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.