Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists of data
Ok, so I have 2 lists of data, one is a master list that a client has sent me
telling me how they want their expense reports filed (unfortunatly sent to me 6 months late..) and a list we have been maintaining over the course of the archiving for them which we created and enter manually everyday. And I need to find a formula that will highlight the information that is similar in both lists green and the ones that dont match up to highlight red. Here is an example of what the sheet looks like: |-----Their List-----| |--------Our List--------| A B C D E F 1 Expense# Box # Expense# Box # Box Compare 2 Bob00942 Box001 Ryan00759 Box001 Box001 3 Ryan00759 Box001 Louis00953 Box001 Box002 4 Clayton00643 Box001 Clayton00643 Box001 Box 001 5 Sherri00571 Box002 Bob00942 Box002 Box001 6 Louis00953 Box002 Roger00911 Box002 N/A 7 Brandon00652 Box002 Steven00864 Box002 N/A 8 Anthony00945 Box002 Anthony00945 Box002 Box002 On F, I used =VLOOKUP(D2,A:B,2,FALSE) in order to get the box number that the expense report numbers are supposed to be in according to their list, but my boss would like me to get it to where A and D would light up so we could see which ones are not matching up ( ex: Sherri00571 would turn red in A since it is not in D) Does anyone know a formula that can do this? I apologise if I wrote it out confusing, but any help would be much appriciated. Thanks, Brandon |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists of data
Their list first:
Select the first cell (A1). Go to Format, conditional format. Change first box to "Formula is". In second box, type =ISERROR(MATCH(A1,D:D,0)) Click format, go to pattern, choose what color you want if there is not a match (red?) Copy the cell, select the rest of column A, then right click, paste special - format only. Your list: similar, except formula is =ISERROR(MATCH(D1,A:A,0)) And it sounds like you want a different color, maybe green? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Brandon" wrote: Ok, so I have 2 lists of data, one is a master list that a client has sent me telling me how they want their expense reports filed (unfortunatly sent to me 6 months late..) and a list we have been maintaining over the course of the archiving for them which we created and enter manually everyday. And I need to find a formula that will highlight the information that is similar in both lists green and the ones that dont match up to highlight red. Here is an example of what the sheet looks like: |-----Their List-----| |--------Our List--------| A B C D E F 1 Expense# Box # Expense# Box # Box Compare 2 Bob00942 Box001 Ryan00759 Box001 Box001 3 Ryan00759 Box001 Louis00953 Box001 Box002 4 Clayton00643 Box001 Clayton00643 Box001 Box 001 5 Sherri00571 Box002 Bob00942 Box002 Box001 6 Louis00953 Box002 Roger00911 Box002 N/A 7 Brandon00652 Box002 Steven00864 Box002 N/A 8 Anthony00945 Box002 Anthony00945 Box002 Box002 On F, I used =VLOOKUP(D2,A:B,2,FALSE) in order to get the box number that the expense report numbers are supposed to be in according to their list, but my boss would like me to get it to where A and D would light up so we could see which ones are not matching up ( ex: Sherri00571 would turn red in A since it is not in D) Does anyone know a formula that can do this? I apologise if I wrote it out confusing, but any help would be much appriciated. Thanks, Brandon |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists of data
Awesome, Thanks! That did the trick!
"Luke M" wrote: Their list first: Select the first cell (A1). Go to Format, conditional format. Change first box to "Formula is". In second box, type =ISERROR(MATCH(A1,D:D,0)) Click format, go to pattern, choose what color you want if there is not a match (red?) Copy the cell, select the rest of column A, then right click, paste special - format only. Your list: similar, except formula is =ISERROR(MATCH(D1,A:A,0)) And it sounds like you want a different color, maybe green? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Brandon" wrote: Ok, so I have 2 lists of data, one is a master list that a client has sent me telling me how they want their expense reports filed (unfortunatly sent to me 6 months late..) and a list we have been maintaining over the course of the archiving for them which we created and enter manually everyday. And I need to find a formula that will highlight the information that is similar in both lists green and the ones that dont match up to highlight red. Here is an example of what the sheet looks like: |-----Their List-----| |--------Our List--------| A B C D E F 1 Expense# Box # Expense# Box # Box Compare 2 Bob00942 Box001 Ryan00759 Box001 Box001 3 Ryan00759 Box001 Louis00953 Box001 Box002 4 Clayton00643 Box001 Clayton00643 Box001 Box 001 5 Sherri00571 Box002 Bob00942 Box002 Box001 6 Louis00953 Box002 Roger00911 Box002 N/A 7 Brandon00652 Box002 Steven00864 Box002 N/A 8 Anthony00945 Box002 Anthony00945 Box002 Box002 On F, I used =VLOOKUP(D2,A:B,2,FALSE) in order to get the box number that the expense report numbers are supposed to be in according to their list, but my boss would like me to get it to where A and D would light up so we could see which ones are not matching up ( ex: Sherri00571 would turn red in A since it is not in D) Does anyone know a formula that can do this? I apologise if I wrote it out confusing, but any help would be much appriciated. Thanks, Brandon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing lists of data | Excel Worksheet Functions | |||
Comparing two lists of financial data | Excel Discussion (Misc queries) | |||
Comparing two lists and extracting data from one to another | Excel Worksheet Functions | |||
Comparing two lists and return specified data | Excel Worksheet Functions | |||
comparing lists of data to remove duplicate data | Excel Discussion (Misc queries) |