Transpose Columns To Rows

[AdamPaver]

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

 

[sandy666]

use XL2BB to post your example not a picture

 

[AdamPaver]

Excel Sample.xlsx
	A	B	C	D	E	F	G	H	I
1	SUPPLIER NR	OEM NR_01		SUPPLIER NR	OEM NR_01	OEM NR_02	OEM NR_03	OEM NR_04	OEM NR_05
2	101003	21426987		101003	20513343	51271207037	7420513343	7421426987
3	101003	20513343		101008	21531072	20513340	5010412450	7420513340	7421531072
4	101003	51271207037		101012	20803650	20483889	20514065	7420514065	7420803650
5	101003	7420513343
6	101003	7421426987
7	101008	21531072
8	101008	20513340
9	101008	5010412450
10	101008	7420513340
11	101008	7421531072
12	101012	20803650
13	101012	20483889
14	101012	20514065
15	101012	7420514065
16	101012	7420803650
Sheet1

 

[IneptPig]

Ignore this! I can't delete it but misread the question...

 

[sandy666]

SUPPLIER NR	OEM NR_01		SUPPLIER NR	OEM NR_0.1	OEM NR_0.2	OEM NR_0.3	OEM NR_0.4	OEM NR_0.5
101003	21426987		101003	21426987	20513343	51271207037	7420513343	7421426987
101003	20513343		101008	21531072	20513340	5010412450	7420513340	7421531072
101003	51271207037		101012	20803650	20483889	20514065	7420514065	7420803650
101003	7420513343
101003	7421426987
101008	21531072
101008	20513340
101008	5010412450
101008	7420513340
101008	7421531072
101012	20803650
101012	20483889
101012	20514065
101012	7420514065
101012	7420803650

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]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	SUPPLIER NR	OEM NR_01		SUPPLIER NR	OEM NR_01	OEM NR_02	OEM NR_03	OEM NR_04	OEM NR_05
2	101003	21426987		101003	21426987	20513343	5.1271E+10	7420513343	7421426987
3	101003	20513343		101008	21531072	20513340	5010412450	7420513340	7421531072
4	101003	51271207037		101012	20803650	20483889	20514065	7420514065	7420803650
5	101003	7420513343
6	101003	7421426987
7	101008	21531072
8	101008	20513340
9	101008	5010412450
10	101008	7420513340
11	101008	7421531072
12	101012	20803650
13	101012	20483889
14	101012	20514065
15	101012	7420514065
16	101012	7420803650
17
Data

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
E2:I4	E2	=TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2))
Dynamic array formulas.

 

[mohadin]

VBA Option

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]

For the earlier version of office.

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I
1	SUPPLIER NR	OEM NR_01		SUPPLIER NR	OEM NR_01	OEM NR_02	OEM NR_03	OEM NR_04	OEM NR_05
2	101003	21426987		101003	21426987	20513343	51271207037	7420513343	7421426987
3	101003	20513343		101008	21531072	20513340	5010412450	7420513340	7421531072
4	101003	51271207037		101012	20803650	20483889	20514065	7420514065	7420803650
5	101003	7420513343
6	101003	7421426987
7	101008	21531072
8	101008	20513340
9	101008	5010412450
10	101008	7420513340
11	101008	7421531072
12	101012	20803650
13	101012	20483889
14	101012	20514065
15	101012	7420514065
16	101012	7420803650
54

Cell Formulas
Range	Formula
D2:D4	D2	=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:I4	E2	=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))),"")