Separating data from a single column to two columns

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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...
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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]

Code:
[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
 
Last edited:
Upvote 0
Assuming your data start in A1, this will reconfigure it starting in D1.
Code:
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
 
Upvote 0
IF those Vendor numbers are in fact numerical, then you could try something like this.

Code:
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
ABCD
1
2Vendor10000561000056MACHINE PARTS
3SearchTermMACHINE PARTS1000059MACHINE PARTS
4Vendor10000591000065MACHINE PARTS
5SearchTermMACHINE PARTS1000076MACHINE PARTS
6Vendor10000651000094PACKAGING
7SearchTermMACHINE PARTS1000098MACHINE PARTS
8Vendor1000076
9SearchTermMACHINE PARTS
10Vendor1000094
11SearchTermPACKAGING
12Vendor1000098
13SearchTermMACHINE PARTS
Vendor Search
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top