I love VLOOKUP, but when I have one specific item name repeated in a table, I don't know how to
Capture.PNG
Can someone give me awesome tip?
In file or image, I want to get Total run time of work center (Line1/Line2) for product A and B.
I love VLOOKUP, but when I have one specific item name repeated in a table, I don't know how to
Capture.PNG
Can someone give me awesome tip?
In file or image, I want to get Total run time of work center (Line1/Line2) for product A and B.
Last edited by kimgag; 11-09-2018 at 12:23 PM.
Question should be: What is January's Line1 total run time, and Line3? what about whole year?
It will be Product A's January Demand "2" multiply by Line1's runtime for A + Product B's January Demand "3" multiply by Line1's runtime for B
Bascially at C13, I want to know F3*C9+F5*C10 in better layout which I can imply to 10,000 over numbers data table.
Hi Kimgag,
I might be misunderstanding your problem, but if you want to use the VLOOKUP on the product range, you can put the 2nd OP in the same row (next) as 1st OP. So you will have just one item to lookup.
Kimgag.jpgOr use HLOOKUP
Assuming C13 should be 0.0615, and further assuming that, in reality, you may have many more items to consider than just A & B, and items to exclude etc, you could use something like:
Formula:Please Login or Register to view this content.
modify ranges to suit , but note Array entry requirement.
note also that SUMPRODUCT can be slow when used in large volume, and against very large datasets. Avoid using in conjunction with Conditional Formatting, and volatile functions etc...
XLent, awesome answer. It is working!
I don't just want to take this tip and know nothing after, so if you could explain how that formula works briefly, it will be appreciated. If no time, it's fine. I will dig by myself.
@XLent,
Since your formula as an array formula there is no use of SUMPRODUCT fucntion. Instead, use SUM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Certainly, breaking into the component parts, using your sample file and cell C13 as example:
The below will generate a 1-dimensional Boolean array, where True denotes the Line# in your Product key matches the Line# in your calc row:
Formula:Please Login or Register to view this content.
the second range returns the associated Run Time for each of the Product / Line # combinations
Formula:Please Login or Register to view this content.
the below generates a 2-dimensional Boolean array, the test being whether or not the Products in the "multiplier lines" match the Product Keys, e.g.:
Formula:Please Login or Register to view this content.
so, in other words, you test both rows of your Product Multiplier against each row of your Product / Line # table
the below generates a 1-dimensional Boolean array (horizontal array), with values being the monthly "multipliers" for each product
Formula:Please Login or Register to view this content.
The multiplication of each array coerces the Booleans to their Binary equivalent, which in XL Worksheet terms is 0 for FALSE and 1 for TRUE (in VBA TRUE equates to -1)
So, post multiplication, you're effectively creating a 2-dimensional array of values -- 1 row per Product / Line #, and 1 column per "Product Multipler"
Formula:Please Login or Register to view this content.
the semi-colon indicates a "row", you have one "row" per Product/Line combo
the comma indicates a "column", you have two "columns" per row, these reflect the transposed Product Multipiler rows
so, in the above:
first "row" you get 0.023 for Product A w/Line 1 and Multiplier of Product A, and 0 for the same Product / Line # but with Product B multiplier (as not Product B)
second "row" you get 0 for both multipliers given Line # of the Product / Line combo (2) does not match that of the calc row (1)
third "row" you get 0.0385 for Product B w/Line 1 and Multiplier of Product B, and 0 for the same Product / Line # but with Product A multiplier (as not Product A)
fourth "row" you get 0 for both multipliers given Line # of the Product / Line combo (2) does not match that of the calc row (1)
As @AlKey has noted, you could use SUM, rather than SUMPRODUCT, however there will be no performance gain either way, and both can be adjusted to avoid explicit coercion if so required.
Last edited by XLent; 11-09-2018 at 12:10 PM.
I am facing one problem right now. I have followed the formula in exercise and it was ok. Once I added my 25,000 row/3 column (1st sheet) and 6,000 row/10 column into the formula, it pops the error msg (ran out of resources). I have gone back to check and review what formula I put wrong, nothing came out.. is this something to do with your 1st note (it might slow down if data is large)?
Yes, Arrays* are resource intensive given their iterative nature and fact that, unlike standard functions, they do not leverage the used range intersect.
if you can provide an indicative sample of your real life layout we can offer alternatives... counter intuitively it is often better in XL to use "more" lightweight calcs than "fewer" elegant, yet inefficient, ones... i.e. you may find adding a key or two will trivialise your summary calculations.
Note: SUMPRODUCT is processed like an Array, even without use of TRANSPOSE etc...
Is there any way I can share this file with you? I can't upload this file idk why. it is only 3MB. tried to PM to you, it doesn't go to....
Save a copy of the file with .xlsb extension -- this will offer better compression and also, bizarrely, is assigned a higher threshold on the upload.
Include a few expected results, this makes it easier for people to follow / test whilst trying to assist.
Here is the excel file. I have put what I am expecting at "Final" sheet. You would understand fast as you already helped me out once..
Key question, will it be possible to have the Demand & Labor tabs sorted?
With large / complex datasets sorting data is often the biggest factor in optimising calculation performance - sorting precedent ranges opens up binary search methods, and binary search methods mean you are only ever working with a tiny fraction of the larger dataset.
Those data can be sorted to some extent as I also pull the data from other raw data using Pivot and filtering.
I would say Whatever is needed on data sorting can be done pretty much. I have access to them.
Further to the above, the attached is an extension of your sample file -- the notable changes are as follows:
- I sorted BOTH the Demand tab (by Col A), and the Labor tab (by Col A & B)
- I created a Dynamic Named Range {DNR} entitled _Demand
- I added formulae on Labor tab (Cols D onwards) that leverage binary search approach {quicker to do 2 binary searches on big data than one linear lookup}
On my machine, which is pretty old, the workbook calculates in half a dozen seconds -- still not brilliant, but decent enough I would say?
{given the nature of calc engine the SUMIF calcs on first tab will run quickly, despite entire column range - i.e. overhead of introducing a DNR not worthwhile}
NOTE: to reduce file size I removed the formulae from both Final & Labor tabs row 3 onwards -- to reassert copy row 2 and apply to remaining rows.
@kimgag, suffice to say my prior suggestion didn't sit comfortably with me (given performance)
I've attached an alternative version which should be quicker as, in hindsight, the introduction of the DNR referenced in my prior post / attachment is only going to slow things down.
Per the prior example I would still advise sorting the 'Demand per material tab' data (by Col A) but you needn't sort the Labor tab.
As before, to reduce file size, I have removed the formulae from rows 3 onwards in both Final & Labor tabs, to reinstate copy calcs in row 2 downwards.
With all formulae in place the file should calculate in a second or so.
edit: I also note, belatedly, that my previous suggestion included some rogue values {more specifically August & September}, apologies for any confusion caused.
Last edited by XLent; 11-10-2018 at 04:01 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks