Filtering Information

[Brandy]

Ok. I hope I explain this correctly.

I have an excel worksheet. I have columns for "Vendor Names - Company A",
"Vendor # - Company A", "Vendor Names - Company B", "Vendor # - Company B".

I want to compare either the names or the numbers (doesn't matter which) and
find all the vendors that Company B has that Company A doesn't have.

Does that make sense? Is there an EASY way to do this?

Thank you.

 

[Dave Peterson]

You can use this kind of formula to determine if a value appears in another
column:

=isnumber(match(a1,c:c,0))

and drag down as far as you need.

 

[Prasada]

There is a function "EXACT" - You can use this to compare two text columns.
In case you have trailing spaces to the vendor names, use trim.
It looks like this - =EXACT(TRIM(A1),TRIM(B1)), which returns true or false

Thanks
Prasada

 

[Brandy]

I'm sorry, but I don't know what to do with the "EXACT" formula.
Is there a way to compare the name or numbers and then delete all the
matching ones and leave the ones that don't match? So I can see how many
vendors company B has that company A doesn't??

 

[Brandy]

Which column would I put this formula in?

You can use this kind of formula to determine if a value appears in another
column:

=isnumber(match(a1,c:c,0))

and drag down as far as you need.

 

[Prasada]

Brandy, lets assume the excel sheet has 4 columns A,B,C,D
which has vendor name A, vendor # A, vendor name B, vendor # B
Now put the cursor in column E (row # 1) and click the function button - it
looks like fx and a big text box next to it. In the text box type
=EXACT(TRIM(A1),TRIM(C1))
Press enter and then copy the first E1 and paste in all the E column rest
other rows. Now the other rows will display either true or false.. Now from
the DATA Menu do a filter for all the "FALSE" ones

I hope this helps

 

[Dave Peterson]

You could put it in any unused column--or even insert a new column just to hold
it.

But you will have to adjust the formula to match your data. I used A1 (all of
column A, in fact) as the cell/range to check to see if it appears in column C.

 

[Brandy]

I think I got this to work exactly how I wanted it to! Thank you so much.

In the formula =isnumber(match(a1,c:c,0)) ..... what does the c,0 do ????
I understand taking a1 and looking for it in column C .... but guess i don't
understand the c,0. Just want to know what it does.

Thank you.

 

[Dave Peterson]

=match() has 3 arguments.

The first is the value that you're trying to match--for me it was A1.

The second is the range that should be looked at to find the match. I could
have used C1:C100 (for the first 100 rows in column C). But since I wanted to
use the whole column, I specified C:C (that's the way excel refers to a whole
column).

The third parm tells excel what kind of match I want. I want to match exact
values, so I used 0.

There's more info in excel's Help.

I think I got this to work exactly how I wanted it to! Thank you so much.

In the formula =isnumber(match(a1,c:c,0)) ..... what does the c,0 do ????
I understand taking a1 and looking for it in column C .... but guess i don't
understand the c,0. Just want to know what it does.

Thank you.