Hi! I am trying to take two values from one sheet- column A is an invoice number and column B is an amount and comparing that to another report with the same information in both columns. It won't let me merge the two columns. How can I see if that invoice number with the amount of the invoice (in column B) matches the other sheet that is set up the same way? The other sheet has multiple charges from the same invoice number with different amounts so I need to narrow it down so I can remove the duplicate columns. Hope this makes sense!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Matching two reports
- Thread starter wjohnson
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 73,972
- Office Version
- 365
- Platform
- Windows
Welcome to the Board!
You could use a COUNTIFS function, to count how many records on the second sheet match, when checking both column A and B, i.e.
(when this formula is placed on Sheet1, it checks the number of rows on Sheet2 what match what is in the combination of A1 and B1 on Sheet1)
If it returns a value greater than 0, then there are matches, i.e.
If you want to see if there are duplicates, check to see if there are more than one match:
You could use a COUNTIFS function, to count how many records on the second sheet match, when checking both column A and B, i.e.
Excel Formula:
=COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,B1)If it returns a value greater than 0, then there are matches, i.e.
Excel Formula:
=IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,B1)>0,"Matches found","No matches found")If you want to see if there are duplicates, check to see if there are more than one match:
Excel Formula:
=IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,B1)>1,"Duplicates found","No duplicates found")
Upvote
0
Similar threads
