Separating data from a single column to two columns

[Robert_Conklin]

I have a list of data that that has two options: "Vendor" and "SearchTerm" As of right now my data looks like this:

Column A Column B
[TABLE="width: 227"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]1000056[/TD]
[/TR]
[TR]
[TD]SearchTerm[/TD]
[TD]MACHINE PARTS[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]1000059[/TD]
[/TR]
[TR]
[TD]SearchTerm[/TD]
[TD]MACHINE PARTS[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]1000065[/TD]
[/TR]
[TR]
[TD]SearchTerm[/TD]
[TD]MACHINE PARTS[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]1000076[/TD]
[/TR]
[TR]
[TD]SearchTerm[/TD]
[TD]MACHINE PARTS[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]1000094[/TD]
[/TR]
[TR]
[TD]SearchTerm[/TD]
[TD]PACKAGING[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]1000098[/TD]
[/TR]
[TR]
[TD]SearchTerm[/TD]
[TD]MACHINE PARTS
[/TD]
[/TR]
</tbody>[/TABLE]


I am in need of a VBA macro to make my data look like this:

Vendor SearchTerm
1000056 MACHINE PARTS
1000059 MACHINE PARTS
1000065 MACHINE PARTS

etc...

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]SOURCE[/td][td][/td][td][/td][td]RESULT[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]A[/td][td=bgcolor:#5B9BD5]B[/td][td][/td][td=bgcolor:#70AD47]Vendor[/td][td=bgcolor:#70AD47]SearchTerm[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vendor[/td][td=bgcolor:#DDEBF7]

1000056​

[/td][td][/td][td=bgcolor:#E2EFDA]

1000056​

[/td][td=bgcolor:#E2EFDA]MACHINE PARTS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SearchTerm[/td][td]MACHINE PARTS[/td][td][/td][td]

1000059​

[/td][td]MACHINE PARTS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vendor[/td][td=bgcolor:#DDEBF7]

1000059​

[/td][td][/td][td=bgcolor:#E2EFDA]

1000065​

[/td][td=bgcolor:#E2EFDA]MACHINE PARTS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SearchTerm[/td][td]MACHINE PARTS[/td][td][/td][td]

1000076​

[/td][td]MACHINE PARTS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vendor[/td][td=bgcolor:#DDEBF7]

1000065​

[/td][td][/td][td=bgcolor:#E2EFDA]

1000094​

[/td][td=bgcolor:#E2EFDA]PACKAGING[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SearchTerm[/td][td]MACHINE PARTS[/td][td][/td][td]

1000098​

[/td][td]MACHINE PARTS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vendor[/td][td=bgcolor:#DDEBF7]

1000076​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]SearchTerm[/td][td]MACHINE PARTS[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vendor[/td][td=bgcolor:#DDEBF7]

1000094​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]SearchTerm[/td][td]PACKAGING[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vendor[/td][td=bgcolor:#DDEBF7]

1000098​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]SearchTerm[/td][td]MACHINE PARTS[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type any}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"B")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vendor", Int64.Type}, {"SearchTerm", type text}})
in
    #"Changed Type2"[/SIZE]

try PowerQuery (Get&Transform)
Excel 2010/2013 - add-in
Excel 2016 and higher - built-in

 

[JoeMo]

Assuming your data start in A1, this will reconfigure it starting in D1.

Sub ReconfigureData()
Dim Vin As Variant, Vout As Variant, i As Long
Vin = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
ReDim Vout(1 To UBound(Vin, 1) + 1, 1 To 2)
Vout(1, 1) = "Vendor": Vout(1, 2) = "SearchTerm"
For i = 1 To UBound(Vin, 1)
    Select Case Vin(i, 1)
        Case "Vendor": Vout(i + 1, 1) = Vin(i, 2)
        Case "SearchTerm": Vout(i, 2) = Vin(i, 2)
    End Select
Next i
Application.ScreenUpdating = False
With Range("D1").Resize(UBound(Vout, 1), 2)
    .Value = Vout
    .EntireColumn.AutoFit
    .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

IF those Vendor numbers are in fact numerical, then you could try something like this.

Sub Vendor_Search()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .SpecialCells(xlConstants, xlNumbers).Copy Destination:=.Cells(1, 2)
    .SpecialCells(xlConstants, xlTextValues).Copy Destination:=.Cells(1, 3)
  End With
End Sub

My sample data (cols A:B) & code results (cols C:D)

Book1
	A	B	C	D
1
2	Vendor	1000056	1000056	MACHINE PARTS
3	SearchTerm	MACHINE PARTS	1000059	MACHINE PARTS
4	Vendor	1000059	1000065	MACHINE PARTS
5	SearchTerm	MACHINE PARTS	1000076	MACHINE PARTS
6	Vendor	1000065	1000094	PACKAGING
7	SearchTerm	MACHINE PARTS	1000098	MACHINE PARTS
8	Vendor	1000076
9	SearchTerm	MACHINE PARTS
10	Vendor	1000094
11	SearchTerm	PACKAGING
12	Vendor	1000098
13	SearchTerm	MACHINE PARTS
Vendor Search