turn on suggestions

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

Showing results for

- 250K Members
- 6,430 Online
- 57.9K 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?

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

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

- 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

Microsoft Excel Product Team AMA confirmed for November 6th at 2:00 PM Eastern Time (GMT-5:00)

Eric Starker
in
Excel
on
10-26-2018
2,177
Views

0 Likes

13 Replies

Copy/paste no longer working in Excel

Jon Firooz
in
Excel
on
10-03-2017
146K
Views

0 Likes

65 Replies

Security Warning: Automatic update of links has been disabled.

Mark Adams
in
Excel
on
03-11-2018
7,604
Views

0 Likes

6 Replies

Share

Popular

Learning Resources

Programs

Values

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