You are here:

Excel/Match 2 columns return 3rd with a difference

Advertisement


Question
I have 1 worksheet (7-24) with the following columns:
Sold To Party (A), Sales Document (B) , Item # (C),  Material (D),Created on Date (E), Document Date (F), Delivery Date (G)

2nd Worksheet (7-25) is identical but for the following 7/25/15 info
3rd Worksheet (7-26) is identical but for that days information and so on

I am trying to return values in Worksheet 1 (7-24) columns Q the difference in days of the date for columns E, F & G.  

I was able to use the following formula to create the date lookup and calculation:
=IF(ISBLANK('07-25'!H3),"",('07-24'!H3-'07-25'!H3)*-1)
But I do not know how to ensure the Sales document (B) and Item Number (C) match for the dates I am returning and comparing against.

Answer
Angelique

I may not be understanding your question, but-----
You can create a unique serial number, by combining the data in the sales doc column and the item number column, by using the & formula, and then make sure in your matching formula that the result matches the same combined number on 7-25 and 7-26.

If 7-24 B2 contains 12345 and 7-24 C2 contains zztop, then the formula =B2&C2 will result in 12345zztop.  you use this same formula in sheet 7-25 and sheet 7-26, thus creating unique numbers for each item in all sheets that combine the sales doc number and the item number.  

When you create a formula in cell Q2 you can compare that serial number to make sure your working with the same Sales doc and the same item number.

Also, a suggestion--you might consider using a VLOOKUP formula to look up the serial number values and where they match, subtract the dates to calculate the days. I think it might be easier to work with.

Hope this helps.

If you have questions e-mail me and include in the subject "Allexperts Question"

Richard
Florida USA

Rirstress@aol.com

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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Roberts

Expertise

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.

Experience

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

Education/Credentials
BA, Certified Public Accountant

©2016 About.com. All rights reserved.