trying to use vlookup?

[clemmon]

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 !

[Richard Buttrey]

Hi,

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

[clemmon]

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!

[xladept]

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)

[clemmon]

hi,

unfortunately i have no idea how to use code. i will try to get google to fill in the blanks for me - unless that is there's an easier way?


thanks

[xladept]

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"

[clemmon]

ok thanks xladept!

last question would just be that it's not immediately obvious what bits of the code i should modify. i can probably use some common sense though...

thanks again

[xladept]

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

[Richard Buttrey]

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

[xladept]

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.

[clemmon]

ok, i'll try that now, thanks...

however, there's another operation i may want to run first?
each time the original data-set cycles from 0 to 1, i want to repeat the data.

so e.g., if the data original read 0 1 0 1 0 0 1 1
i would want it to read -> 0 1 0 1 0 1 0 1 0 0 1 1 0 0 1 1.


make sense? is there a simple way of achieving this?



thanks

[clemmon]

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

the 5 means that there are 5 1s in a row in the original data, then there are 2 zeros, then there are 3 1s next to each other...

make sense now ? sorry!

[clemmon]

kinda drowning in excel atm (not done maths in 10 years)... but how can i reverse the process?

so e.g. if i have 1 3 6 4
that is 0 1 1 1 0 0 0 0 0 0 1 1 1 1

any help?? i don't even know if that's what i want yet


cheers!!!

[clemmon]

btw that code worked great. but idk how to reverse it and that's maybe what i want now.
probably the last thing i need from excel atm.


thanks so much for any further advice

[clemmon]

hi, i can't start new threads - i just get a blank screen when i do. so i can't start one asking about this second problem

so i'll bump this instead ??

[xladept]

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

[clemmon]

well it's ok - i'm using max/msp to sort the data. thanks !

[xladept]

OK then - Good Luck!