Match multiple values across several sheets and enter results on a different sheet

[drewship]

Hello all,
I have attached an example file, but the Issued sheet can contain over 200,000 rows so whatever we come up with will have to take that into account.

Several comparisons need to be made. The first is that the PARTNO (col B) on the Issued Sheet has to be matched to col D on the MaintPartNumber sheet. This means that the equipment on the Issued sheet must have maintenance performed on it.

The Issued sheet has the equipment that has been provided to various people.
The yellow highlighted rows are items that require maintenance, but no maintenance has been submitted (which is compared to the items on the MaintCompReport sheet).

The blue highlighted row does not require maintenance

The clear rows require maintenance and maintenance has been submitted (which is compared to the items on the MaintCompReport sheet).

The next comparison is between PartNo/SerialNo columns B and C in the Issued sheet with columns E and F on the MaintCompReport sheet.

For every item that requires maintenance as determined above, there should be at least one entry for each applicable maintenance on the MaintPartNumber sheet. So for example, if one of the items matches Part No 147 in Column D of the the MaintPartNumber sheet, that applicable serial number should have at lease one Mouse-1 AND one Mouse-2 maintenance entry in the MaintCompReport sheet.

If there is not a matching PartNo/SerialNo combination from the Issued sheet on the MaintCompReport sheet, then the results are copied to the MissingMaintReport sheet. This sheet currently contains the results of what I need the formulas to accomplish.

I tried VLOOKUP but it only grabs the first matching item so will not work here. I am not adverse to helper columns and have used them in the past if we need to use several formulas.

Any help is greatly appreciated.

[JeteMc]

This proposal utilizes several helper columns.
On the Issued sheet column G makes the maintenance required match using: =ISNUMBER(MATCH(B2,MaintPartNumber!D$2:D$11,0))
Column H makes the maintenance done match using: =IF(G2=FALSE,"",AND(ISNUMBER(MATCH(B2,MaintCompReport!E$2:E$6,0)),ISNUMBER(MATCH(C2,MaintCompReport!F$2:F$6,0))))
Column I counts the number of times the item is listed on the MaintPartNumber sheet using: =IF(H2=FALSE,COUNTIFS(MaintPartNumber!D$2:D$11,B2),"")
Column J makes a cumulative count using: =IF(I2="","",SUM(MAX(J$1:J1),I2))
On the MissingMaintReport sheet column T lists the part #'s using: =IFERROR(INDEX(Issued!B$2:B$11,AGGREGATE(15,6,(ROW(B$2:B$11)-ROW(B$1))/(Issued!$J$2:$J$11>=ROWS($A$1:$A1))/(Issued!$J$2:$J$11<>""),1)),"")
Column S counts the consecutive times an item is listed using: =IF(A12="","",IF(T12<>T11,1,SUM(S11,1)))
Columns A:E are populated using a formula similar to that of column T
Column F is populated using: =IF(A12="","",INDEX(MaintPartNumber!A$2:A$11,AGGREGATE(15,6,(ROW(A$2:A$11)-ROW(A$1))/(MaintPartNumber!D$2:D$11=T12),S12)))
Let us know if you have any questions.

[drewship]

Thanks JeteMc for this. I have not responded sooner as I am trying to incorporate this into my production file. I had to turn off the formula updating but even after that I have not been able to complete copying and pasting the formulas down all of the rows. Not sure if it is partially or entirely due to the size of the sheets or just because I have to use a government computer with all its security restrictions. I can see the progress bar move but I can't wait for hours for this to finish. I have to run this every week and post it to our SharePoint site. The formulas seem to work as needed on the test workbook so thank you. I have not been able to figure out how the formulas using the AGGREGATE term work but I can follow the rest. I will keep trying.

[drewship]

I was finally able to get all the formulas copied to all the rows and saved without calculating. I forced the calculations to start and after 3 hours only at 27%. Certainly not a viable solution for my situation but perhaps the formulas can be adapted to a VBA module so I can just run it whenever needed. I don't know if VBA would be any faster or not but at least it wouldn't try to calculate every time I made a change.

[JeteMc]

Both of the INDEX(...AGGREGATE(15,6... based formulas are basically INDEX(...SMALL(IF... array formulas. Since the AGGREGATE function handles the conditions without requiring Ctrl + Shift + Enter activation they should be more computationally efficient.
Lets see if it speeds things up if the formulas for columns A:E on the MissingMaintReport sheet are modified, as modeled in the attachment, to read*:

Please Login or Register  to view this content.

*Note that formulas in columns B:D index columns C:Eon the Issued sheet.
Let us know if you have any questions.
Edit: This post is a response to post #3 as I had not seen post #4 before submitting.

[JeteMc]

In response to post #4.
To get rid of the last IFERROR and speed this up as much as I can think of:
1. Place the following into cell K1 of the Issued sheet: =MAX(J:J)
2. Modify the formula in cell T12 and down on the MissingMaintReport sheet to read:

Please Login or Register  to view this content.

I assume that you mean you set the File > Options > Formulas > Calculation Options to Manual and after pasting the formulas you then pressed the F9 key (or selected calculated from the bottom left corner of the spreadsheet).
If that isn't the case you might try that.
I don't know enough about VBA to say whether or not thee is a way to do what you want in a faster manner, let alone how it would be done. If you would like the VBA contributors to look into the possibility then I suggest marking this thread as 'Solved' using the thread tools menu above your first post, and then opening a new thread on the VBA forum on this site.
You may want to include the attached file so that they can see what has been done to date.
Best of luck and I hope that you have a blessed day.

[drewship]

Thanks JeteMc. I tried these changes but they do help but not enough to be viable on the amount of data being calculated even on a couple different computers. I will close this and open under the VB thread. Hope your day is blessed.