# Conditional Numbering of List Values

• Hello All,

I have a list of 48 000 zeros and ones (0 and 1s) (and 12 separate lists).

If there is a 1 then I will return a zero.
If there is a zero then I need to find the next 1 and return the 'distance' to that 1.

For example
1
1
1
1
0
0
0
0
1

The first zero will have to return 4, the second 3 etc.

I have tried using Match(1,[range],0) but this takes for ever to run.
I could write a udf to do this but figure that it would have to be based on a loop. Can anyone think of a clever way of doing this?

In the mean time I will implement the udf.

Thanks,

Alan.

• Re: Finding Next 1 In A List Of 0 And 1

Ouch.

I would turn the data around so that you have 12 rows rather than 48000, read it directly from the data file (if you can) and try using the string operations such as Instr which can take a start point in the string to start searching from.

Just output the results into Excel.

If it's not a state secret, what are you doing ??

regards

Rich

Regards

Rich

• Re: Finding Next 1 In A List Of 0 And 1

Rich,

I am analysisng 15 years of offshore wave data in 3 hourly intervals.

I thought of writing a time series analysis tool that processed it sequentially, however there are several things that I am analysing for and Excel has done very well so far. This one may be the one that makes me go back to a time series analysis tool and output the results to Excel.

However if I can avoid this I would like to...

I have written the udf and I am running it over 6 columns of data at the moment. It appears to be working but will still take a long time and therefore I may as well do a time series analysis tool...

Anyone else have an idea?

A.

• Re: Finding Next 1 In A List Of 0 And 1

Kris,

This still takes forever to run.

I have implemented the udf it takes too long. I will now look at developing a more generic analysis tool.

Thanks for help everyone,

A.

• Re: Finding Next 1 In A List Of 0 And 1

Hi A9192Shark

Does this do what you want ?

This takes about 2 seconds for one string, including the output.

Regards

Rich

Regards

Rich

• Re: Finding Next 1 In A List Of 0 And 1

Rich,

Thanks for the suggestion. I have solved the problem by telling my boss that it really is not required. This is a reasonable statement at this stage but we will want teh info later.

I will look into the idea of using a string to store the data. I think that I could implement something using this but it will be a peice of code rather than a function.

Your code runs very quickly because you fill the data from a loop, I have to fill it from a worksheet where the numbers are calculated.

Thanks for your help- I will look at using teh string when I solve this one later in teh year!

Cheers,

Alan.

• Re: Finding Next 1 In A List Of 0 And 1

Hi Alan,

What I'm trying to say is don't always get stuck on looking at data in the way it is presented to you at first.

It may be quicker to write a program to extract the 0's and 1's from the spreadsheet, put them into a string and then call the subroutines I've written rather than trying to do it directly from the spreadsheet.

Whatever you do, don't get it fixed in your head that you must use the data as is. If a bit of re-arranging (not creative mind you!) helps, and it's going to be faster in the long run, go for it!

Regards

Rich

Regards

Rich

• Re: Finding Next 1 In A List Of 0 And 1

I agree with you.

If I was allowed the time I would develop a program to read the original text file and do alot more with teh data. As it is I did a calc using Excel that works very well. I was then asked the next logical question and tried to look at it using the excel sheet.

Cheers,

A.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!