Formula to paste Data from column to Row (Non contiguous cells)

[cidfidou]

Hi Excel Masters,

Here is my issue. I have a list of data listed in Columns and I would need to retrieve those data in a row format in non contiguous cells.


-- removed inline image ---


I would need to a formula to paste down to give me J2 in C2, K2 on C3, L2 in C4 and then J3 in C5, K3 in C6 , L3 in C7 and so on...
As usual, Thanks in advance

 

[dirtychinchilla]

If I understand what you're saying correctly, select the columns and then go to the area where you want to paste them. Right click, paste special, and tick transpose on the bottom right.

 

[kevatarvind]

HI YOU CAN DO THIS WITH HELP OF ONE HELPER COLUMN DO THE FOLLOWING

First Put
D2=2
D3=2
D4=2

And in D5 = =D2+1 and Drag Down As you need and

Now Put in Column C
C2 =INDIRECT("J"&D2)
C3 =INDIRECT("k"&D3)
C4 =INDIRECT("l"&D4)

AND NOW SELECT ALL 3 CELL C2:C4 AND DRAG DOWN AS YOU NEED THATS IT

 

[cidfidou]

Thanks DirtyChinchila but my spreadsheet is quite big...

Keva thank you for your solution.... do you think it is possible without one helper column? i have already thought bout it but doing that in spreadsheet will mean rework??

 

[venkat1926]

try macro

Sub test()
Dim r As Range, c As Range


Set r = Range(Range("J2"), Range("j2").End(xlToRight).End(xlDown))
For Each c In r
c.Copy
Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial


Next c
end sub

 

[kevatarvind]

After Thinking more so one formula can do it easily try like below

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
2	1	*	*	*	*	*	*	1	2	3
3	2	*	*	*	*	*	*	4	5	6
4	3	*	*	*	*	*	*	7	8	9
5	4	*	*	*	*	*	*	10	11	12
6	5	*	*	*	*	*	*	13	14	15
7	6	*	*	*	*	*	*	16	17	18
8	7	*	*	*	*	*	*	19	20	21
9	8	*	*	*	*	*	*	22	23	24
10	9	*	*	*	*	*	*	25	26	27
11	10	*	*	*	*	*	*	28	29	30
12	11	*	*	*	*	*	*	31	32	33
13	12	*	*	*	*	*	*	34	35	36
14	13	*	*	*	*	*	*	37	38	39
15	14	*	*	*	*	*	*	40	41	42
16	15	*	*	*	*	*	*	43	44	45
17	16	*	*	*	*	*	*	46	47	48
18	17	*	*	*	*	*	*	49	50	51
19	18	*	*	*	*	*	*	52	53	54
20	19	*	*	*	*	*	*	55	56	57
21	20	*	*	*	*	*	*	58	59	60
22	21	*	*	*	*	*	*	61	62	63
23	22	*	*	*	*	*	*	64	65	66
Sheet2

 

[cidfidou]

Thanks Keva... your formula is amaaazing!!!!!