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!

@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:

Cells(r, i + 1) = H: Cells(r, i + 2) = k: k = 0: r = r + 1

needs to be altered:

Cells(r, i ) = H: Cells(r, i + 1) = k: k = 0: r = r + 1

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:

ASSIGNATIONS:
i = 7: j = 4: r = j: k = 0: S = Cells(j, i): H = S

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

ASSIGNATIONS:
i = 1: j = 1: r = j: k = 0: S = Cells(j, i): H = S

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

Hi clemmon,

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

Sub OnOff(): Dim S As String, H As String
Dim r As Long, i As Long, j As Long, k As Long

ASSIGNATIONS:
i = 7: j = 4: r = j: k = 0: S = Cells(j, i): H = S

Do Until S = ""
S = Cells(j, i)
If S = H Then
k = k + 1
Else
Cells(r, i + 1) = H: Cells(r, i + 2) = k: k = 0: r = r + 1
End If
H = S: j = j + 1: Loop
End Sub

(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.

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

OK then - Good Luck!