0

I have two files. File A has supply type 1 purchase orders, File B has supply type 2 purchase orders.

File A has three columns Name order # date

File B has three columns, too, the same as file A.

I need to add in the File B order # as long as its within a week of the date of what's on file A.

Attached is a visualization to better model the problem.

I need a mix between a =index/match + IF + Date statement to compare the two files and only bring in the order number if the dates between those rows are similar, and to leave out the duplicate orders that are too old.

example

nick lanta
  • 602
  • 3
  • 10
  • Is the order number only numeric (i.e. no letters)? Also, is there only *one* order within a week, or can there be multiple? – BigBen Jun 19 '19 at 19:57
  • it will be just #'s for one file and text strings for another. – nick lanta Jun 19 '19 at 19:59
  • Which one is File B? Text or #s? – BigBen Jun 19 '19 at 20:00
  • there will never be multiple within one week, so that could be the limiting part of the if statement? – nick lanta Jun 19 '19 at 20:00
  • @BigBen File A = columns A:D, File B = Columns F:H – nick lanta Jun 19 '19 at 20:01
  • I got that, I want to know if the order #'s in File B are numbers, or text. – BigBen Jun 19 '19 at 20:02
  • file B will be #'s in this case, too. Other instances with future reports, they may be a mix of #'s and text, but we'll focus on just #s – nick lanta Jun 19 '19 at 20:02
  • If it's just numbers, you can probably use SUMIF(S). Numbers and text will require a different solution. – BigBen Jun 19 '19 at 20:03
  • @BigBen I guess I'm not really familiar with sumifs by date range. The order number is just the value I'm trying to bring over to file A. The date range is the IF statement part of the formula and the unique Identifier will be a mixture of text & numbers | example: `johnsmith5544` – nick lanta Jun 19 '19 at 20:09
  • 1
    see: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Jun 19 '19 at 20:13

2 Answers2

1

If the order numbers are just numbers (no text), then you can use SUMIFS:

=SUMIFS(G:G,F:F,A3,H:H,">="&C3-7,H:H,"<="&C3+7)

enter image description here

If you've got text or a mix of numbers and text, then you can use AGGREGATE.

=INDEX(G:G,AGGREGATE(15,6,ROW($A$1:$A$4)/(($F$1:$F$4=A2)*($H$1:$H$4<=C2+7)*($H$1:$H$4>=C2-7)),1))

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • if the order numbers are strings of text, will this work the same way? If they aren't a mix and they're either numbers or letters (soley), this will operate the same way? – nick lanta Jun 19 '19 at 20:23
  • No. If there is any text present, sumifs won't work. See the answer that Scott pointed out in the comments above. – BigBen Jun 19 '19 at 20:24
  • I understand that an index/match would work easily, but it’s the date parameters that are confusing me. How would I append that to an index match formula? Scott’s examples seem to not have that kind of scenario. Would I just wrap around an if statement? – nick lanta Jun 19 '19 at 20:35
  • something like: `=if(H:H,">="&c3,H:H,"<="&c3,index(G:G,match(a3,F:F,0)))` ? – nick lanta Jun 19 '19 at 20:39
  • 1
    Thank you!! Solution verified – nick lanta Jun 19 '19 at 20:54
  • Great, glad to help. Love `AGGREGATE`. Most credit goes to Scott Craner for posting such a thorough [answer](https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another) elsewhere. – BigBen Jun 19 '19 at 20:54
  • Hey, so it was working on dummy data, but isn't working on my actual dataset. any idea what could be going on? did I type in something incorrectly? `=INDEX(T:T,AGGREGATE(15,6,ROW($A$1:$A$300)/(($R$1:$R$300=A290)*($U$1:$U$300<=I290+5)*($U$1:$U$300<=I290-5)),1))` – nick lanta Jun 20 '19 at 15:42
  • getting a #NUM! error in the cell i'm trying to pull the values in – nick lanta Jun 20 '19 at 15:45
  • Can you evaluate the formula, stepping through and figure out which portion isn't working? – BigBen Jun 20 '19 at 15:49
  • Why do you have two instances of `<=`? That doesn't seem right. I think the second should be `>=`. – BigBen Jun 20 '19 at 15:57
  • I changed that around and its resulting in the same error – nick lanta Jun 20 '19 at 16:28
  • I think it may have to do with my date formatting, one has `/` while the other has a `-` delimter – nick lanta Jun 20 '19 at 16:39
  • It's possible that you have text-that-looks-like-a-date. – BigBen Jun 20 '19 at 16:39
0
=INDEX(G:G,MATCH(1,(ABS(C3-H:H)<5)*(A3=F:F)*(ABS(C3-H:H)<5),0),1)

n/a

The following index / match formula returns an order number from file b if the name is the same and if the order b date is within 4 days of the order a date.

You can update the formula where it is highlighted in the screenshot and change the number of acceptable days for an order to be considered similar. Please keep in mind that if multiple orders meet your criteria (e.g. John made many orders one day after the other) your INDEX formula will only return one order # and could potentially lead to matching the incorrect invoices.

dnacarlos
  • 81
  • 4