Transpose Columns To Rows

AdamPaver

New Member
Joined
Feb 3, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have displayed a sample sheet for reference.

I have a spreadsheet that has in Column A that has Supplier Numbers, and Column B are OEM (Part) numbers. In Column A, there are duplicated values as some suppliers supply more than one part.

What I would like to do, is shift that data from being in two really long columns, to being in a table, with the first column being Supplier Number, then each subsequent one being OEM Number 1, Number 2, etc.

This sheet has thousands of values and I can't work out how to run a formula/VBA script that would automate this process for me. I have done this manually in the attached image, so that hopefully it displays better what I am talking about.

Any help would be much appreciated.

Thank you
 

Attachments

  • excel1.png
    excel1.png
    64.7 KB · Views: 8

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AdamPaver

New Member
Joined
Feb 3, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Excel Sample.xlsx
ABCDEFGHI
1SUPPLIER NROEM NR_01SUPPLIER NROEM NR_01OEM NR_02OEM NR_03OEM NR_04OEM NR_05
210100321426987101003205133435127120703774205133437421426987
3101003205133431010082153107220513340501041245074205133407421531072
41010035127120703710101220803650204838892051406574205140657420803650
51010037420513343
61010037421426987
710100821531072
810100820513340
91010085010412450
101010087420513340
111010087421531072
1210101220803650
1310101220483889
1410101220514065
151010127420514065
161010127420803650
Sheet1
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

SUPPLIER NROEM NR_01SUPPLIER NROEM NR_0.1OEM NR_0.2OEM NR_0.3OEM NR_0.4OEM NR_0.5
1010032142698710100321426987205133435127120703774205133437421426987
101003205133431010082153107220513340501041245074205133407421531072
1010035127120703710101220803650204838892051406574205140657420803650
1010037420513343
1010037421426987
10100821531072
10100820513340
1010085010412450
1010087420513340
1010087421531072
10101220803650
10101220483889
10101220514065
1010127420514065
1010127420803650

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SUPPLIER NR"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "OEM NR_0", each [Count][OEM NR_01]),
    Extract = Table.TransformColumns(List, {"OEM NR_0", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount = Table.AddColumn(Extract, "Split Count", each List.Count(Text.Split([OEM NR_0],","))),
    MaxCount = List.Max(SplitCount[Split Count]),
    Split = Table.SplitColumn(Table.TransformColumnTypes(Extract, {{"OEM NR_0", type text}}, "en-GB"), "OEM NR_0", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), MaxCount),
    Type = Table.TransformColumnTypes(Split,{{"SUPPLIER NR", type text}})
in
    Type
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,451
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEFGHI
1SUPPLIER NROEM NR_01SUPPLIER NROEM NR_01OEM NR_02OEM NR_03OEM NR_04OEM NR_05
21010032142698710100321426987205133435.1271E+1074205133437421426987
3101003205133431010082153107220513340501041245074205133407421531072
41010035127120703710101220803650204838892051406574205140657420803650
51010037420513343
61010037421426987
710100821531072
810100820513340
91010085010412450
101010087420513340
111010087421531072
1210101220803650
1310101220483889
1410101220514065
151010127420514065
161010127420803650
17
Data
Cell Formulas
RangeFormula
D2:D4D2=UNIQUE(FILTER(A2:A100,A2:A100<>""))
E2:I4E2=TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2))
Dynamic array formulas.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
VBA Option
VBA Code:
Sub testr()
    Dim a As Variant, i
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), a(i, 2)
                Else
                    .Item(a(i, 1)) = .Item(a(i, 1)) & "#" & a(i, 2)
                End If
            End If
        Next
       Cells(2, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
       Cells(2, 5).Resize(.Count).TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
         Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 1))
    End With
End Sub
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
For the earlier version of office.

Excel Message board.xlsm
ABCDEFGHI
1SUPPLIER NROEM NR_01SUPPLIER NROEM NR_01OEM NR_02OEM NR_03OEM NR_04OEM NR_05
21010032142698710100321426987205133435127120703774205133437421426987
3101003205133431010082153107220513340501041245074205133407421531072
41010035127120703710101220803650204838892051406574205140657420803650
51010037420513343
61010037421426987
710100821531072
810100820513340
91010085010412450
101010087420513340
111010087421531072
1210101220803650
1310101220483889
1410101220514065
151010127420514065
161010127420803650
54
Cell Formulas
RangeFormula
D2:D4D2=IFERROR(INDEX($A$2:$A$16,AGGREGATE(15,6,(ROW($A$2:$A$16)-ROW($A$2)+1)/(FREQUENCY($A$2:$A$16,$A$2:$A$16)>0),ROWS($D$2:D2))),"")
E2:I4E2=IFERROR(INDEX($B$2:$B$16,AGGREGATE(15,6,((ROW($A$2:$A$16)-ROW($A$2)+1)/($D2=$A$2:$A$16)),COLUMNS($E$2:E2))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,323
Messages
5,635,603
Members
416,869
Latest member
JeffK26

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
Top