Match 3 sets of criteria for select relating to one name

Hello,

I was hoping someone could assist with this one. In the attached workbook I have example of raw data from columns A:P. In the table starting from column T is where my results will be. I need a formula that matches the names in column T with column A, also matching the numbers in row 1 (starting in U1) with the name of the person for that row in column T, while also looking up column P for that person to see if they have any of the Monday to Friday days associated to them in column P. If they do i'd like to put the word yes in the corresponding cell in the second table that relates to the name, number and day of week.

Each column of the raw data on the left has around 600 - 800 rows. Also the results table on the right has some names in column T that don't appear in the raw data but still need to be showcased.

I'm open to different formats if there's a better way. But I need to work out something that gives me a similar layout to the table on the right and tells me what day each person has completed each number across the top.

Thanks!

See Attached: VBA

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]

Thanks this is fantastic. Much better than having that many formulas! One problem with it is that when I paste in my extended dataset down to row 900 in each of the columns of Sheet1 and I run the macro it comes up with 'Run time errors'.

The first one was a 'Run time error 9 - subscript out of range' and the line of: OutArr(r, Cidx) = mark ' mark ouput, was highlighted in yellow.

Originally I was going to tweak whatever formula was available to incorporate the duration data for each of those days per week as my next step. But now that it's in VBA code I have no idea how to tweak and incorporate the durations on my own. I've added in the attached workbook new columns in both sheets & highlighted in yellow where durations would appear (first in sheet1 dataset and then summing the durations to the relevant days per week. There will be multiple durations on the same weekdays where some people can work multiple jobs on the one day which would need to be totalled to one figure for the day.

When I added the duration columns the macro is now stopping at the line above where I was getting the Run time error 9 and is now a Run time error 13 'Type mismatch' which I'm assuming is because of the new columns I added..

Would it be possible to tweak the code to incorporate the daily duration totals per week and also rectify the Run time errors i'm getting with rows down to 900?

Thanks so much for your assistance!

Please Login or Register  to view this content.

I can't solve this without the data. There is no restriction in VBA on file size so I do not know/understand why it fails.

Probably a data problem.

Hi rooboyz,

This is not vba but a formula. Copy and paste in U3

HTML Code: 

Then drag down and across.

Hope this is what you are looking for.

@D13L
Nice one.

Look at changed file in Post #5 which has added column.

I ran a test with over 800 names and there was no issue with the macro.

This works great D13L. Do you know how i'd replace the word "YES" with the sum of duration in column Q for any days that fall within the same week number for that person? So, like in the attached example if Helen Z only worked one job on Wednesday for week 2 and it'll show 3 hours, where as Bill F worked two jobs on the Monday in week 10 so that should show 4 hours total as highlighted in yellow for that weeks results.

Hi rooboys,

I'm not sure what you mean by sum of duration. Would mind putting an example on the excel file with the expected results you you want.

It doesn't have to be on all of them at least give me expected results for just Bill F.


Edited: since the formula works, here is a macro code utilizing the formula.

Please Login or Register  to view this content.

Attached is an example of results for Bill F and Helen Z. Basically what I mean in regards to the word sum is if there's only one row that contains a duration, which matches the current criteria of weekday and week number for Bill, Helen (or any name) then instead of having the word "YES" appear in the results table, just have whatever the corresponding duration was for that day/week number from column Q.

If Bill or Helen have two or more rows in the raw data which correspond to the same weekday, week number & name criteria as the "YES" formula is covering, then total the durations (from column Q)in each of the rows matching the criteria for that person so it totals the amount of hours duration they worked on that day to cover any split shifts.

Oh, D13L I forgot to say your macro worked fine with returning the corresponding "YES" as per the original formula you wrote. Even after I pasted in my full dataset.

Once the formula is worked out to replace the "YES" component with the duration totals explained in my previous post would it be possible to also have the altered macro as well?

Thanks so much for your help!

Great! Thank you I appreciate your help. I'm assuming as long as I don't require any data appearing in column U or row 3 of the results section the new macro should work fine?

Scratch that. I just noticed your most recent post. Thank you again D13L. You saved me big time!!

Thanks for +rep rooboyz. It was really fun helping you.

I'm not really that good compared to others here (EX: JohnTopley, MarcL, Jindon, ALiW, BeeBo, etc.... I've learned a lot by reading post they are involved in). I've improve by getting engaged in project such as yours, and have assessed I have enough knowledge to complete it.

@D13L
Job well done!