Complicated transpose

[SeanDamnit]

Hello,

I was hoping to get a little help with a complicated transpose. I have data that looks like this:

---APA7373KT---APA9292KT---APA9292WT...
ALBN1PO248---5---1---9...
ALBN1PO249---3---1---1...
ALBN2PO224---4---5---0...
.
.
.

and I would like it to look like this:
ALBN1PO248---APA7373KT---5---APA9292KT---1---APA9292WT---9...
ALBN1PO249---APA7373KT---3---APA9292KT---1---APA9292WT---1...
ALBN2PO224---APA7373KT---4---APA9292KT---5---APA9292WT---0...
.
.
.

The source file will always have a varying amount of data both left to right and top to bottom, so any script used will have to adapt to that. Unfortunately I'm not completely familiar with VBS yet, so I'm not sure where to begin here.

If anyone is so kind as to help me out, please add comments to some of the VBS lines so I can understand why certain steps are used...(that'll make it so I won't have to bug all of you as much )

 

[SeanDamnit]

giving it a try first thing tomorrow - thanks so much for putting time in to this when you didn't have to. I appreciate it!

 

[SeanDamnit]

worked perfect - can't thank you enough

 

[hrlngrv]

You could do this just with formulas. If the original range were named xtab, and the top-left cell of the second range were A11, try these formulas.

A11: =INDEX(xtab,(1+ROWS(A$11:A11))^MOD(COLUMNS($A11:A11),2),1+INT(COLUMNS($A11:A11)/2))

Fill right until it returns #REF!. For this example, the rightmost nonerror cell would be G11. Select A11:G11 and fill down until it returns #REF! in column A.

 

[hiker95]

SeanDamnit,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[SeanDamnit]

oops, spoke too soon. There was an error I somehow overlooked:

In cases where there are multiple Vendor SKUs on the row, it will repeat the second SKU/QTY instead of showing each unique entry.

For example, one of the Purchase Orders in the original Raw Data looks like this (note that I removed irrelevant columns)

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:62px;"><col style="width:62px;"><col style="width:79px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>H</td><td>K</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; font-size:8pt; text-align:center; ">PO #</td><td style="font-weight:bold; font-size:8pt; text-align:center; "># Ordered</td><td style="font-weight:bold; font-size:8pt; text-align:center; ">Vendor SKU</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-weight:bold; font-size:8pt; ">
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-size:8pt; ">ANTI1PO26</td><td style="font-size:8pt; text-align:right; ">6</td><td style="font-size:8pt; text-align:right; ">885909538034</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-size:8pt; ">ANTI1PO26</td><td style="font-size:8pt; text-align:right; ">2</td><td style="font-size:8pt; text-align:right; ">885909538027</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="font-size:8pt; ">ANTI1PO26</td><td style="font-size:8pt; text-align:right; ">1</td><td style="font-size:8pt; ">APX515CKT</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="font-size:8pt; ">ANTI1PO26</td><td style="font-size:8pt; text-align:right; ">2</td><td style="font-size:8pt; ">LG510KIT</td></tr></tbody></table>

I expect the transpose to look like this:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:96px;"><col style="width:147px;"><col style="width:92px;"><col style="width:32px;"><col style="width:92px;"><col style="width:14px;"><col style="width:92px;"><col style="width:14px;"><col style="width:92px;"><col style="width:21px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>ANTI1PO26</td><td>ANTI</td><td style="text-align:right; ">885909538034</td><td style="text-align:right; ">6</td><td style="text-align:right; ">885909538027</td><td style="text-align:right; ">2</td><td>APX515CKT</td><td style="text-align:right; ">1</td><td>LG510KIT</td><td style="text-align:right; ">2</td></tr></tbody></table>
But the macro is producing this:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:96px;"><col style="width:147px;"><col style="width:92px;"><col style="width:14px;"><col style="width:92px;"><col style="width:14px;"><col style="width:92px;"><col style="width:14px;"><col style="width:92px;"><col style="width:21px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>ANTI1PO26</td><td>ANTI</td><td style="text-align:right; ">885909538027</td><td style="text-align:right; ">2</td><td style="text-align:right; ">885909538027</td><td style="text-align:right; ">2</td><td style="text-align:right; ">885909538027</td><td style="text-align:right; ">2</td><td style="text-align:right; ">885909538027</td><td style="text-align:right; ">2</td></tr></tbody></table>
Please let me know if this can be fixed - thanks again!

 

