Excel Question - Teach me how to use If function?

Underdog

So I'm trying to figure out how to work through a spreadsheet of information but most of my Excel experience has been gained through poking around until I can get it to work. I've got about 900 rows to look through and I'd really rather not do it manually. I started with 9000 though, so I feel better about my work. Basically, I have four columns that matter in each row, name, currency, purpose and account number. For each row, if the values in the first three columns match one or more other rows, I'd like someway to indicate that I need to review those particular rows. Is there anyway to do this simply and without the need to review all 900 rows?

I can't see how to make it work with a VLOOKUP and can't figure out how If or Match functions work. Actually, now that I think about it, I really only need to check the name. Basically, if there should only be one entry for each name and I want a formula that tells me when I have a name coming up more than once. Or maybe just have something look up the list of names and tell me how many times each one shows up in the data. Any help would be appreciated, I'm really at the limit of my abilities here.

Cauld

I usually read the built in excel help when I'm trying to figure out a function, not the online microsoft help, I find the offline help best for me.

The if function is pretty simple. It's basic format is =if([logical function],[value if true],[value if false])

[Logical Function] can be straight forward if you're trying to match text. For example A6="John" will be TRUE if the text in cell a6 = john.

[Value if True] and [Value if false] is also pretty straightforward. In the example above if cell A6 contains John then whatever you have in [Value if True] will be shown. Otherwise whatever you have in [Value if false] will be shown.

So for a more complete example =IF(a6="John","Check Closely","OK") will result in "Check Closely" if a6 contains "John" or will result in "OK" if a6 has anything else in it.

The more powerful if function will contain other functions, or other if functions in it so that you can test for multiple cases at once. For example:

=IF(a6="John","Check Closely",if(a6="Sally","Check Closely","OK"))

above will result in your cell reading "Check Closely" if A6 contains either "John" or Sally" otherwise it will show "OK".

If you're having problems with a long formula and don't where the error is, I often use the "Evaluate Formula" thing to step through the the formula in excel so I can at least narrow down where my problems are.

If you want to post an example to a google spreadsheet, the if statement has the same syntax I think. So someone could probably help provide some better examples

useless4

quick and dirty:
if you have four cells (a1, b1, c1, d1) and you want to know if a1 matches *any* of b,c or d?
here is what you do (very quick very dirty)

In E1 type the following =if(a1=b1,TRUE,if(a1=c1,TRUE,if(a1=d1,TRUE,FALSE)))

It will return TRUE if it matches any and FALSE if it matches none.

There are much better ways to do it but there is your quick and dirty

localh77

If it was me, I'd probably make some columns off to the right to find multiples. So for example:

In cell E1, have the formula: =IF(COUNTIF(A:A,A1)>1,"MATCH","")

Then copy and paste that all the way down column E. What you'll get is any row that has a value in column A that matches any other row will show up as MATCH in column E (and if not column E will just be blank). You can then do the same thing in col F for col B (=IF(COUNTIF(B:B,B1)>1,"MATCH","")), and col G for col C.

Then you can just quickly scroll down the sheet, looking at columns E - G, looking for matches. Depending on what you want to do with matches, you can obviously get a lot fancier. But that's a start.

GenlyAi

What localh77 said is great.

The following is slightly easier to understand, and has the advantage that you will have all the duplicate rows side by side to view.

A) sort the sheet by column A (if you're going to need to get it back in its original order, make an index column first that you can resort by)
in a new column (say E), make a new formula in E2: =exact(A1,A2), and fill down to the bottom of the sheet
C) search column E for any "TRUE", as these will indicate your duplicate rows

Underdog

Geez, how thoughtless of me! localh77, that was exactly what I needed and it worked like a charm. Turned a 800 line slog fest into a quick 15 min review. Perfect! And GenlyAi, that actually would have worked very well too so I think I'm going to keep that in mind for the future. Can't learn enough Excel stuff, that's for sure. Thanks to everyone who helped!