SOLVED

Excel Compare Data

Copper Contributor

 

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
best response confirmed by Arnold Lopez (Copper Contributor)
Solution

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

 

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 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.

 

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.

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.

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.

 

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.

 

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

 

Please find the solution in the attachment file.

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? 

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.

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.

1 best response

Accepted Solutions
best response confirmed by Arnold Lopez (Copper Contributor)
Solution

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

 

View solution in original post