Worcester/Boston Social Thread
Evolved Member
iTrader: (13)
Join Date: Sep 2004
Location: Marlboro, MA
Posts: 602
Likes: 0
Received 0 Likes
on
0 Posts
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?!?
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?!?
Evolving Member
iTrader: (2)
Join Date: Aug 2008
Location: MA
Posts: 232
Likes: 0
Received 0 Likes
on
0 Posts
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?!?
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?!?
Evolved Member
iTrader: (13)
Join Date: Sep 2004
Location: Marlboro, MA
Posts: 602
Likes: 0
Received 0 Likes
on
0 Posts
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*
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.
ie: in another column =IIF(MOD(ROW(),2)=0,1,0)
this will get you part way there.
Evolved Member
iTrader: (13)
Join Date: Sep 2004
Location: Marlboro, MA
Posts: 602
Likes: 0
Received 0 Likes
on
0 Posts
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...
EvoM Staff Alumni
iTrader: (16)
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...
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...
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.
Last edited by MR Turco; Sep 22, 2008 at 11:37 AM.
Evolved Member
iTrader: (13)
Join Date: Sep 2004
Location: Marlboro, MA
Posts: 602
Likes: 0
Received 0 Likes
on
0 Posts
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...
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...