[hiker95]

SeanDamnit,


Sorry I missed the dupes on worksheet Results.


See the instructions in BOLD in the below code, in case you have to change the file path, name(s), and, worksheet names.


After the newest macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
5	ANTI1PO26	ANTI	885909538034	6	885909538027	2	APX515CKT	1	LG510KIT	2
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).

Option Explicit
Sub ReorgDataV4()
' hiker95, 03/01/2011
' http://www.mrexcel.com/forum/showthread.php?t=572298
Dim w1 As Worksheet, wT As Worksheet, wR As Worksheet, wB As Worksheet
Dim wbp As Workbook
Dim a() As Variant, b() As Variant
Dim r As Long, lr As Long, nr As Long, rr As Long, n As Long, nc As Long, fr As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")

'**********
' Change the full path, and name of the file, that contians the "brightpointref" workbook
' Workbooks.Open Filename:="C:\TestData\brightpointref.xlsx"
'**********
Workbooks.Open Filename:="C:\TestData\brightpointref.xlsx"

'**********
' Change the file name, that contians the "brightpointref" workbook
' Set wbp = Workbooks("brightpointref.xlsx")
'**********
Set wbp = Workbooks("brightpointref.xlsx")

'**********
' Change the file name and worksheet name that contains the "brightpointref" raw data, columns A and B
' Set wB = Workbooks("brightpointref.xlsx").Worksheets("Sheet1")
'**********
Set wB = Workbooks("brightpointref.xlsx").Worksheets("Sheet1")

wB.ShowAllData
lr = wB.Cells(Rows.Count, 1).End(xlUp).Row
a = wB.Range("A1:A" & lr)
b = wB.Range("B1:B" & lr)
Application.DisplayAlerts = False
wbp.Close
Application.DisplayAlerts = True
If Not Evaluate("ISREF(hiker95!A1)") Then Worksheets.Add(After:=w1).Name = "hiker95"
Set wT = Worksheets("hiker95")
wT.UsedRange.Clear
w1.UsedRange.Copy wT.Cells(1, 1)
lr = wT.Cells(Rows.Count, 5).End(xlUp).Row
For r = lr To 1 Step -1
  If wT.Cells(r, 5) = "Total:" Or wT.Cells(r, 5) = "" Then wT.Rows(r).Delete
Next r
wT.Columns("L:V").Delete
wT.Columns("I:J").Delete
wT.Columns("B:G").Delete
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
lr = wT.Cells(Rows.Count, 1).End(xlUp).Row
nr = 2
For r = 2 To lr
  n = Application.CountIf(wT.Columns(1), wT.Cells(r, 1).Value)
  wR.Cells(nr, 1) = wT.Cells(r, 1)
  fr = 0
  On Error Resume Next
  fr = Application.Match(Left(wT.Cells(r, 1), 5), a, 0)
  On Error GoTo 0
  wR.Cells(nr, 2) = b(fr, 1)
  nc = 3
  If n = 1 Then
    wR.Cells(nr, 3) = wT.Cells(r, 3)
    wR.Cells(nr, 4) = wT.Cells(r, 2)
  Else
    For rr = r To (r + n - 1)
      wR.Cells(nr, nc) = wT.Cells(rr, 3)
      wR.Cells(nr, nc + 1) = wT.Cells(rr, 2)
      nc = nc + 2
    Next rr
  End If
  r = r + n - 1
  nr = nr + 1
Next r
nc = wR.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
wR.Cells(1, 1) = 1
With wR.Range(wR.Cells(1, 2), wR.Cells(1, nc))
  .FormulaR1C1 = "=RC[-1]+1"
  .Value = .Value
End With
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.DisplayAlerts = False
wT.Delete
Application.DisplayAlerts = True
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


Then run the ReorgDataV4 macro.

 

[SeanDamnit]

that did it - thanks again