Excel Question #46

Alex · 03-22-2010, 03:27 PM

For the second one:

Assuming all you need is the original records but limited by date.

1. Where you would have put the =LEFT(G2,4) -- presumabely H2 -- put this formula instead:

=IF(MID(G2,5,2)="04", "AAAAA", "ZZZZZ")

2. This formula says if the 5th and 6th character of G2 equals "04" populate the cell with "AAAAA" otherwise populate the cell with "ZZZZZ". Obviously the fill can be anything but AAAAA and ZZZZZ are likely unique making searching feasible.

3. Drag the formula down the entire column.

4. Sort the spreadsheet in ascending order by column H.

5. Search "ZZZZZ" to find the first non April row.

6. Select all the rows below that and delete them.

03-22-2010, 03:27 PM	#10
Alex . Join Date: Feb 2005 Posts: 13,354	For the second one: Assuming all you need is the original records but limited by date. 1. Where you would have put the =LEFT(G2,4) -- presumabely H2 -- put this formula instead: =IF(MID(G2,5,2)="04", "AAAAA", "ZZZZZ") 2. This formula says if the 5th and 6th character of G2 equals "04" populate the cell with "AAAAA" otherwise populate the cell with "ZZZZZ". Obviously the fill can be anything but AAAAA and ZZZZZ are likely unique making searching feasible. 3. Drag the formula down the entire column. 4. Sort the spreadsheet in ascending order by column H. 5. Search "ZZZZZ" to find the first non April row. 6. Select all the rows below that and delete them. Last edited by Alex : 03-22-2010 at 03:37 PM.
	Submit to Quotes