Converting Vertical data to Selected Horizontal data

[Jwgnwa]

Hi All...I am certainly not a master VBA'r, and I cannot find a way to solve the following problem.

I start with a 2 column data set of varying lengths. Column A has Product codes and Column B has Product information. One of the codes is "PN" in column A, this "PN" indicates the start of a new Part Number. The rows following this "PN" have information relating to that Part Number. The number of rows vary depending on the part. Then when the row after the first "PN" has code "PN" this begins a new part number and information so on and so on until there is a blank row. There could be 3 parts or 1,000 parts and the length of the initial 2 column dataset could be 10 rows or 10,000

[TABLE="class: outer_border, width: 100, align: center"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[/TR]
[TR]
[TD]PN[/TD]
[TD]12345[/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Desk[/TD]
[/TR]
[TR]
[TD]SP[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]75.00[/TD]
[/TR]
[TR]
[TD]QT[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]OB[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]WT[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]PN[/TD]
[TD]98765[/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Shelf[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]35.00[/TD]
[/TR]
[TR]
[TD]MC[/TD]
[TD]R101[/TD]
[/TR]
[TR]
[TD]PN[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Part[/TD]
[/TR]
[TR]
[TD]QT[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Now the problem is that I need to copy each part number and the following required part information for that part number (Just the data from Column B) onto rows in Sheet 2. Sheet 2 is laid out with each required code on the columns as headers for the data. (If Column A on the vertical dataset has a code that is not shown as headers on Sheet 2, the data is not needed and not copied to Sheet 2)So the data for the codes must be copied into the correct column header in Sheet 2. Every Part will have a PN (Part Number) and a PD (Part Description)

​

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD="align: center"]PN[/TD]
[TD="align: center"]PD[/TD]
[TD="align: center"]SP[/TD]
[TD="align: center"]QT[/TD]
[TD="align: center"]SP[/TD]
[TD="align: center"]BP[/TD]
[TD="align: center"]MG[/TD]
[TD="align: center"]OL[/TD]
[TD="align: center"]MC[/TD]
[TD="align: center"]DT[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Desk[/TD]
[TD]100.00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]70.00[/TD]
[TD][/TD]
[TD]Oak[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]98765[/TD]
[TD]Shelf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]35.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]R101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]99999[/TD]
[TD]Part[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[TD]ETC.[/TD]
[/TR]
</tbody>[/TABLE]


I need to have each part number laid out horizontally so that I can import the data for all the parts into QuickBooks.

I have exhausted my limited VBA knowledge after trying to solve this for weeks. I am hoping this is a simple problem and I am just not seeing it. Thanks for your help

 

[Tom.Jones]

Hello,

I would like you to help me with a similar problem.

If posting here is not appropriate, ask a moderator to move it to a new post. Eventually with a new name. Let's say "Rearrange data"

Thank you.


I have a table (I say the table but it is not a real table - with Insert table),

[TABLE="width: 341"]
<tbody>[TR]
[TD="width: 41, bgcolor: transparent"]Cod
[/TD]
[TD="width: 41, bgcolor: transparent"]H1
[/TD]
[TD="width: 41, bgcolor: transparent"]H2
[/TD]
[TD="width: 41, bgcolor: transparent"]H3
[/TD]
[TD="width: 41, bgcolor: transparent"]H4
[/TD]
[TD="width: 41, bgcolor: transparent"]H5
[/TD]
[TD="width: 41, bgcolor: transparent"]H6
[/TD]
[TD="width: 41, bgcolor: transparent"]H7
[/TD]
[TD="width: 41, bgcolor: transparent"]H8
[/TD]
[TD="width: 41, bgcolor: transparent"]H9
[/TD]
[TD="width: 41, bgcolor: transparent"]H10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[/TR]
</tbody>[/TABLE]

which I would like to turn it into

[TABLE="width: 108"]
<tbody>[TR]
[TD="width: 48, bgcolor: transparent"]Cod
[/TD]
[TD="width: 48, bgcolor: transparent"]H
[/TD]
[TD="width: 48, bgcolor: transparent"]TdTx
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H1
[/TD]
[TD="bgcolor: transparent"]Tx1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H2
[/TD]
[TD="bgcolor: transparent"]Tx2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H3
[/TD]
[TD="bgcolor: transparent"]Tx3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H4
[/TD]
[TD="bgcolor: transparent"]Tx4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H5
[/TD]
[TD="bgcolor: transparent"]Tx5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H6
[/TD]
[TD="bgcolor: transparent"]Tx6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H7
[/TD]
[TD="bgcolor: transparent"]Tx7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H8
[/TD]
[TD="bgcolor: transparent"]Tx8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H9
[/TD]
[TD="bgcolor: transparent"]Tx9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]H10
[/TD]
[TD="bgcolor: transparent"]Tx10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H1
[/TD]
[TD="bgcolor: transparent"]Tx11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H2
[/TD]
[TD="bgcolor: transparent"]Tx12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H3
[/TD]
[TD="bgcolor: transparent"]Tx13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H4
[/TD]
[TD="bgcolor: transparent"]Tx14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H5
[/TD]
[TD="bgcolor: transparent"]Tx15
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H6
[/TD]
[TD="bgcolor: transparent"]Tx16
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H7
[/TD]
[TD="bgcolor: transparent"]Tx17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H8
[/TD]
[TD="bgcolor: transparent"]Tx18
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H9
[/TD]
[TD="bgcolor: transparent"]Tx19
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"]H10
[/TD]
[TD="bgcolor: transparent"]Tx20
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H1
[/TD]
[TD="bgcolor: transparent"]Tx21
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H2
[/TD]
[TD="bgcolor: transparent"]Tx22
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H3
[/TD]
[TD="bgcolor: transparent"]Tx23
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H4
[/TD]
[TD="bgcolor: transparent"]Tx24
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H5
[/TD]
[TD="bgcolor: transparent"]Tx25
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H6
[/TD]
[TD="bgcolor: transparent"]Tx26
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H7
[/TD]
[TD="bgcolor: transparent"]Tx27
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H8
[/TD]
[TD="bgcolor: transparent"]Tx28
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H9
[/TD]
[TD="bgcolor: transparent"]Tx29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3
[/TD]
[TD="bgcolor: transparent"]H10
[/TD]
[TD="bgcolor: transparent"]Tx30
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H1
[/TD]
[TD="bgcolor: transparent"]Tx31
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H2
[/TD]
[TD="bgcolor: transparent"]Tx32
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H3
[/TD]
[TD="bgcolor: transparent"]Tx33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H4
[/TD]
[TD="bgcolor: transparent"]Tx34
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H5
[/TD]
[TD="bgcolor: transparent"]Tx35
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H6
[/TD]
[TD="bgcolor: transparent"]Tx36
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H7
[/TD]
[TD="bgcolor: transparent"]Tx37
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H8
[/TD]
[TD="bgcolor: transparent"]Tx38
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H9
[/TD]
[TD="bgcolor: transparent"]Tx39
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"]H10
[/TD]
[TD="bgcolor: transparent"]Tx40
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H1
[/TD]
[TD="bgcolor: transparent"]Tx41
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H2
[/TD]
[TD="bgcolor: transparent"]Tx42
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H3
[/TD]
[TD="bgcolor: transparent"]Tx43
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H4
[/TD]
[TD="bgcolor: transparent"]Tx44
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H5
[/TD]
[TD="bgcolor: transparent"]Tx45
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H6
[/TD]
[TD="bgcolor: transparent"]Tx46
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H7
[/TD]
[TD="bgcolor: transparent"]Tx47
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H8
[/TD]
[TD="bgcolor: transparent"]Tx48
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H9
[/TD]
[TD="bgcolor: transparent"]Tx49
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5
[/TD]
[TD="bgcolor: transparent"]H10
[/TD]
[TD="bgcolor: transparent"]Tx50
[/TD]
[/TR]
</tbody>[/TABLE]


We want formula and if you can and with VBA cod.

Thank you.

 

[sandy666]

this is very easy with PowerQuery

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Cod"}, "Attribute", "Value"),
    Ren = Table.RenameColumns(Unpivot,{{"Attribute", "H"}, {"Value", "TdTx"}})
in
    Ren[/SIZE]

 

[Tom.Jones]

Thank you so much Sandy,

I don't know with PowerQuery, anyway our boss want formula and if possible VBA cod.

Thank you.

 

[sandy666]

sure, good luck

Have a nice day

 

[Tom.Jones]

Someone?

 

[Jwgnwa]

So sorry Peter. The text of the "it's awesome..." message was certainly focused on you. As I said, I had been working on a solution for weeks & you solved it in less than an hour!

The rest of my post was my continual amazement that in this day & time, people like yourself take your time to share your knowledge and help your neighbors with no expectation of return. Thank you and thank all of the moderators & posters for helping those of us in need. Now, I am off to have a large very cold glass of VB.

 

[Rick Rothstein]

@Tom.Jones

See if this macro does what you want...

[table="width: 500"]
[tr]
	[td]Sub RearrangeData()
  Dim R As Long, LR As Range, Data As Variant, HRow As Variant, ColArr As Variant
  Data = Sheets("Sheet1").Range("A1").CurrentRegion
  HRow = Application.Transpose(Sheets("Sheet1").Range("B1").Resize(, UBound(Data, 2) - 1))
  ColArr = Application.Transpose(Evaluate("ROW(2:" & UBound(Data, 2) & ")"))
  For R = 2 To Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Set LR = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    LR.Resize(UBound(Data, 2) - 1) = Sheets("Sheet1").Cells(R, "A").Value
    LR.Offset(, 1).Resize(UBound(Data, 2) - 1) = HRow
    LR.Offset(, 2).Resize(UBound(Data, 2) - 1) = Application.Transpose(Application.Index(Data, R, ColArr))
  Next
End Sub[/td]
[/tr]
[/table]

 

[DanteAmor]

@Tom.Jones

See if this macro does what you want...

Hi Rick, In the example the OP captured Tx1, Tx2, Tx3 ... I guess it's a typo error. Let's wait for your comments.

Here I put another way:

Sub Vertical_horizontal()
Dim n As Long
 n = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column - 1
 For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
  Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).Resize(n).Value = c.Value
  Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2).Resize(n).Value = Application.Transpose(Sheets("Sheet1").Range("B1").Resize(1, n).Value)
  Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp)(2).Resize(n).Value = Application.Transpose(Sheets("Sheet1").Range("B" & c.Row).Resize(1, n).Value)
 Next
End Sub

 

[Tom.Jones]

@Rick, @Dante,

Both VBA codes work great. Thank you.
And yes, Dante, you're right. The first one with A1 is Tx1, Tx2, Tx3 ... and not Tx1, Tx1, Tx1 ....

Thank you very much.

 

[DanteAmor]

@Rick, @Dante,

Both VBA codes work great. Thank you.
And yes, Dante, you're right. The first one with A1 is Tx1, Tx2, Tx3 ... and not Tx1, Tx1, Tx1 ....

Thank you very much.

I'm glad to help you. Thanks for the feedback.