Forum Discussion
Excel Compare Data
Hello,
I need help in comparing column B to column A and pulling that duplicate information to column D. My dilemma is I have three columns A, B, C. B and C are connected, column B has the ID information and Column C has the Date. When comparing column B to A, I need the results along with the date to go to column D (ID information) and column E (Date). Blue fonts are my anticipated results. I hope I am explaining myself clearly. I have about 2000 records that I need to compare. I was told that I can do this in Excel 2016 using VLOOKUP or ACCESS database, but I am not familiar on how to do this. Excel info would be great. Any help is greatly appreciated, thanks.
Arnold,
Step 1
Put this formula in cell D2 and fill it down:
=IF(ISNUMBER(MATCH(B2,$A$2:$A$200,0)),B2,"")
Don't forget to change this range $A$2:$A$200 to your existing range.
Step 2
Put this formula in cell E2 and fill it down:
=IF(D2<>"",C2,"")
Step 3
Sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.
12 Replies
Arnold,
Step 1
Put this formula in cell D2 and fill it down:
=IF(ISNUMBER(MATCH(B2,$A$2:$A$200,0)),B2,"")
Don't forget to change this range $A$2:$A$200 to your existing range.
Step 2
Put this formula in cell E2 and fill it down:
=IF(D2<>"",C2,"")
Step 3
Sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.
Hello Haytham,
I applied your formula on a new spread sheet but I am getting a blank result. How do I run the formula? This is what I have so far:
=IF(ISNUMBER(MATCH(B2,$A$2:$A$1538,0)),B2,"") In Column D. I just hit Enter and nothing came out.
=IF(D2<>"",C2,"") In Column E. I also hit enter and nothing came out.
After you hit enter drag each formula down by using the https://support.office.com/en-us/article/Fill-a-formula-down-into-adjacent-cells-041edfe2-05bc-40e6-b933-ef48c3f308c6
Then sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.
