In the office, I sometimes deal with a table of values. In that table, there are good rows, and bad rows.
I want to quickly put the good rows to the top of the table, and leave the bad rows at the bottom of the table.
So I take the first unused column, give it a rubbish column header, such as “qwe”, and build a formula in the next row down ready to autofill.
The formula will return TRUE for the goodies, and FALSE for the baddies.
After an autofill, I’ll sort descending by that column, which puts the goodies at the top.
Usually the formula will be something simple, like
In that way, all of the bad rows can be dealt with as a group, rather than scanning through the rows fixing one at a time.
There’s another one I regularly do. It’s “is this value in my other list”. Kind of like an integrity check.
I’ll have two tables – a primary table and a secondary table. I need to know if rows in my primary table are linked to rows in my secondary table.
For example, Employee not in Attendees list. Purchase Order not in Invoices list.
I’ll build a column on the side of the primary table that returns either TRUE or FALSE and use a lookup formula to do the work.
Once I have a column of TRUEs and FALSEs, it’s a simple matter to sort, then focus on the baddies.
In fact, I do these types of yes/no lookups so often that I remember the pattern to building the formula off by heart. Sad, but true.
1. NOT followed by ISNA followed by MATCH
2. Value to look up
3. List to look up
4. 0 for exact match
5. three closing brackets
6. Highlight the “list to look up” text and hit the F4 key to make it absolute.
Now that I think about it, I’ve probably typed that line of text over 1000 times and I really should write a macro for that.
While I’m writing about the lookup, here’s a way of doing a lookup all without an external list.
It’s possible to bring, for example, B3:B9 into the formula while still retaining the MATCH function – what I call an inline lookup.
Simply highlight the B3:B9 text, and press F9 on your keyboard.
By pressing F9, it brings the list into your formula as an array.
Usually you’ll want to leave the list as a range of its own, but depending on the occasion, it could be tidier to have it inline.
Hi Ron,
I do such checks frequently.
Only, I use the COUNTIF function to check whether a given cell value exists in the Primary list or not.
That not only returns ‘0’ for missing items, but also gives me a fair idea how many values are repeated multiple times in the list.
Does your approach give any significant performance improvements (especially in case of large datasets) ?
Cheers!
Khushnood
Mumbai, India
Re: table, I do the exact same thing — create some criteria to sort the rows, insert the appropriate formula into a helper column, then sort by that column.
[quote]I’ll have two tables – a primary table and a secondary table. I need to know if rows in my primary table are linked to rows in my secondary table.
For example, Employee not in Attendees list. Purchase Order not in Invoices list.[/quote]
Don’t let us start that Access discussion again….
[F9] in Edit mode is new to me. (Cool). Any other Edit mode keyboard tricks like that (I know [F4] for absolute/relative toggle).
Ron, in your list of steps #6 can be eliminated by pressing F4 before typing the comma in step 3.
I use a raft of tricks, especially in data extracted from “mainframe” types of databases (am I dating myself here? I’m referring to software like DB2 and – gasp – COBOL flat files. Yes, my employer still has a couple of COBOL things). It’s very annoying to receive a dataset with all the numbers and dates formatted as text.
And F9 in edit trick is neat. I’ll have to remember that.
You could simplify your “yes/no” lookups by using the following:
It saves a few keystrokes and excel functions.
F9 is the same as Ctrl+= (control and equals). I don’t know how I got in the habit of Ctrl+=, but it’s what I use.
Khushnood Viccaji: Often I’ll work with a small number of rows, so performance is not generally an issue. Sometimes larger datasets, and I might have to wait 10 minutes. If I’m performing the same cleansing exercise many times, and each time it’s taking 10 minutes, it’ll be time to move it out of Excel into a database.
AlexJ: I only know of F9 and F4. F9 is a great debugging tool. It allows me to peek at the result of a partial formula, then I hit the ESC key to undo the changes.
Tim: great tip. I really have no excuse for overlooking array formulas.
Ctrl-Z is a better undo than Esc in F9, for the reason that if you are debugging a formula when you are first creating it, F( then Esc will destroy the formula, whereas F9 then Ctrl-Z will correctly undo it.
“By pressing F9, it brings the list into your formula as an array.”
Voted coolest trick of the day/week/month/year/decade in our office …
I generally don’t like the idea of literals embedded in formulas, but I can dream up a case where this kind of trick could make the code more readable.
Hi, GREAT TIP the F9. But it doesn’t work for me! just replace all the text in the cell with a #N/A. I’m doing something wrong?
Bob: I figure the formula already exists, and I’m working my way down nested IFs to the result. If there were multiple levels of undo, it would certainly be my choice.
Chris: Literals in code/formulas are also bad in my book, but I reckon I’m wrong about nearly everything, so I’m probably wrong about that too.
Tim: I don’t think you need to array enter your formula. Mind you, I often don’t think ;-) THe function OR accepts an array, so no need to array enter, yes?
@jeff, really? OR doesn’t natively take an array, I get #VALUE if it isn’t array entered.
Ah. Not really. Don’t know what I was thinking. I think what I’d done is put an array in manually like this:
=OR(A1={“Apple”;”Banana”;”Orange”;”Strawberry”;”Grape”;”Mango”;”Lemon”})
…then noted that you didn’t have to CSE it to get it to evaluate.
But that’s a different kettle of formula altogether.
@Jeff
I’m with @Bob. I get #VALUE! when not array entered.
Hi Rob,
Just one more thing that I usually setup when I’m working on such tables:
If a Record Number column doesn’t already exist, I add it to the table.
This way, when I want to, I can revert to the original sort order of the table, with a single click.
I have added the Sort buttons to my QAT in Excel 2007.
A few other points:
1. The new column can be inserted before the first (or after the last), column in the table.
If the table has a defined range name (especially ‘Database’), then be sure to expand the RefersTo property of the range name, in the Name Manager.
Otherwise, when you sort the table, the Record Number column may not be sorted along with the rest of the table, and values in that column will stay where they are !
2. My preferred method of creating the record number is to type ‘00001 in a new column against the first record.
The ‘ (single-quote) prefix ensures that the record number is created with padded zeros, and is treated as text.
This is a personal preference, for various reasons.
Insert fewer or more zeros in the record number as per the count of records in the table.
3. If the cells in the column to the left or right of this cell are filled all the way down to the last record,
just double-click on the Fill Handle of this cell. It will fill sequential record numbers down the column upto the last record.
If the cells in the adjacent column are not completely filled, then you can do a quick fill-down of the first cell, in the record number column itself.
Then do the double-click step from the first record, to fill sequential record numbers.
Khushnood Viccaji
Mumbai, India
Agree with Khushnood – don’t sort without an index present that will allow you to recover the original order.
An alternative is to filter for the good rows and hide the bad ones. Most operations apply only to the visible rows in filtered view (Formatting, Fill, Find/Replace, Copy, etc.) so you don’t need to change the sort order.
Additionally, applying Advanced Filter can be much quicker than individual lookup formulas, and you can either filter in place and mark the rows by filling down with an “X” or extract data to another sheet. Just add a header to your list of fruit and use this for the criteria range, this can be placed on a separate sheet or a temporary location in a new workbook.
One thing to be aware of is that for text criteria the default compare operation for Advanced Filter and other Database Functions is “begins with”. This often won’t matter but you may want to prefix the fruit with an equals sign to make the match exact (or toggle the “Transition Formula Evaluation” setting but make sure to turn it off after).
I agree with ihm as i think showing the good date int the rows & hiding the bad data is a good option when you are going through a big list of table values.