Formula to copy every other cell value from a single row to a single column,
Hi All,
I have some data in a spread sheet which is on a row, with only one entry every 4 columns.
Now I want to transpose this to a single column but don't want any of the empty cells. Now have tried using "INDIRECT" and this will indeed allow me to select every 4 rows but only horizontally i.e. across the columns. Now I am pretty sure I have to use a combination of formulas to achieve this but which ones I am not to sure as well as how so any help would be greatly appreciated.
Example below.
How it looks...
A B C D E F G H I J K L M..........
1 5 9 7
Using Indirect I get...
A B C D E F G..........
1 5 9 7
Which works as it only gives me the cells I need, however want it as follows;
A
1
5
9
7
Note I only shown 4 entries, but there is many more.
Is there another way of doing this, which I am just not seeing.
Thanks Again.
Andy
|