purchased vs invoiced days duplicates values

**costi_linho12** · 07-14-2014, 12:51 PM

Dear All,

I need your help 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).

From Scan Date I need to return the Invoice Date - the invoice date must be ” =>” then Scan date, not “<”.

I sent this message on several forums, but without success to be solved ....

Can anybody help me with this issue ?

Many thanks,
Costin

**hoyasaxa215** · 07-14-2014, 01:05 PM

What is wrong with the formula in place? What do you want to happen if the scan date is after the invoice date? And vice versa?

**costi_linho12** · 07-14-2014, 01:15 PM

Hello hoyasaxa215,

Thanks for reply. My formula is wrong....

What I want to return is the number of days & Invoice date until final invoice (the invoice date must be => then Scan date, not <).

Regards,
Costin

**hoyasaxa215** · 07-14-2014, 01:21 PM

Can you update the file to provide an example of what the correct information should be? Highlight the cells where you'd like formulas to import the data you're looking for and then upload. It's still not entirely clear. Thanks.

**costi_linho12** · 07-14-2014, 01:37 PM

Attached the update made manually.
Somehow you must take from Source sheet Invoice Date , this information must be placed in D2 (must be always positive), and then make the difference between D2 and E2 (final number of days on stock till final invoice).

**newdoverman** · 07-14-2014, 01:39 PM

The dates in Source!E2:E24 are TEXT. Change these to real dates and then your formula will work. There is a negative value in final!F6 and F7 because the scan date is newer than the invoice date.

**costi_linho12** · 07-14-2014, 01:43 PM

This is my issue, I don't know what formula to use (because of the duplicates)...

**newdoverman** · 07-14-2014, 01:55 PM

This might help. The dates on the Source worksheet are TEXT and will not calculate. I converted them to real dates by selecting them and then used DATA, TEXT TO COLUMNS,NEXT, NEXT, chose MDY, FINISH.

I changed the date format to show the day of the week with the date then added consecutive INVOICE DATES so that you could see what was going on.

**newdoverman** · 07-14-2014, 02:08 PM

Why not use the ENTRY NO from the Source worksheet by inserting a column for ENTRY NO on the Final Worksheet. You can hide it if you don't want it visible.

The ENTRY NO appears to not be duplicated so it might be a good id to use.

**costi_linho12** · 07-14-2014, 03:17 PM

In your file Scan date is the same and is not correct, in my test file Invoice date return the same Mon 13/01/2014....I don't know where is my mistake.

**newdoverman** · 07-14-2014, 04:04 PM

I'm totally confused.

**costi_linho12** · 07-14-2014, 04:19 PM

The ENTRY NO appears to not be duplicated, yes, but I cannot put the ENTRY NO to final sheet. I don't know a formula who can return ENTRY NO (see attached file)

Thanks for your help,
Costin

**costi_linho12** · 07-15-2014, 04:34 AM

Hello,

In this moment, my file has 20.000 lines, and I make the update with more information every week, in attached file "purchased vs invoiced days duplicates values updated 2.xlsx‎" you can see that in source sheet for exemple p/n 123456 moved (by a sale) from stock 455 times and also has 455 unique ENTRY NO - this unique ENTRY NO will be different every time I make the file update (I don't know a way to put the unique ENTRY NO from source sheet in final sheet to every p/n).

When I use INDEX & MATCH the result sometimes return Invoice date < than Scan date and is not ok the Invoice date must be => than Scan date.

I'm totally confused...maybe is another way to solve this issue with duplicate values ?

My question remain the same - how to return the Invoice date with a positive date when I have duplicate values in source sheet and also, in final sheet ?

Regards,
Costin

**costi_linho12** · 07-16-2014, 02:42 PM

Hello,

Sorry for pushing... can you find a way to solve this issue ?

Regards,
Costin

**newdoverman** · 07-16-2014, 03:04 PM

You need to have at least one shared element that is unique between the two tables. Right now, there is nothing unique (even combinations of columns that create something unique) between the two tables.

**costi_linho12** · 07-16-2014, 03:25 PM

Hello newdoverman,

Thanks for reply and info, from what you told me is not possible to do this when I have duplicates, but if I remove all the duplicates can you find a solution ?

Regards,
Costin

**newdoverman** · 07-16-2014, 04:26 PM

Without some element that is common to both, there can be no guarantee that the records are properly matched even with duplicates removed. The only hope is that both records have the same number of records and they are in exactly the same order otherwise, I don't see how this can be done.

**costi_linho12** · 07-17-2014, 12:31 PM

Thanks for your help and your time spent with this issue !

Regards,
Costin

**newdoverman** · 07-17-2014, 02:30 PM

You're welcome. Thank you for the feedback.

**costi_linho12** · 07-22-2014, 12:32 PM

fyi

Hello,

A colleague of mine solved this issue with IF function. See in attached file.

Regards,
Costin

purchased vs invoiced days duplicates values

LinkBack

Thread Tools

Rate This Thread

Display

purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Re: purchased vs invoiced days duplicates values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to Validate Invoiced Rates Against a Rate Card

[SOLVED] Macro to find duplicates, concatenate Unique Values, then delete old duplicates

Need to change expiry duration of my purchased positions from 81 days to 88 days..Plz help

Excel 2007 : How to keep ONE instance of duplicates while removing duplicates AND unique values

[SOLVED]Invoiced days per month

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions