Extracting data that is separated by rows.
Posted by Rich Korsmoe on July 24, 2001 9:06 AM
I have some data arranged in rows (seven columns wide). I want to extract the data and match it up by every third row. For example I want to extract the data in rows one four seven ten etc. then come back and extract the data from rows two five eight etc.
Is this possible - I have 1803 rows to sort.
Posted by Mark W. on July 24, 2001 9:43 AM
In the 1st case use =NOT(MOD(ROW()-3-1,3)) to
return TRUE on the appropriate rows. In the
2nd case use =NOT(MOD(ROW()-3-2,3)).
Posted by IML on July 24, 2001 10:00 AM
Probably goes without saying, but if you are sorting, either you will want to copy and paste special values over your formula before sorting. Or, you could create a dummy column with the formula =row() and copy paste special values over that. Then simply replace row() with the cell in Mark's formula.
Good luck.
Posted by Mark W. on July 24, 2001 10:07 AM
Actually, if you're sorting rows these formulas
will not be affected. ROW() always returns the
row number of the cell that contains it. Try it!
Posted by IML on July 24, 2001 10:14 AM
Strange! and cool
Of course you're right. I used
"Bird",TRUE;"dog",FALSE;"dog",FALSE;"Bird",TRUE;"dog",FALSE;"dog",FALSE;"Bird",TRUE;"dog",FALSE;"dog",FALSE;"Bird",TRUE;"dog",FALSE;"dog",FALSE
and keying on the true false column it sorted to
"dog",TRUE;"dog",FALSE;"dog",FALSE;"dog",TRUE;"dog",FALSE;"dog",FALSE;"dog",TRUE;"dog",FALSE;"Bird",FALSE;"Bird",TRUE;"Bird",FALSE;"Bird",FALSE
That blows my tiny little mind away!
Posted by Mark W. on July 24, 2001 10:36 AM
Re: Strange! and cool
Excel is performing the Sort, and then re-evaluating
the formulas. I for one don't believe that a
sort is needed at all. I'd use Advanced AutoFilter
to extract TRUE records to another location (below?)
on the worksheet.
