New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

purchased vs invoiced days duplicates values

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

purchased vs invoiced days duplicates values

Postby costi_linho12 » Fri Jul 11, 2014 5:40 pm

Dear All,

I need you help me in order to find a correct formula who can return date between “Scan Date” and “Invoice date” from the attached file ?

Issue – I want to know how many days has past from Scan Date till invoice date (you can see that I have a lot of duplicates p/n in the file).

Can anybody help me with this issue ?

Many thanks,
Costin Calin
  • 0

You do not have the required permissions to view the files attached to this post.
costi_linho12
Rookie
 
Posts: 3
Joined: Jul 11, 2014
Reputation: 0
Excel Version: Microsoft Office 2007

Re: purchased vs invoiced days duplicates values

Postby NoSparks » Sat Jul 12, 2014 12:35 am

Costin,
There is nothing that ties your final sheet data to your source sheet data unless the final sheet Document No. and the source sheet Entry No. are the same thing.

If this is the case you can get the invoice date from the source using an Index Match formula, and the days difference is just invoice date - scan date.

If that's not the case you will need to explain things differently.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: purchased vs invoiced days duplicates values

Postby costi_linho12 » Sat Jul 12, 2014 5:22 am

Hello NoSparks,

Thanks for your reply. Document No and Entry No, are not the same.
I already tried INDEX MATCH, but sometimes, this return negative number (or I’m using the formula wrong, you can see in attached file how the formula look in my case).
From Scan Date I need to return the Invoice Date - the invoice date must be ” =>” then Scan date, not “<”.

Regards,
Costin
  • 0

You do not have the required permissions to view the files attached to this post.
costi_linho12
Rookie
 
Posts: 3
Joined: Jul 11, 2014
Reputation: 0
Excel Version: Microsoft Office 2007

Re: purchased vs invoiced days duplicates values

Postby NoSparks » Sat Jul 12, 2014 9:39 am

The match formula returns the first row of the array on which it finds a match.
Your index match formula in cell E2 of final is right, except what you are looking for is the same for every row in the array, not anything that would distinguish one row from another.

The reason the Invoice dates are different each time is due to the array being changed. It's the first item in the "new" array that is being returned every time.

There is nothing on your final sheet that would associate with any specific row of the source the way you have it now. That's why I originally thought the Document No. might actually be the same as the Entry No.

I would also suggest you have a read of this. http://www.excelguru.ca/content.php?184 as it pertains to all forums.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: purchased vs invoiced days duplicates values

Postby NoSparks » Sat Jul 12, 2014 5:27 pm

I directed you to the link in my previous post as a polite way of saying
DON'T CROSS POST UNLESS YOU ARE GOING TO DO IT IN A RESPECTABLE MANNER
not as an encouragement to post the same question in another forum, which you had already done.
That is a page numerous Excel forums refer members to for an understanding of why cross posting is considered a No-No.

Now, back to your question, obviously you don't agree with my view of the problem, so maybe I'm misunderstanding something. Believe it or not I am trying to help you.

On your final sheet, for row 7 where your Document No. is TEST6, from your source sheet, what Invoice Date should be going into cell E7? and how do you know that?

With what you have now in E7 the value of -5 in cell F7 is correct because the invoice date is earlier than the scan date.
If it doesn't matter which was first, the scan date or the invoice date, then =ABS(NETWORKDAYS(D7,E7)) will always give a positive number.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: purchased vs invoiced days duplicates values

Postby costi_linho12 » Mon Jul 14, 2014 3:04 am

Hello NoSparks,

Thanks for your help and support, but INDEX and MATECH cannot help me in this case....

Regards,
Costin
  • 0

costi_linho12
Rookie
 
Posts: 3
Joined: Jul 11, 2014
Reputation: 0
Excel Version: Microsoft Office 2007

Re: purchased vs invoiced days duplicates values

Postby NoSparks » Mon Jul 14, 2014 10:48 am

Oh well, I tried.

My suspicion is that only a portion of a larger amount of data was posted.
Hoped something in that "other" data could be used to identify your rows and be used to accomplish your goal.

At any rate, if you're not able to answer the questions of my last post ........ I'm out of here

Good luck to ya
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 68 guests