Need to create multi-criteria lookup without helper column

gmomo

New Member
Joined
Apr 13, 2016
Messages
4
Hi folks, thanks for all the awesome tips thus far!

SUMMARY:
I have (1) purchase orders for crayons and (2) a list of several different companies with bagged crayons in varying sized quantities. I'd like to find orders that were made in sequential order that have matching SKU's that are 1-digit apart in incremental order (100,101 but not 101,100) without using a helper column

STEPS TAKEN:
1) The 'Orders' sheet (Column B&C) - contains purchase orders for crayon colors and the Qty. needed
2) All Sku's from the 'Stock' sheet that match the criteria (Color and Qty) are selected from various companies (ex. Shady, Grady, 1-size Betty) and placed (SMALL'd) on the 'Orders' sheet 'Matches area' (Column F to I) as helper columns for step 3.

Working formula in the "MATCHES" section (F4 to I19)
(This formula gets the appropriate SKU from the "stock" sheet and looks for: Matching color, and quantity)
(F4) =IFERROR(INDEX(Stock!$A$4:$A$25,SMALL(IF([@Color]=Stock!$B$4:$B$25,IF([@Qty]>=Stock!$D$4:$D$25,IF([@Qty]<=Stock!$E$4:$E$25,ROW(Stock!$A$4:$A$25)-ROW(Stock!$A$4)+1))),COLUMNS($A4:A4))),"")

3) On the 'Orders' sheet, I then created a '+1 SKU difference' formula (Columns K to N) to search through the Matches (F to I) and only show results for SKU's from the 'Stock' sheet that are 1-digit apart in incremental order.
Working formula for the SKU's that have a 1-digit incremental counterpart
(K4) =IFERROR(SMALL(IF(COUNTIF($F5:$I5,$F4:$I4+1),$F4:$I4),COLUMNS($A4:A4)),"")

PROBLEM:
I'm in need of doing the multi-criteria search for the current row and following row ('Orders' sheet row 4 and row 5) and showing SMALL'd results from left to right revealing all SKU's that have a +1 SKU in the next row within one single formula.

In case it's not clear, I need to somehow get this into one formula using this logic:

Find results from ('Orders' F4-I4)
=IFERROR(INDEX(Stock!$A$4:$A$25,SMALL(IF([@Color]=Stock!$B$4:$B$25,IF([@Qty]>=Stock!$D$4:$D$25,IF([@Qty]<=Stock!$E$4:$E$25,ROW(Stock!$A$4:$A$25)-ROW(Stock!$A$4)+1))),COLUMNS($A4:A4))),"")

and subtract the SKU number from the results of Sku's found as a result of this formula ('Orders' F5-I5)
=IFERROR(INDEX(Stock!$A$4:$A$25,SMALL(IF([@Color]=Stock!$B$4:$B$25,IF([@Qty]>=Stock!$D$4:$D$25,IF([@Qty]<=Stock!$E$4:$E$25,ROW(Stock!$A$4:$A$25)-ROW(Stock!$A$4)+1))),COLUMNS($A5:A5))),"")

and report all SKU's that have a 1-digit higher value in the following row like I did with this formula (K4-H4) =IFERROR(SMALL(IF(COUNTIF($F5:$I5,$F4:$I4+1),$F4:$I4),COLUMNS($A4:A4)),"")


- THE DATA SET -​

Filelink:
https://dl.dropboxusercontent.com/u/2447350/pics/Crayon Demo.xlsx

Note: Correct results are in the 'SKU DIFFERENCE = +1 (Column K through L) area on the 'Orders' sheet. It is currently searching through results from the Matches area. I will have thousands of these rows and want to eliminate the mess by creating a single formula.


Sheet name "Orders"

PURCHASE ORDERSMATCHESSKU
DIFFERENCE = +1
(A) Order #(B) Color (C) Qty(F) SKU(G) SKU2(H) SKU3(I) SKU4(K) +1 SKU DIFF(L) +1 SKU DIFF2
1Grey96101400601400
2Grey81100401601
3Black90300701
4Blue80200801200
5Blue105201501
6Black92300701
7Blue116201501
8Black120301703
9Black55702802
10Black95300701
11Grey80100401601800401601
12Grey60402602800800
13Blue15500801500
14Blue125501
15Black51702802702
16Black121301703

<tbody>
</tbody>


Sheet name "Stock"

STOCK
SKU#Color GroupPackageQty MinQty Max
100GreyShady Grey Small8085
101GreyShady Grey Large86110
200BlueShady Blue Small8089
201BlueShady Blue Medium90120
202BlueShady Blue Large121115
300BlackShady Black Small90110
301BlackShady Black Large111125
400GreyGrady Grey Large90110
401GreyGrady Grey Med7589
402GreyGrady Grey Small5574
500BlueGrady Blue Ex Small1525
501BlueGrady Blue Extra Large100150
600GreyGrady Grey Special Large100119
601GreyGrady Grey Special Medium8099
602GreyGrady Grey Special Small5079
700BlackGrady Black Large100110
701BlackGrady Black Medium6199
702BlackGrady Black Small5060
703BlackGrady Black Extra Large111130
800GreyBetty 1-size1080
801BlueBetty 1-size1080
802BlackBetty 1-size1080

<tbody>
</tbody>


Thanks in advance!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

gmomo

New Member
Joined
Apr 13, 2016
Messages
4
Due to my poor explanation, I'm assuming no one could figure out what I was looking for but, unfortunately, I don't know how to erase/edit the original.

I found a work around, but it involved searching for ROWs in sequential order instead of the SKU's which proved to be much more complex.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,990
Messages
5,628,001
Members
416,286
Latest member
ko15

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
Top