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

Column A	Column B
PN	12345
PD	Desk
SP	100.00
BP	75.00
QT	2
OB	87
WT	75
PN	98765
PD	Shelf
BP	35.00
MC	R101
PN	99999
PD	Part
QT	4

<tbody>
</tbody>

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)

​

PN	PD	SP	QT	SP	BP	MG	OL	MC	DT
12345	Desk	100.00	2		70.00		Oak
98765	Shelf				35.00			R101
99999	Part
ETC.	ETC.	ETC.	ETC.	ETC.	ETC.	ETC.	ETC.	ETC.	ETC.

<tbody>
</tbody>

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

 

[Rick Rothstein]

You show two columns in your desired output with the header text "SP"... I am assuming the second one it mislabeled, so what should it really be?

 

[Jwgnwa]

Sorry The second "SP" should be "GP", Thanks

 

[sandy666]

with Power Query, just for fun
this is not vba!

Code	Value		PN	PD	SP	BP	QT	OB	WT	MC
PN	12345​		12345	Desk	100	75	2	87	75	R101
PD	Desk		98765	Shelf		35	4
SP	100​		99999	Part
BP	75​
QT	2​
OB	87​
WT	75​
PN	98765​
PD	Shelf
BP	35​
MC	R101
PN	99999​
PD	Part
QT	4​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Value", type text}}),
    Group = Table.Group(Type, {"Code"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Value", each Table.Column([Count],"Value")),
    Extract = Table.TransformColumns(List, {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Rem = Table.RemoveColumns(Extract,{"Count"}),
    Split = Table.SplitColumn(Rem, "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    Demote = Table.DemoteHeaders(Split),
    Transpose = Table.Transpose(Demote),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    Rem2 = Table.RemoveColumns(Promote,{"Code"})
in
    Rem2[/SIZE]

 

[sandy666]

ignore post above, correct result is here:

Code	Value		PN	PD	SP	BP	QT	OB	WT	MC
PN	12345​		12345	Desk	100	75	2	87	75
PD	Desk		98765	Shelf		35				R101
SP	100​		99999	Part			4
BP	75​
QT	2​
OB	87​
WT	75​
PN	98765​
PD	Shelf
BP	35​
MC	R101
PN	99999​
PD	Part
QT	4​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Value", type text}}),
    Condition = Table.AddColumn(Type, "PN", each if [PLAIN][Code][/PLAIN] = "PN" then [Value] else null),
    FillD = Table.FillDown(Condition,{"PN"}),
    Filter = Table.SelectRows(FillD, each ([PLAIN][Code][/PLAIN] <> "PN")),
    Group = Table.Group(Filter, {"Code", "PN"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Value", each Table.Column([Count],"Value")),
    Extract = Table.TransformColumns(List, {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Rem = Table.RemoveColumns(Extract,{"Count"}),
    Pivot = Table.Pivot(Rem, List.Distinct(Rem[PLAIN][Code][/PLAIN]), "Code", "Value")
in
    Pivot[/SIZE]

 

[Peter_SSs]

See if this does what you want. Check the sheets names are correct and test in a copy of your workbook.

Sub Parts_List()
  Dim aData As Variant, aResults As Variant, aProdInfo As Variant
  Dim cols As Long, rws As Long, parts As Long, i As Long, k As Long, col As Long
  
  With Sheets("Sheet2")
    cols = .Cells(1, .Columns.Count).End(xlToLeft).Column
    aProdInfo = .Range("A1").Resize(, cols).Value
  End With
  With Sheets("Sheet1")
    aData = .Range("A1", .Range("B" & .Rows.Count).End(xlUp)).Value
    rws = UBound(aData)
    parts = Evaluate("countif('" & .Name & "'!A1:A" & rws & ",""PN"")")
  End With
  If parts > 0 Then
    ReDim aResults(1 To parts, 1 To cols)
    For i = 2 To rws
      If aData(i, 1) = "PN" Then
        k = k + 1
        aResults(k, 1) = aData(i, 2)
      Else
        col = 0
        On Error Resume Next
        col = Application.Match(aData(i, 1), aProdInfo, 0)
        On Error GoTo 0
        If col > 0 Then aResults(k, col) = aData(i, 2)
      End If
    Next i
    Sheets("Sheet2").Range("A2").Resize(k, cols).Value = aResults
  End If
End Sub

 

[Jwgnwa]

It's awesome...Still amazing to find people that are willing to help other people that they don't know, just because they can. Thanks for that.

 

[Jwgnwa]

Thanks Sandy, I have not used Power Query, but will be sure to look at it for future uses.

 

[sandy666]

Thanks Sandy, I have not used Power Query, but will be sure to look at it for future uses.

You are welcome

 

[Peter_SSs]

It's awesome...Still amazing to find people that are willing to help other people that they don't know, just because they can. Thanks for that.

Given your other post that was addressed to Sandy, I'm wondering if this comment was in relation to my suggestion? If so, you are very welcome. If not, you are still very welcome.