turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 197K Members
- 7,408 Online
- 49.6K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Excel Compare Data

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2017 03:13 PM - edited 11-30-2017 04:54 PM

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.

Labels:

12 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2017 09:21 PM - edited 11-30-2017 09:22 PM

SolutionArnold,

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

- Tags:
- duplicate

Best Response confirmed by
Arnold Lopez (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2017 08:51 AM - edited 12-01-2017 08:52 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2017 10:09 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2017 01:07 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2017 08:15 PM - edited 12-01-2017 08:18 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2017 11:21 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2017 08:26 PM - edited 12-05-2017 08:32 PM

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:

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017 09:16 AM - edited 12-06-2017 09:47 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2017 07:28 AM

Please find the solution in the attachment file.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2017 03:16 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2017 08:29 PM

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

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

Highlighted
##
##### Re: Excel Compare Data

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-08-2017 09:06 AM

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
10-03-2017
103K
Views

0 Likes

60 Replies

Security Warning: Automatic update of links has been disabled.

Mark Adams
in
Excel
on
03-11-2018
3,387
Views

0 Likes

6 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft