You are here:

Excel/Help with consolidating data from 2 worksheets



I have created an Excel file with 2 worksheets.  #1 has a column A of Product Numbers and Column B with the date that due diligence was completed, if any (many are blank).  The second worksheet has Column A Product Numbers and Column B with the date and assessments were completed, if any (many are blank).  Product Numbers may be unique to a worksheet or be present on both.

My assignment is to find the Products ID Numbers that are blank  in one or both worksheets under any condition.  Here are the possible scenarios:
1)  Product ID has no date on either sheets.
2)  Product ID has blank date on sheet 1 but does not appear at all on sheet 2.
3)  Product ID has blank date on sheet 2 but does not appear at all on sheet 1.

In short, I need a list of Product IDs that have not had either due diligence or assessment dates at all.

This seems simple to me, but I cannot get it to work in Excel.  I created a pivot table for each sheet, but have to do stare and compare to get my answer.  Is it VLookup (a feature I am not profient at), or what is the function(s) I should use to come up with a list of ID?

Thanks for any help.


Here is how I would handle it.

To keep it simple, I believe you can use a simple sort feature to identify the products that need the due dili or the assessment or both.

How about this

Create a third sheet and copy the data from sheet1 to the third sheet then copy the data from sheet 2 to the third sheet.  When doing his you might want to insert a column between the A and B columns of sheet 2 leaving the C column as the assmnt date column.  That way when you copy the data to the third sheet the assmnt dates will be in column c and the due dili dates will be in column B.

After creating the sheet3 and copying the data over, I would do a simple sort of the data using column A (the product number) as the first sort then column B (the due dili date) as the second sort, and column C (the assesment date)as the third sort.

Now you have a list of all of the products numbers and next to the product number in column B and C you have the due dili date and the assmnt date or if neither was done then B and C will be blank.  You will have duplicate numbers listed, but that should not be an issue if you are wanting to produce a working list.  It will be easy to see which products are in need of due dili, assesmnt or both.

We could get fancier with the formulas and eliminate the duplicates but its prolly not necessary and simpler is always better if it works.

Hope this helps

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2016 All rights reserved.