i enjoyed the first one dexter.

 

lol on the 1st one.

 

Guys, I need excel help... i have data like this:

Blue1
Red1
Blue2
Red2
Blue3
Red3
Blue4
Red4

and so on...

I need it to look like this:

Blue Red
1 1
2 2
3 3


I have tried all iterations of Vlookup, offset, index, lookup, rows, etc that I can think of but i am stumped... how do you parse/collect alternating rows of data?!?

 

I'm pretty lame when it comes to excel, but can u manipulate the original sheet? If so could you try sorting it by color and then running through the numbers?

 

Dave, you will need a macro or perl script to extract that.

 

I know... I am trying to write a formula or macro to extract it...

I've done (what seems to be) more complicated lookups before but this is stumping me. And to make it worse, there's really no "red/blue" in the data...it's just a HUGE string of numbers. *sigh*

 

if its perfectly alternating you have to use the MOD() function. and tabulate more columns

ie: in another column =IIF(MOD(ROW(),2)=0,1,0)
this will get you part way there.

 

so like
1231
1241
1232
1242
1233
1243

??

 

Dexter, please to expand. thanks!

Yeah...it looks like

504.97
203.65
550.12
203.50
600.23
203.45

One set of data is essentially background readings and is fairly stable...the other is a signal reading and the value changes according to the chemistry we're measuring...

 

Well i know you can do a find and replace and remove the first x characters if they are the same. like in my example if you wanted to remove 123 you could find and replace 123 with null and it would remove all of them.

The sorting is the hard part but if you just want every other number to go to a specific cell you could do something like

Range("A1").Select
Do While Selection <> "" 'loop until it finds the last cell
selection.cut
selection.paste 'to new spot
x = x + 2
Range("A1").Offset(x).Select
Loop

something like that. I would have to formally write it of course.

Edit: the problem is that Excel is robust for a spreadsheet but elementary for a programming language making it very hard to do simple things. Parsing strings within a cell being one of them.

 

4 door lambo = UBER GHEY

 

Alrighty, I've got my IF/Mod statement working and I have a column next to my data of 0s and 1s...

Turco, I like your macro there, I may try to expand on that since I fear that writring all of this in a formula is going to be convoluted...

 

i will continue to work here

 

Dave i found an exercise in the book i have that i am going to try to make work.