On Wednesday, 6 June 2018 12:05:11 UTC+1, Claus Busch wrote:
Hi Andy,

Am Wed, 6 Jun 2018 03:51:06 -0700 (PDT) schrieb Andy McNicol:

Your help is much appreciated, now have it all in the same row which is good, however not quite there as getting the following results;

A
0
21
0
23
0
43
0
34

Also should have probably been clearer but want to retain the original data as well so no overwrite in A1. So for testing purposes added the formula into Cell A4, but for some reason it ignores the first 2 values in the row and still returns a 0 between the values.

I thought you would have the results in another sheet.
You have data in every other cell. So you have to multiply ROW() with 2.
When you start in row 4 you must change the formula to:
=INDEX($1:$1,,ROW()*2-7)


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,




Thanks again,




And apologies you are right ultimately it will end up on a different sheet, so probably should have done that, but wanted to view the results on the same sheet for test purposes so my fault.




Anyway our help is greatly appreciated as was pulling my hair out as I know I have done this before, but for the life of me cannot remember what and why I used it. So many spread sheets I have been through over the years.




Just one thing now and then I am sorted, I am still; getting a 0 between each of numbers./ Thus how do I get rid of this so I only get the cells with the actual values in it. Note that on the original sheet there will be an entry every 5th cell on the row.




Regards,




Andy