#1
|
|||
|
|||
VLOOKUP with max value
Hi, as you can see in the attachment, i have a bonus sheet at 1st from then each sheet label corresponds to work date. in the bonus sheet i want to add all the production and machine number of each production day. so i did it with VLOOKUP function. At A4 cell is a id of a operator. that id is placed in different/same machine in a production day (if he was present). as you can see he was available at 01-02-23 sheet, he was placed in machine a1 & a2, his produced 80kg & 90kg with both of those machine. and so on. some times A operator can run 2 machine, someday 1 machine. now here is the problem. my formula took A1 and production 80kg since its 1st in order. but I want to add his max produced machine and production which is A2 & 90kg. how do i do that? |
#2
|
||||
|
||||
Seems like hard work.
As a possible alternative, have a look at the pivot table in the attached and see if it gets anywhere near what you want (pivot tables are very flexible). If you try to refresh the pivot table it will surely complain that it can't find the file; what you need to do is adjust cell A1 (yellow) to point to the location and name on your hard disk of that file that you attached in your message, then refresh the pivot. Once it's working with that same file, you can try using other source data files. |
#3
|
|||
|
|||
thanks for the reply
I'm not well versed with pivot table. why its adding production with target? for startup i dont need to calculate targets(for now) only production. If I need it it should be separate from production. and i dont need to add 2 m/c production if the operator operated both of them. i just need to calculate which m/c produced most. Last edited by stricky; 06-20-2023 at 01:46 AM. |
#4
|
||||
|
||||
My mistake regarding including Target values, that filter should have been included in the pivot table (you could do it yourself by fitering Attribute for only Production).
Regarding showing only which m/c produced most, there are many instances where 2 machines produced the same Max (operator 101833 on 2/2/2023 produced the max of 80 on both machines A3 and A4). What do you want to see? Your last column Total Produc will only show the sum of the machines which produced the max (ignoring all other machines); is this really what you want to see? Separately, in your picture, there are some numbers which I haven't seen; does this mean you succeeded in interrogating other files apart from the one you attached? If so that's great! Pending your needs, so far I've got it to look like this: 2023-06-21_133138.jpg And one more important thing, which version of Excel are you using? Does it have available such functions on the worksheet as FILTER, TOCOL and XMATCH? |
#5
|
|||
|
|||
"My mistake regarding including Target values, that filter should have been included in the pivot table (you could do it yourself by fitering Attribute for only Production)."
I dont know how. im really novice in pivot table. "there are many instances where 2 machines produced the same Max (operator 101833 on 2/2/2023 produced the max of 80 on both machines A3 and A4). What do you want to see?" Yes. if there a match, it will show only one. the production i gave on those sheet are all the copy/paste. real one match is quite rare. "Your last column Total Produc will only show the sum of the machines which produced the max (ignoring all other machines); is this really what you want to see?" Yes. "And one more important thing, which version of Excel are you using? Does it have available such functions on the worksheet as FILTER, TOCOL and XMATCH? " im using windows 10, office 2019. normal filter is avaiable. but i dont know about "TOCOL and XMATCH". i cant install addons or any software on this pc (locked down by IT dept.). Your solution is perfect. just ignore 2nd machine number. and add total in the end. that will do. many thanks to you |
#6
|
||||
|
||||
See attached.
Also added column AA outside the pivot. Not sure yet how to add grand total to rows without also 'adding' the machine IDs |
#8
|
||||
|
||||
I'm not sure what you're asking here:
If the source data changes you just need to refresh the pivot table (you may also have to adjust how far down the column the formulae in column AA). Otherwise you can exclude certain ID nos. by filtering them out in the pivot table. If I've misunderstood you question, come back. |
#9
|
|||
|
|||
Quote:
sorry solved the problem. i just had to 'select all' in the id filter. if i add a new id on source file it doesn't get selected automatically. Please mark this thread solved Last edited by stricky; 06-25-2023 at 07:30 PM. Reason: solved the problem |
#10
|
||||
|
||||
Quote:
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#12
|
||||
|
||||
In the workbook March Op List Juwel+Ratan.xlsx you have in sheet 23-03-23, in column F, lots of #VALUE! errors.
How would you like to handle them? |
#13
|
|||
|
|||
thanks p45cal for quick reply. it seems after i fixed them its working again. i dont know how it gets #VALUE! errors.
those #VALUE! comes from an formula mentioned in this thread. if click those cell without corresponding files opened. this error occurs. after open this files, errors fix themself automatically. Also row range needs to be increased to 200 |
#14
|
||||
|
||||
I don't know what you mean by this.
|
#15
|
||||
|
||||
As mentioned by xor:
Quote:
19-03-23-A.xlsx and that sometimes you're looking at a sheet called DELEV-A, and sometimes DELEV-B. What determines which sheet is to be looked at? (Maybe there's only one sheet in each of such workbooks?) It would probably be easier and more robust to grab the data for your current problem direct from these workbooks rather than via another workbook. If you want to take this further, come back. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Vlookup help | mance006 | Excel | 3 | 08-05-2021 03:19 AM |
Tell me which place the vlookup was successful (within a nested vlookup) | Izzii0x | Excel | 2 | 07-18-2019 10:29 PM |
How can I return Vlookup only if Specific Criteria is met in 1 column of the Vlookup Array | EcommDOC | Excel | 7 | 01-22-2018 11:00 AM |
something like VLOOKUP | hanvyj | Excel | 4 | 03-13-2012 09:03 AM |
Vlookup | Karen615 | Excel | 4 | 09-12-2011 02:30 PM |