Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
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. Brandy wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering information
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?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
Which column would I put this formula in?
"Dave Peterson" wrote: 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. Brandy wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering information
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 "Brandy" wrote: 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?? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
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 wrote: Which column would I put this formula in? "Dave Peterson" wrote: 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. Brandy wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
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" wrote: 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 wrote: Which column would I put this formula in? "Dave Peterson" wrote: 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. Brandy wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Information
=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. Brandy wrote: 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" wrote: 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 wrote: Which column would I put this formula in? "Dave Peterson" wrote: 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. Brandy wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering out rows with duplicate information | Excel Discussion (Misc queries) | |||
Filtering | Excel Discussion (Misc queries) | |||
Functions for manipulating and filtering address information | Excel Discussion (Misc queries) | |||
filtering | Excel Worksheet Functions | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |