Need help with multiple criteria and multiple results

SARABECK

Board Regular
Joined
Jan 5, 2012
Messages
132
Hello,

I have been working on this formula for the past few days and it is just not working at all, please help?

In sheet 1 - in column A, I input dates from Sheet2 and Sheet3 and insert the desire formula in C2-C9 it should pull in the invoice# and the quantities in D2-C9 from sheet2 and sheet3.

Sheet1

ABCDHelper Column for Sheet 3
1DateProductInvoiceQTY
204/26/1801234
304/27/1801234
404/30/1801234YYY123200
504/30/1801234AC1234100
604/30/1801234WC56781000
705/01/1801234AE22221003rd Party Purchase
805/03/1801234AG333330Shipped
905/04/1801234BU555550Received
1005/04/1801234WE66661000Received

<tbody>
</tbody>

Sheet 2 (Report) Only the last 3 business days of the monh

Criteria: Product and Date

AGNPUY
MonthProductProduct DescriptionInvoice#QTYDate
104/30/1801234BlahTU145212004/01/18
204/30/1801234BlahAB111115404/15/18
304/30/1801234Blah222225004/26/18
404/30/1801234Blah22222-5004/26/18
504/30/1801234BlahYYY12320004/30/18
604/30/1801234BlahAB123410004/30/18
704/30/1801234BlahWC5678100004/30/18

<tbody>
</tbody>

Sheet 3 (Report 2) All info related to the May

Criteria: Product and Transaction Type
FKLNS
ProductTransaction TypeDateInvoice#QTY
1012343rd Party Purchase05/01/18AE2222100
201234Shipped05/03/18CB444420
301234Shipped05/03/18CB4444-20
401234Shipped05/03/18AG333330
501234Received05/04/18BU555550
601234Received05/04/18WE66661000

<tbody>
</tbody>


Thank you.
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi SaraBeck,

The challenge is here that dates are repeated multiple times in Sheet2 and Sheet3. To address this, i have developed some formulas. However, it will pick invoices from the top. That is if you have 4/30/18 in Sheet2 multiple times and repeat the date in Sheet 1 only three times, it will pick up first three invoices as listed below.
TU1452
AB1111
22222

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
.
Having said that, enter the following array formula in C2 in Sheet1 by pressing Control + Shift + Enter:


=IFERROR(IF(ISERROR(INDEX(Sheet2!$D$1:$D$100,SMALL(IF(A2=Sheet2!$A$1:$A$100,ROW($1:$100),""),COUNTIFS($A$2:A2,A2)))),INDEX(Sheet3!$D$1:$D$100,SMALL(IF(A2=Sheet3!$C$1:$C$100,ROW($1:$99),""),COUNTIFS($A$2:A2,A2))),INDEX(Sheet2!$D$1:$D$100,SMALL(IF(A2=Sheet2!$A$1:$A$100,ROW($1:$100),""),COUNTIFS($A$2:A2,A2)))),"")

Enter the following formula in D2

=IFERROR(IF(ISNA(INDEX(Sheet2!E:E,MATCH(Sheet1!C2,Sheet2!D:D,0))),INDEX(Sheet3!E:E,MATCH(Sheet1!C2,Sheet3!D:D,0)),INDEX(Sheet2!E:E,MATCH(Sheet1!C2,Sheet2!D:D,0))),"")

Enter the following formula in E2

=IFERROR(INDEX(Sheet3!B:B,MATCH(Sheet1!C2,Sheet3!D:D,0)),"")

Kind regards

Saba
 
Upvote 0
Hi Saba,

Thank you so much Saba for your help.

From Sheet 2
The only info that is needed is the last 3 business dates.

Looking at April month it would be 4/26/18, 4/27/18, and 4/30/18.

304/30/1801234Blah222225004/26/18
404/30/1801234Blah22222-5004/26/18
504/30/1801234BlahYYY12320004/30/18
604/30/1801234BlahAB123410004/30/18
704/30/1801234BlahWC5678100004/30/18

<tbody>
</tbody>

<tbody>
</tbody>

The date is listed in column Y
4/26/18 - This is zeroed out (It was invoiced and looks like then cancelled)
4/30/18 - 3 invoices were issued

From Sheet 3

5/3/18 - 3 invoices were issued and invoice# CB4444 shouldn't have been picked up instead should have picked up invoice#AG3333.

201234Shipped05/03/18CB4444 20
301234Shipped05/03/18CB4444-20
401234Shipped05/03/18AG333330

<tbody>
</tbody>


Your help is very appreciated.

Thank you again.
 
Last edited:
Upvote 0
In the formula, the product has not be incorporated and it should be since i have over 50-80 products, for which, the last 3 business days and the month of May info will need to be pull from the 2 reports.


Thank you
 
Upvote 0
Hi SaraBeck,

Based on your requirements, I have included a helper column in Sheet3 and modified my formulas.

Helper Column F of Sheet3: I used a SUMIFs formula to total net invoice amount. If total quantity of an invoice amount is zero, it will be NOT be picked in Sheet1. Enter the following formula in cell F2 in Sheet3 and copy it down
=SUMIFS(E:E,D:D,D2)

Enter the following array formula in C2 in Sheet1 by pressing Shift + Control + Enter and copy it down
=IFERROR(IF(ISERROR(INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),INDEX(Sheet3!$D$1:$D$1000,SMALL(IF((A2=Sheet3!$C$1:$C$1000)*(B2=Sheet3!$A$1:$A$1000)* (Sheet3!$F$1:$F$1000>0),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2))),INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),"")

Please note that the above formula includes your product as a criteria and it is based on the following assumptions:

  • You enter dates in Column A of Sheet 1
  • If it the first argument which looks for answer in Sheet2 produces an error, it looks for answer in Sheet3. If both arguments produce error, it puts nothing in cells

Formulas from Column D to E in Sheet remains the same as given in my reply post.

Kind regards

Saba
 
Upvote 0
Hi SaraBeck,

I tested my formula in your spreadsheet. it worked.

=IFERROR(IF(ISERROR(INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),INDEX(Sheet3!$D$1:$D$1000,SMALL(IF((A2=Sheet3!$C$1:$C$1000)*(B2=Sheet3!$A$1:$A$1000)*(Sheet3!$F$1:$F$1000>0),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2))),INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),"")

Copy the formula as it is in C2 and enter as array formula by pressing Shift + Control + Enter.

The formula in your spreadsheet in C2 is not the same as the above formula.

Kind regards

Saba
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top