Formula to turn 7 columns into 2 columns of data?

[tonywatsonhelp]

Hi Everyone,
I currently have a situation where i need to combine 7 columns of data into 2 columns using only formulas?
(I can do this with VBA but we are sharing this over excel online and VBA does not work.)

so Column C has the names in it, Columns E G J L N P have the data i want to turn into one column

So in S for example I'll have the names in column C repeated 6 times, in T ill have all data in E then G, J and so on.
you can assume the Columns start at row 10 and go down to the last bit of data, every columns has same number of data in it.

Please help if you can
Thanks
Tony

 

[Toadstool]

Hi TonyWatsonHelp,

Does this do what you want?

TonyWatsonHelp3.xlsx
	C		E		G			J		L		N		P	Q	R	S	T
9	IamC		IamE		IamG			IamJ		IamL		IamN		IamP			S	T
10	Bob		BobIamE		BobIamG			BobIamJ		BobIamL		BobIamN		BobIamP			Bob	BobIamE
11	Jim		JimIamE		JimIamG			JimIamJ		JimIamL		JimIamN		JimIamP			Bob	BobIamG
12	Sue		SueIamE		SueIamG			SueIamJ		SueIamL		SueIamN		SueIamP			Bob	BobIamJ
13	Tim		TimIamE		TimIamG			TimIamJ		TimIamL		TimIamN		TimIamP			Bob	BobIamL
14																	Bob	BobIamN
15																	Bob	BobIamP
16																	Jim	JimIamE
17																	Jim	JimIamG
18																	Jim	JimIamJ
19																	Jim	JimIamL
20																	Jim	JimIamN
21																	Jim	JimIamP
22																	Sue	SueIamE
23																	Sue	SueIamG
24																	Sue	SueIamJ
Sheet1

Cell Formulas
Range	Formula
S10:S24	S10	=IF((ROW()-10)>=COUNTA($C$10:$C$9999)*6,"",INDEX($C$10:$C$9999,INT((ROW()-4)/6)))
T10:T24	T10	=IF(S10="","",INDEX($E$10:$P$9999,INT((ROW()-4)/6),INDEX({1,3,6,8,10,12},INT(MOD((ROW()-4),6))+1)))

 

[Fluff]

Another option, if you have the LET function in 365.

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
9	IamC		IamE		IamG			IamJ		IamL		IamN		IamP			Bob	BobIamE
10	Bob		BobIamE		BobIamG			BobIamJ		BobIamL		BobIamN		BobIamP			Bob	BobIamG
11	Jim		JimIamE		JimIamG			JimIamJ		JimIamL		JimIamN		JimIamP			Bob	BobIamJ
12	Sue		SueIamE		SueIamG			SueIamJ		SueIamL		SueIamN		SueIamP			Bob	BobIamL
13	Tim		TimIamE		TimIamG			TimIamJ		TimIamL		TimIamN		TimIamP			Bob	BobIamN
14																	Bob	BobIamP
15																	Jim	JimIamE
16																	Jim	JimIamG
17																	Jim	JimIamJ
18																	Jim	JimIamL
19																	Jim	JimIamN
20																	Jim	JimIamP
21																	Sue	SueIamE
22																	Sue	SueIamG
23																	Sue	SueIamJ
24																	Sue	SueIamL
25																	Sue	SueIamN
26																	Sue	SueIamP
27																	Tim	TimIamE
28																	Tim	TimIamG
29																	Tim	TimIamJ
30																	Tim	TimIamL
31																	Tim	TimIamN
32																	Tim	TimIamP
33
Master

Cell Formulas
Range	Formula
S9:T32	S9	=LET(LastRow,LOOKUP(2,1/(C10:C9999<>""),ROW(C10:C9999))-9,Qty,SEQUENCE(LastRow*6,,0),INDEX($C$10:$P$9999,INT(Qty/6)+1,CHOOSE(MOD(Qty,6)+1,{1,3},{1,5},{1,8},{1,10},{1,12},{1,14})))
Dynamic array formulas.

 

[alansidman]

One more option: Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IamC", type text}, {"Column1", type any}, {"IamE", type text}, {"Column2", type any}, {"IamG", type text}, {"Column3", type any}, {"Column4", type any}, {"IamJ", type text}, {"Column5", type any}, {"IamL", type text}, {"Column6", type any}, {"IamN", type text}, {"Column7", type any}, {"IamP", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"IamC", "IamE", "IamG", "IamJ", "IamL", "IamN", "IamP"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"IamC"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

 

[Peter_SSs]

Another 365 option (assuming no blanks in the column C data)

21 09 27.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
9	IamC		IamE		IamG			IamJ		IamL		IamN		IamP			Bob	BobIamE
10	Bob		BobIamE		BobIamG			BobIamJ		BobIamL		BobIamN		BobIamP			Bob	BobIamG
11	Jim		JimIamE		JimIamG			JimIamJ		JimIamL		JimIamN		JimIamP			Bob	BobIamJ
12	Sue		SueIamE		SueIamG			SueIamJ		SueIamL		SueIamN		SueIamP			Bob	BobIamL
13	Tim		TimIamE		TimIamG			TimIamJ		TimIamL		TimIamN		TimIamP			Bob	BobIamN
14																	Bob	BobIamP
15																	Jim	JimIamE
16																	Jim	JimIamG
17																	Jim	JimIamJ
18																	Jim	JimIamL
19																	Jim	JimIamN
20																	Jim	JimIamP
21																	Sue	SueIamE
22																	Sue	SueIamG
23																	Sue	SueIamJ
24																	Sue	SueIamL
25																	Sue	SueIamN
26																	Sue	SueIamP
27																	Tim	TimIamE
28																	Tim	TimIamG
29																	Tim	TimIamJ
30																	Tim	TimIamL
31																	Tim	TimIamN
32																	Tim	TimIamP
7 to 2

Cell Formulas
Range	Formula
S9:T32	S9	=LET(s,SEQUENCE(COUNTA(C10:C9999)*6,,0,1/6),INDEX(C10:P9999,s+1,CHOOSE(MOD(ROUND(s*6,0),6)+1,{1,3},{1,5},{1,8},{1,10},{1,12},{1,14})))
Dynamic array formulas.