• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

comparing two columns in two lists

B

birdiegirl

New Member
Hi, i have been looking around for all the answers on comparing two lists. I get how to compare two lists with conditional formating and such. but, my list is a little different and i guess I am just not getting how the conditional formation will help in my situation. let me try to explain.

i am comparing two lists and they look like this

list 1

column A Column B

1809 $8.80

1946 $8.12

2000 $7.95

2000 $2.38


List 2 (separate Excel spreadsheet)

Column A Column B

1809 $8.80

1824 $8.89

1946 $7.90

2000 $2.38


what I want to do is compare the two lists where both columns match in each list.

in the above sample, 1809 and 2000 in both lists would be highlighted whatever color I pick.

1946 would not hightlight since both columns are not a match.


i hope I have made some sense here. I have tried the highlight with conditional formationg and the 1946 highlights when it should not.


thank you very much for your time


Birdiegirl

aka Lisa
 
Hi, birdiegirl!


Have you performed searches within this site before posting?, because maybe your question had been answered yet, and in this case I'm sure about it.


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

http://chandoo.org/wp/2011/10/27/compare-2-lists-visually-and-highlight-matches/

http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/

http://chandoo.org/wp/2012/02/06/comparing-2-lists-with-a-twist/

... and a lot more!


Regards!
 
yes sir, i did search in this site. i found the three matches you listed in your reply. I tried to apply them to my situation but the correct information was not returned. i can compare column a to column b with no problem. where I have the problem is when I try to compare column a & b to column c & d where a & b have to match column c & d equally


i am pretty sure it can be done but with my limited knowledge I am not finding it. i try to search using "compare 2 columns to 2 columns" or words to that effect but I am not finding what I am looking for.


can you suggest what I should type in search to find what I am looking for.


i'm trying to learn excel and chandoo.com :) and have read all of the green sticky'ed links up top.
 
Hi, birdiegirl!

Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the green sticky posts at this forums main page for uploading guidelines... again :)

Regards!
 
Hi, birdiegirl!


In the meanwhile give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/comparing%20two%20columns%20in%20two%20lists%20%28for%20birdiegirl%20at%20chandoo.org%29.xlsx


It has your 1st list in 1st sheet A1:B5, your 2nd list in 2nd sheet at same place.

A2:B5 cells (list without titles) has this conditional formatting formula to highlight matches:

=COINCIDIR($A2&$B2;Hoja2!$A$2:$A$5&Hoja2!$B$2:$B$5;0) -----> in english: =MATCH($A2&$B2,Hoja2!$A$2:$A$5&Hoja2!$B$2:$B$5,0)

Same changing the sheet name for 2nd list too.


Just advise if any issue.


Regards!
 
okay,

went to rapidshare, hope i did it correctly


http://rapidshare.com/files/2284477268/comparing.xlsx


sheet one is what I get from my financial services group

sheet two is what I keep through out the month


it is a list of shipping charges and the # of the person it is associated with.


when i choose column E and F (Fin Serv tab) and column B and C (my info tab) and then choose conditional formating-highlight cells-duplicate values the wrong values highlight.


i need E to match B and F to match C


hope this helps
 
SirJB7, your spreadsheet does look like it is doing what I want. I will try to apply to my spreadsheet and will reply with Success or not soon.


Birdiegirl
 
Hi, birdiegirl!

Glad to help you. Try that by yourself and let us know if you succeeded.

Thanks for your kind words too and welcome back whenever needed or wanted.

Regards!
 
all right, I am trying to change your formula to my information.


It has your 1st list in 1st sheet A1:B5, your 2nd list in 2nd sheet at same place.

A2:B5 cells (list without titles) has this conditional formatting formula to highlight matches:

=COINCIDIR($A2&$B2;Hoja2!$A$2:$A$5&Hoja2!$B$2:$B$5;0) -----> in english: =MATCH($A2&$B2,Hoja2!$A$2:$A$5&Hoja2!$B$2:$B$5,0)

Same changing the sheet name for 2nd list too.


this is what i come up with


=MATCH($E4&$F4,my info!$B$2:$B$500&my info!$C$2:$C$500,0)


i am on Fin Serv tab and i choose conditional formating-create rule-use a formula to determine which cells to format


I my formula into the box for the formula.

I click format and choose a color

click OK

and I get an error message that says...

you may not use reference operators(such as unions, intersections, and ranges) or array constants for conditional formatting criteria.


i am sure I have something wrong but I cannot see it.


=MATCH($A2&$B2,Hoja2!$A$2:$A$5&Hoja2!$B$2:$B$5,0)

i changed ($A2 and $B2 to ($E4 and $F4 (to match my column numbers)

I changed Hoja2! to my info! (to match my second tab name)

i changed $A$2:$A$5 to $B$2:$B$500 (to match my column)

i changed &Hoja2! to &my info! (to match my second tab name)

I changed $B$2:$B$5 to $C$2:$C$500 (to match my column)

then put ,0)


i am not sure why I am getting an error message.

i am using version 2010, not sure if that will make any difference or not.


can you help ?
 
Hi, birdiegirl!


The formula you get for your data is:

=MATCH($E4&$F4,my info!$B$2:$B$500&my info!$C$2:$C$500,0)

and it should be:

=MATCH($E4&$F4,'my info'!$B$2:$B$500&'my info'!$C$2:$C$500,0)


That's to say, it's just perfect, you understood each part and how to adapt it, but... and as my old friend b(ut)ob(ut)c uses to say, there's always a but! (I actually think that he repeats the last letter, but I'm not sure)...


...the but is that your second sheet name (my info) has a blank embedded, so Excel requires and needs to have it between apostrophes: 'my info'. And your first too, so make sure to set the reverse CF condition formula using 'Fin Serv'.


Et voilà, c'est tout!


Regards!


PS: A little tip. If you have a lot of matching values your worksheets will become very colored, so if in this case you want to highlight the non matching cells change formulas from:

=MATCH(...)

to:

=ISERROR(MATCH(...))
 
Sir, Thank you so much for the tips here. I really appreciate it. somehow I knew that it would be that darn space and I guess I could also fix it by nameing my sheet my_info instead.


again, thank you very much


Birdiegirl
 
You must log in or register to reply here.
Back
Top