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: 12

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

C
  • Question Question
SUMIFS or any other formula that can help
Replies
5
Views
79
Alex Blakenburg
A
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
219
Pbobas
P
K
  • Solved
is it possible copy data invoice information from sheet based on sheet name like this
Replies
4
Views
486
KalilMe
K
H
  • Solved
Problem with Sections and Index match
Replies
5
Views
589
Peter_SSs
Peter_SSs
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
277
MeaclH
M

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

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