2010 Magic Trick 672: Help with Small on large table

I

irfmevans

New Member
Joined
Aug 24, 2015
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I am struggling to find any way to insert a column into the MAIN tab pulling from the INV tab. The MAIN tab is one sales order for 1 customer with 29 rows, intentionally order qty of 1 in each. The INV Tab is the list of invoices for the same sales order. Some of the invoices were for quantities greater than 1, this is where my formulas do not work well. In using the SMALL fx, I can create a unique field for both tables and as long as the # of sale order rows = the # of invoice rows, I am good. Of course, it is not that simple.

My ex, see the highlight rows 9, 29, 30 & 31 in the Main tab. This shows that a qty of 4 total were listed in SO# 19424 SOLine# 4 & SORel# 1. Again, a line per qty of 1 is intentional to assist in the fx. In the INV Tab, the same lines are on rows 9, 10 & 11 (sorted by invoice date); however, row 11 on 12/22/2020 indicates qty 2 were invoiced. My dilemna shown on Main tab rows 30 & 31. I need a formula for col H for the invoice #.

Goal, the sales order is the MAIN data set with some "open" (not yet invoiced) lines and some "invoiced" lines. This spreadsheet will populate overnight and update the MAIN tab coming from the INV tab to indicate which lines have been invoices and which are still open. When qty's > 1 exist in the INV, I cannot figure a formula (fx) to populate Col H.

Note: I have a very large data set but only included one data set here. The most efficient formula (less time consuming in processing time) would be greatly appreciated but at this point, I will take whatever works. There will be many more columns added which I know those formulas making this file a large file when completed.

My last resort would be to find a fx that would force the INV tab to list at Qty 1 for each row, meaning repeat rows on the INV tab similar to the MAIN tab and then use a count/small function. But the level of effort to force both the INV and MAIN tabs to be singular (qty 1 each row) is also a resource ******.

Thank you very much.
MRExcel Help.xlsx
ABCDEFG
1INVOICES
228.00$ 2,253,380.00
3SO#SO LineSO RelInv DateInv#Inv QtyInvoice Total$
4194240012/2/20191910900$ 676,014.00
5194241111/16/20202013651$ 54,956.30
6194241111/19/20202013741$ 54,956.30
7194241112/7/20202013841$ 54,956.30
8194241112/7/20202013821$ 54,956.30
9194244112/10/20202013881$ 59,780.00
10194244112/11/20202013901$ 59,780.00
11194244112/22/20202014052$ 119,560.00
12194245112/22/20202014061$ 59,780.00
13194245112/23/20202014101$ 59,780.00
1419424511/12/20212114161$ 59,780.00
1519424211/26/20212114231$ 54,956.30
1619424211/27/20212114241$ 54,956.30
1719424221/29/20212114271$ 54,956.30
1819424222/8/20212114281$ 54,956.30
1919424222/16/20212114321$ 54,956.30
2019424222/22/20212114391$ 54,956.30
2119424222/26/20212114431$ 54,956.30
2219424223/8/20212114461$ 54,956.30
2319424223/12/20212114531$ 54,956.30
2419424223/22/20212114601$ 54,956.30
2519424223/30/20212114702$ 164,868.90
2619424313/30/20212114701$ -
2719424613/30/20212114731$ 59,780.00
2819424314/6/20212114821$ 54,956.30
2919424314/14/20212114931$ 54,956.30
3019424315/27/20212115471$ 54,956.30
31
INV
Cell Formulas
RangeFormula
F2:G2F2=+SUBTOTAL(9,F4:F30)



MRExcel Help.xlsx
ABCDEFGHIJKL
3ORDER DATANeed fx for this col
4Order DateOrder#SOLine#SORel#ORDER QTY Unit $ Ext SO $ Invoice#
512/2/201919424111$ 78,509.00$ 78,509.00
612/2/201919424211$ 78,509.00$ 78,509.00
712/2/201919424221$ 78,509.00$ 78,509.00
812/2/201919424311$ 78,509.00$ 78,509.00
912/2/201919424411$ 85,400.00$ 85,400.00201388
1012/2/201919424511$ 85,400.00$ 85,400.00
1112/2/201919424611$ 85,400.00$ 85,400.00
1212/2/201919424710$ -$ -
1312/2/201919424111$ 78,509.00$ 78,509.00
1412/2/201919424111$ 78,509.00$ 78,509.00
1512/2/201919424111$ 78,509.00$ 78,509.00
1612/2/201919424211$ 78,509.00$ 78,509.00
1712/2/201919424221$ 78,509.00$ 78,509.00
1812/2/201919424221$ 78,509.00$ 78,509.00
1912/2/201919424221$ 78,509.00$ 78,509.00
2012/2/201919424221$ 78,509.00$ 78,509.00
2112/2/201919424221$ 78,509.00$ 78,509.00
2212/2/201919424221$ 78,509.00$ 78,509.00
2312/2/201919424221$ 78,509.00$ 78,509.00
2412/2/201919424221$ 78,509.00$ 78,509.00
2512/2/201919424221$ 78,509.00$ 78,509.00
2612/2/201919424311$ 78,509.00$ 78,509.00
2712/2/201919424311$ 78,509.00$ 78,509.00
2812/2/201919424311$ 78,509.00$ 78,509.00
2912/2/201919424411$ 85,400.00$ 85,400.00201399
3012/2/201919424411$ 85,400.00$ 85,400.00201405<=can't figure out how to report this
3112/2/201919424411$ 85,400.00$ 85,400.00201405<=can't figure out how to report this
3212/2/201919424511$ 85,400.00$ 85,400.00
3312/2/201919424511$ 85,400.00$ 85,400.00
34
35
MAIN
Cell Formulas
RangeFormula
G5:G33G5=+E5*F5
 

