trying to use vlookup?

hi,

i have a one column of about 500,000 numbers - either 0 or 1.
i need to know: each time the column reads 1, how many rows until it reads a 0; and then how long until the next 1.

ideally this will leave me with a shorter dataset something like:

1 5
0 2
1 3
0 1
1 8
0 2
1 7
0 11

obviously the first column would be easy to generate if i can generate the second...

thanks for any answers !

Hi,

Your request is not clear. Explain how you arrive at the numbers in column B with reference to the column A cell refs.

oh sorry - column B is the length until the next time the value in column A appears. column A just cycles between 0 and 1.

not actually sure that's the most efficient way of having the data, but yeah. thanks!

Hi clemmon,

If you want to overwrite the original data then only increment the second column index.

Please Login or Register  to view this content.

(I ran my test from G4)

Here are some instructions I copied from one of the major contributors:

First Save Your Book As Macro Enabled - extension .xlsm

Copy the code I posted
With your file open, press Alt+F11 to display the VBA editor
Go to the Insert menu and select Module
Paste the code I provided

After pasting - modify the assignations to conform to your own book.

Then you can play the macro several ways - if you have the worksheet opened before you
and you're looking at the code then place the cursor within the code and hit F5
or GoTo Tools-Macro-Macros-"OnOff"

Modify the assignations for sure because that's where the whole routine is specifically defined.

@clemmon

Hi,
That's just a repeat of what you said originally. What I'm trying to understand is why you have the value 5 against the first value 1 in column A when the next 1 down column A is 2 rows below.

Ditto the 3 in B3. A3 is zero and the next 1 is in A5, 2 rows below not 3

Please explain.

It's a frequency table representing the original column of just 1's and 0's.

If the original data is to be overwritten then only this code:

Please Login or Register  to view this content.

needs to be altered:

Please Login or Register  to view this content.

It's now set up to place the items and their frequencies beside the original data, of course that leaves the problem of what to do with the original data - one option is to delete those lines once they're counted.

The assignments:

Please Login or Register  to view this content.

Are set up to start in G4 from whence I did my testing.

Please Login or Register  to view this content.

By just changing the i - Column and j - row to ones the routine would key on A1.

I don't get it - I'll study it anon.

OK then - Good Luck!