Exchange rows and columns

[RichardL69]

Hi, Hope everyone is well?
I receive a file with a box number at the top, and a list of items below similar to:

1	2	3
AAA	EEE	III
BBB	FFF	JJJ
CCC	GGG	KKK
DDD	HHH	LLL

What I need to do is change the listing to be a box number for each part supplied, like the below:

1	AAA
1	BBB
1	CCC
1	DDD
2	EEE
2	FFF
2	GGG
2	HHH
3	III
3	JJJ
3	KKK
3	LLL

Does anyone know of a formula where i can list easily what is required without dragging and dropping?
I can switch with copy and paste and put everything left to right, but i need to assign a box number to every part?

 

[gigidica]

You can use copy/paste special and check the transpose button in the lower right of the "Paste Special" box. Do you need a formula for this project?

 

[RichardL69]

You can use copy/paste special and check the transpose button in the lower right of the "Paste Special" box. Do you need a formula for this project?

Hi, yes i can transpose the values but i can't get a formula to look up each box number and list as per the last picture?

 

[RichardL69]

All sorted, i just done a macro to run each time. Thanks

 

[smozgur]

Hi @RichardL69. Welcome to the board!

Glad to hear you got the solution.

Do you mind posting about your solution (the macro you wrote) to help future readers? Then it is perfectly fine to mark your post as the solution.

 

[alansidman]

An alternative solution is to use Power Query called Get and Transform Data in 365 and found on the Data Tab
Unpivot the data on the Tranform Tab of the PQ Editor.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

Book7
	A	B
1	Attribute	Value
2	1	BBB
3	1	DDD
4	1	CCC
5	1	AAA
6	2	GGG
7	2	FFF
8	2	HHH
9	2	EEE
10	3	LLL
11	3	III
12	3	JJJ
13	3	KKK
Table1

 

[smozgur]

Quick and clean solution with Power Query, @alansidman. I would mark it as the solution if @RichardL69 doesn't prefer to share the mentioned solution with us.

I thought it might be a good opportunity to make practice with LET function and creating a loop-kind behavior as I really like LET and LAMBDA recently (Office 365):

1162593.xlsm
	A	B	C	D	E	F
1	1	2	3		A1:C5
2	AAA	EEE	III		1	AAA
3	BBB	FFF	JJJ		1	BBB
4	CCC	GGG	KKK		1	CCC
5	DDD	HHH	LLL		1	DDD
6					2	EEE
7					2	FFF
8					2	GGG
9					2	HHH
10					3	III
11					3	JJJ
12					3	KKK
13					3	LLL
LET_Function

Cell Formulas
Range	Formula
E2:F13	E2	= LET(rng, A1:C5, cols, COLUMNS(rng), rows, ROWS(rng)-1, data, OFFSET(rng,1,,rows,cols), seq, SEQUENCE(rows*cols,2), IF(ISEVEN(seq), LET( x, MOD(seq/2-1,rows)+1, y, ROUNDDOWN((seq/2-1)/rows,0)+1, INDEX(data,x,y) ), INDEX(rng,1,ROUNDDOWN(((seq+1)/2-1)/rows,0)+1) ) )
Dynamic array formulas.

---

Similar solution by using a user-defined function:

Function DISTRIBUTE(rng As Range)
Dim col As Range
Dim cll As Range
Dim i As Integer

    ReDim arr((rng.Rows.Count - 1) * rng.Columns.Count - 1, 1)
  
    For Each col In rng.Columns
        For Each cll In rng.Offset(1, col.Column - rng.Column).Resize(rng.Rows.Count - 1, 1).Cells
            arr(i, 0) = col.Cells(1).Value
            arr(i, 1) = cll.Value
            i = i + 1
        Next cll
    Next col
    DISTRIBUTE = arr
End Function

Result:

1162593.xlsm
	A	B	C	D	E	F
1	1	2	3		A1:C5
2	AAA	EEE	III		1	AAA
3	BBB	FFF	JJJ		1	BBB
4	CCC	GGG	KKK		1	CCC
5	DDD	HHH	LLL		1	DDD
6					2	EEE
7					2	FFF
8					2	GGG
9					2	HHH
10					3	III
11					3	JJJ
12					3	KKK
13					3	LLL
UDF

Cell Formulas
Range	Formula
E2:F13	E2	=DISTRIBUTE(A1:C5)
Dynamic array formulas.

---

And while we already have the UDF, a function-less solution with VBA - takes the selection as the data range:

Sub doDistrubute()
Dim rng As Range
Dim col As Range
Dim cll As Range
Dim i As Integer

    Set rng = Selection
    ReDim arr((rng.Rows.Count - 1) * rng.Columns.Count - 1, 1)
  
    For Each col In rng.Columns
        For Each cll In rng.Offset(1, col.Column - rng.Column).Resize(rng.Rows.Count - 1, 1).Cells
            arr(i, 0) = col.Cells(1).Value
            arr(i, 1) = cll.Value
            i = i + 1
        Next cll
    Next col
    rng.Offset(, rng.Columns.Count + 1).Resize(UBound(arr, 1) + 1, UBound(arr, 2) + 1).Value = arr
End Sub

Obviously, all versions work with the provided range, not limited to the sample range in the original question.