Attachments

  • 1628689026683.png
    1628689026683.png
    62.9 KB · Views: 8

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
The formula below, in column H, worked for your data sample - not sure it would work on different scenarios, since you said you have a large data with more columns.

I used a helper column (J) to simplify the formula in column H (not strictly necessary) that gets the number of occurences of the trio SO# - SO Line - SO Rel in INV sheet.

Pasta1
ABCDEFGHIJ
1
2
3ORDER DATA
4Order DateOrder#SOLine#SORel#ORDER QTY Unit $ Ext SO $ Invoice#Helper
502/12/20191942411178509785092013654
602/12/20191942421178509785092114232
702/12/20191942422178509785092114279
802/12/20191942431178509785092114704
902/12/20191942441185400854002013883
1002/12/20191942451185400854002014063
1102/12/20191942461185400854002114731
1202/12/20191942471000Not Found0
1302/12/20191942411178509785092013744
1402/12/20191942411178509785092013844
1502/12/20191942411178509785092013824
1602/12/20191942421178509785092114242
1702/12/20191942422178509785092114289
1802/12/20191942422178509785092114329
1902/12/20191942422178509785092114399
2002/12/20191942422178509785092114439
2102/12/20191942422178509785092114469
2202/12/20191942422178509785092114539
2302/12/20191942422178509785092114609
2402/12/20191942422178509785092114709
2502/12/20191942422178509785092114709
2602/12/20191942431178509785092114824
2702/12/20191942431178509785092114934
2802/12/20191942431178509785092115474
2902/12/20191942441185400854002013903
3002/12/20191942441185400854002014053
3102/12/20191942441185400854002014053
3202/12/20191942451185400854002014103
3302/12/20191942451185400854002114163
MAIN
Cell Formulas
RangeFormula
G5:G33G5=+E5*F5
H5:H33H5=IFERROR(INDEX(INV!E$4:E$30,AGGREGATE(15,6,(ROW(INV!E$4:E$30)-ROW(INV!E$4)+1)/((INV!A$4:A$30=B5)*(INV!B$4:B$30=C5)*(INV!C$4:C$30=D5)),MIN(J5,COUNTIFS(B$5:B5,B5,C$5:C5,C5,D$5:D5,D5)))),"Not Found")
J5:J33J5=COUNTIFS(INV!A$4:A$30,B5,INV!$B$4:$B$30,C5,INV!$C$4:$C$30,D5)


Hope this helps

M.
 
Last edited:
Upvote 0
You are a genius! Thank you. I should be more in tune with the Aggregate fx than I am considering the volume of spreadsheets I use. I just hope this works well with the large data set. Thank you again!
 
Upvote 0
You're welcome. Glad to help.

I used the AGGREGATE function as a kind of SMALLIFS, i.e, with conditions.

I hope it works on your real data (maybe you can adapt it)

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
is it possible copy data invoice information from sheet based on sheet name like this
Replies
4
Views
334
KalilMe
K
H
  • Solved
Problem with Sections and Index match
Replies
5
Views
408
Peter_SSs
Peter_SSs
J
  • Solved
look up maximum value in range and return corresponding values in another column
Replies
4
Views
457
Fluff
Fluff
B
  • Solved
Help with Index/ Small formula to return valid values and not give a blank row for cells that don't
Replies
4
Views
451
Fluff
Fluff
E
  • Question
FIFO Macro giving different results each time it runs
Replies
7
Views
812
Lee Wei Sin
L

Forum statistics

Threads
1,214,979
Messages
6,122,561
Members
449,089
Latest member
Motoracer88

Share this page

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