Data Orientation Question, HELP ASAP!!!!

[Rodo]

Hi Guys,

When I export data it orients the metrics in columns instead of row. Is there a way to move the data to read in each row (please see image to clarify). I tried to transpose but it did not work, please keep in mind that I am not a super advanced user.

[TABLE="width: 500"]
<tbody>[TR]
[TD]I get this[/TD]
[TD]size1[/TD]
[TD]size2[/TD]
[TD]size3[/TD]
[TD]size4[/TD]
[TD]Qt1[/TD]
[TD]Qt2[/TD]
[TD]Qt3[/TD]
[TD]Qt4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 1[/TD]
[TD]XS[/TD]
[TD]S[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 2[/TD]
[TD]S/M[/TD]
[TD]M/L[/TD]
[TD]L/XL[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 3[/TD]
[TD]OS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I Want this[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]size[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 1[/TD]
[TD]XS[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 1[/TD]
[TD]S[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 1[/TD]
[TD]M[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 1[/TD]
[TD]L[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 2[/TD]
[TD]S/M[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 2[/TD]
[TD]M/L[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 2[/TD]
[TD]L/XL[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style 3[/TD]
[TD]OS[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Rodo,

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

The results will begin in the third column to the right of the last used columns of the raw data.

I assume that the title row, row 1, will have the same number of size, and, Qty, columns.

Sample raw data in the active worksheet, and, results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	I get this	size1	size2	size3	size4	Qt1	Qt2	Qt3	Qt4			Item	size	Qty
2	Style 1	XS	S	M	L	2	4	5	6			Style 1	XS	2
3	Style 2	S/M	M/L	L/XL		5	6	7				Style 1	S	4
4	Style 3	OS				16						Style 1	M	5
5												Style 1	L	6
6												Style 2	S/M	5
7												Style 2	M/L	6
8												Style 2	L/XL	7
9												Style 3	OS	16
10
Sheet1

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, 03/17/2017, ME996382
Dim a As Variant, i As Long, c As Long, lr As Long, lc As Long, n As Long
Dim o As Variant, j As Long
With ActiveSheet
  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.CountA(.Range(.Cells(2, 2), .Cells(lr, lc))) / 2
  ReDim o(1 To n + 1, 1 To 3)
  j = j + 1: o(j, 1) = "Item": o(j, 2) = "size": o(j, 3) = "Qty"
  For i = 2 To UBound(a, 1)
    For c = 2 To ((UBound(a, 2) - 1) / 2) + 1
      If Not a(i, c) = vbEmpty Then
        j = j + 1
        o(j, 1) = a(i, 1)
        o(j, 2) = a(i, c)
        o(j, 3) = a(i, c + 4)
      End If
    Next c
  Next i
  .Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(lc + 3).Resize(, 3).AutoFit
End With
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.

 

[Rodo]

Hi Hiker,

Thanks but I am NOT an expert in Macros, do you know any other way???????????

Thanks again!

 

[hiker95]

Hi Hiker,

Thanks but I am NOT an expert in Macros, do you know any other way???????????

Thanks again!

Rodo,

In the future when asking for help, in the title add with a formula.

In your original reply, you could try to add to the existing Title:

with a formula

 

[hiker95]

Rodo,

You have not received a formula solution for your request, so far.

Why not try my macro, based on your flat text displays, to see how it will work?