Columns to Rows using Excel Formula

[pto160]

I would like to convert data from columns to rows. I already got a VBA solution for it. Is there an Excel formula solution? Here is the data.

Book2
	A	B	C	D	E	F	G
1		Jan Sales	Feb Sales	Mar Sales	April Sales	May Sales	June Sales
2	Item A		15				89
3	Item B	100		102	108		456
4	Item c			1000		789
5
6	Expected Results
7	Item A	Feb Sales	15
8	Item A	June Sales	89
9	Item B	Jan Sales	100
10	Item B	Mar Sales	102
11	Item B	April Sales	108
12	Item B	June Sales	456
13	Item c	Mar Sales	1000
14	Item c	May Sales	789
Sheet3

 

[mrshl9898]

C7 (copied down) =INDEX($B$2:$G$4,MATCH(A7,$A$2:$A$4,0),MATCH(B7,$B$1:$G$1,0))

 

[pto160]

Thanks. Actually what I'm tying to do by using a formula is to put the Items in column A, then pull the month that have a value for the items by using a formula. So for example, Item A has sales in Feb and June, so the formula would put Item A in A7 and A8 and the month in B7 and B8 and the values in C7 and C8.

 

[pto160]

I'm basically tying to create rows of data in column B7 to B14 for the months that are in column format in B1 to G1 with the items in Column A and the values in column C.
Is there a formula solution?

 

[kweaver]

If you can live with helper columns, see this approach:

How to repeat cell value x times in Excel?

 

[pto160]

I can use that approach for A2:A4 for the items. Not sure how to get it to match up with the months B1:G1 and the values.

 

[pto160]

With the new dynamic arrays and functions in Excel there could be a solution.

 

[GlennUK]

My attempt (long-winded formulas in places, and quite a few array-formulas):

Book8
	A	B	C	D	E	F	G
1		Jan Sales	Feb Sales	Mar Sales	April Sales	May Sales	June Sales
2	Item A		15				89
3	Item B	100		102	108		456
4	Item c			1000		789
5
6	Expected Results
7	Item A	Feb Sales	15	Item A	Feb Sales	15
8	Item A	June Sales	89	Item A	June Sales	89
9	Item B	Jan Sales	100	Item B	Jan Sales	100
10	Item B	Mar Sales	102	Item B	Mar Sales	102
11	Item B	April Sales	108	Item B	April Sales	108
12	Item B	June Sales	456	Item B	June Sales	456
13	Item c	Mar Sales	1000	Item c	Mar Sales	1000
14	Item c	May Sales	789	Item c	May Sales	789
Sheet2

Cell Formulas
Range	Formula
D7	D7	=A2
E7	E7	=INDEX($B$1:$G$1,MATCH(FALSE,ISBLANK($B$2:$G$2),0))
F7	F7	=INDEX($B$2:$G$2,MATCH(FALSE,ISBLANK($B$2:$G$2),0))
D8:D14	D8	=IF(COUNTIF(D$6:D7,D7)<COUNTA(INDEX($B$2:$B$4,MATCH(D7,$A$2:$A$4,0)):INDEX($G$2:$G$4,MATCH(D7,$A$2:$A$4,0))),D7,INDEX($A$2:$A$4,MATCH(D7,$A$2:$A$4,0)+1))
E8:E14	E8	=INDEX($B$1:$G$1,SMALL(IF(NOT(ISBLANK(INDEX($B$2:$B$4,MATCH(D8,$A$2:$A$4,0)):INDEX($G$2:$G$4,MATCH(D8,$A$2:$A$4,0)))),COLUMN($B$1:$G$1)-COLUMN($A$1),1E+233),COUNTIF($D$7:D8,D8)))
F8:F14	F8	=INDEX(INDEX($B$2:$B$4,MATCH(D8,$A$2:$A$4,0)):INDEX($G$2:$G$4,MATCH(D8,$A$2:$A$4,0)),SMALL(IF(NOT(ISBLANK(INDEX($B$2:$B$4,MATCH(D8,$A$2:$A$4,0)):INDEX($G$2:$G$4,MATCH(D8,$A$2:$A$4,0)))),COLUMN($B$1:$G$1)-COLUMN($A$1),1E+233),COUNTIF($D$7:D8,D8)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[pto160]

Fantastic. I didn't think this was possible. Thanks a lot. You're right, the formulas to make this work are long. I have Office 365.
I wonder if having the filter, unique, sequence and other new functions would make this formula shorter.

 

[Peter_SSs]

What about this?
Might fail if your range is large. How big are you likely to be dealing with?

pto160 2020-03-08 1.xlsm
	A	B	C	D	E	F	G
1		Jan Sales	Feb Sales	Mar Sales	April Sales	May Sales	June Sales
2	Item A		15				89
3	Item B	100		102	108		456
4	Item c			1000		789
5
6
7	Item A	Feb Sales	15
8	Item A	June Sales	89
9	Item B	Jan Sales	100
10	Item B	Mar Sales	102
11	Item B	April Sales	108
12	Item B	June Sales	456
13	Item c	Mar Sales	1000
14	Item c	May Sales	789
15
16
17
Sheet1

Cell Formulas
Range	Formula
A7:A17	A7	=IF(B7="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("|"&TEXTJOIN("|",1,IF(B$2:G$4<>"",A$2:A$4,""))&"|","|","#",ROWS(A$7:A7)),"|","#",ROWS(A$7:A7)),"#",REPT(" ",1000)),1000,1000)))
B7:B17	B7	=IF(C7="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("|"&TEXTJOIN("|",1,IF(B$2:G$4<>"",B$1:G$1,""))&"|","|","#",ROWS(B$7:B7)),"|","#",ROWS(B$7:B7)),"#",REPT(" ",1000)),1000,1000)))
C7:C17	C7	=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("|"&TEXTJOIN("|",1,B$2:G$4)&"|","|","#",ROWS(C$7:C7)),"|","#",ROWS(C$7:C7)),"#",REPT(" ",1000)),1000,1000))+0,"")