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

Vendor	1000056
SearchTerm	MACHINE PARTS
Vendor	1000059
SearchTerm	MACHINE PARTS
Vendor	1000065
SearchTerm	MACHINE PARTS
Vendor	1000076
SearchTerm	MACHINE PARTS
Vendor	1000094
SearchTerm	PACKAGING
Vendor	1000098
SearchTerm	MACHINE PARTS

<colgroup><col><col></colgroup><tbody>
</tbody>

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?

SOURCE			RESULT
A	B		Vendor	SearchTerm
Vendor	1000056​		1000056​	MACHINE PARTS
SearchTerm	MACHINE PARTS		1000059​	MACHINE PARTS
Vendor	1000059​		1000065​	MACHINE PARTS
SearchTerm	MACHINE PARTS		1000076​	MACHINE PARTS
Vendor	1000065​		1000094​	PACKAGING
SearchTerm	MACHINE PARTS		1000098​	MACHINE PARTS
Vendor	1000076​
SearchTerm	MACHINE PARTS
Vendor	1000094​
SearchTerm	PACKAGING
Vendor	1000098​
SearchTerm	MACHINE PARTS

[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