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?

Attached Files

Op List FEB-23.xlsx (462.3 KB, 8 views)

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.

Attached Files

MSOfficeForums50983.xlsx (79.8 KB, 2 views)

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.

Attached Images

Screenshot 2023-06-20 122456.jpg (51.4 KB, 33 views)

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?

"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

Attached Images

Screenshot 2023-06-23 084014.jpg (92.0 KB, 27 views)

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

Attached Files

MSOfficeForums50983v02.xlsx (177.0 KB, 1 views)

many thanks to you p45cal . i consider this to be perfect solution for my need. but how do i add/delete id number?

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.

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

You must do that yourself under the " thread tools"

__________________
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

p45cal i made it work. now i chaged the file of new month now its giving me this error. what do i do? only change being file name & sheet name in workbook.


save me please.

Attached Images

error.jpg (128.7 KB, 16 views)

Attached Files

test 4.zip (769.2 KB, 1 views)

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?

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

I don't know what you mean by this.

As mentioned by xor:Looking at some of the formulae in columns F, it seems you're interrogating files with a naming structure based on the date:
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.