SOLVED
Home

Excel Compare Data

Arnold Lopez
Occasional Contributor

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.Sample Data.PNG

12 Replies
Highlighted
Solution

Re: Excel Compare Data

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.

 

Extract Duplicate Values.GIF

 

Re: Excel Compare Data

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.

 

Re: Excel Compare Data

After you hit enter drag each formula down by using the fill handle.

Then sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.

 

Re: Excel Compare Data

Okay, I have to hard type the formula and not just copy your formula and paste it. Also, I dragged each formula down and I got the result which is cool. The only problem I am having now is when I'm sorting it, either in Column E or D, the dates are off?  I am comparing the dates from the original and somehow some dates are showing up on ID 2 where there shouldn't be any date at all.

For example:

 

ID 2 (column 2) one ID has no date next to it, but after doing the sorting in column E (dates), the ID with no date will have a date.  I think I will not sort it for now until I get more help from you.  So far, I got the result that I need.  It's the sorting now that I am having problem with, thanks.

Re: Excel Compare Data

I guess that the date that you got is something like this: 01/00/1900

 

If you have blank cells in the column C (DATE column), then replace the below formula in column E:

=IF(D2<>"",C2,"")

With this:

=IF(C2<>"",IF(D2<>"",C2,""),"")

 

After that, sort column D from Z to A.

Re: Excel Compare Data

This time it worked! Thank you so much.  

 

I have another question.  In case I need to add another column for let say Serial Number (sn) which that serial numbers belongs to column B, how will I include that new column info? I know that in column B and C we incorporated that result in D & E, now just in case I need to insert another column let's say insert it next to B (I know all the columns will adjust to the right, if I insert a column after the B column).  I need the B, C, D columns result to be together in another column just like what we did in B & C which the result went to D & E.  I assume this time, I'll have column D, E, F for the results. Not sure if I'm clear with my question, but my point is column B originally has more info such as serial number, site delivered date, etc. attached to it.  In case I have to insert another column, what formula should we use? 

 

Thank you in advance.

 

Re: Excel Compare Data

You don't need to any formula!

To insert a blank column between adjacent columns contain data, select the entire column, and click insert as shown in the below screenshots:

Step1.png

 

Step2.png

 

The data in column C, D and E will move to the right, and the formulas that were in columns D and E its results won't change!

 

After that, add the data that you want in the new blank column.

Re: Excel Compare Data

 

I think what I want is that the new inserted column will also appear on the last column G. The same concept that we did in column D & E before (these columns are now E & F because I inserted a column (c), but this time will have 3 results in ( E, F, G) G would be the serial number that came from column C.  I attached an example. So I think we need a formula in column G for the SN-ID2 in column C to appear in column G. These are the formula that we have so far:

 

Column E
=IF(ISNUMBER(MATCH(B2,$A$2:$A$2067,0)),B2,"")

Column F

=IF(D2<>"",IF(E2<>"",D2,""),"")

Column G?

 

Thanks.

Sample Data2.PNG

 

Re: Excel Compare Data

Please find the solution in the attachment file.

Re: Excel Compare Data

Great, thank you! I got want I needed.  I am having just one more issue, the sorting. I was trying to sort the column F (dates) from Oldest to Newest, but every time I hit enter the columns E, F, and G are disappearing? Am I doing the correct sorting? 

Re: Excel Compare Data

They didn't disappear, but moved down and dispersed.

SortDates.GIF

 

To move them back to the top, sort column A (ID1) from A to Z, or Column E (Duplicate ID) from Z to A.

Re: Excel Compare Data

I was able to sort them this time without the other columns disappearing.  I also used Notepad ++ to fix  the blank spaces then recopied it back to Excel for my reports.

 

Thank you so much for your help.

Related Conversations
Copy/paste no longer working in Excel
Jon Firooz in Excel on
51 Replies
EXCEL DO NOT SHOW GRAPH MAP CHART
Mark 777 in Excel on
47 Replies
align decimals in a column
Jonni Anderson in Excel on
15 Replies
Missing Excel 2013 worksheet tabs
Mike Hill in Excel on
7 Replies
Merging Datasets
Keith Lowery in Excel on
6 Replies