View Single Post
  #1   Report Post  
Old June 6th 18, 09:55 AM posted to microsoft.public.excel.worksheet.functions
Andy McNicol Andy McNicol is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default 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