+ Reply to Thread
Results 1 to 20 of 20

Match 3 sets of criteria for select relating to one name

  1. #1
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

    I would look at using VBA as you will have a formula in every cell: 600-800 rows X 125 columns = 75,000 - 100,000!
    Last edited by JohnTopley; 04-30-2021 at 02:45 AM.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

    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]
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

    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!
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

    Thanks. It's still coming up with: Run time error 9 - subscript out of range' and highlighting the line of: OutArr(r, Cidx) = mark ' mark ouput.

    This is after I paste the full raw data into columns A - Q in Sheet1 and full list of names into column A in Sheet2.
    Attached Images Attached Images
    Last edited by rooboyz; 05-03-2021 at 08:04 PM.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

    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.

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,827

    Re: Match 3 sets of criteria for select relating to one name

    can you explain with example.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  9. #9
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    140

    Re: Match 3 sets of criteria for select relating to one name

    Hi rooboyz,

    This is not vba but a formula. Copy and paste in U3
    HTML Code: 
    =IF(SUMPRODUCT(($A$2:$A$14=$T3)*($P$2:$P$14=U$2)*($B$2:$O$14 = ROUNDUP(COLUMN(U$1)/5,0)-4)) = 0, "", "YES")
    Then drag down and across.

    Hope this is what you are looking for.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

    @D13L
    Nice one.

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

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

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

  12. #12
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

    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.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    140

    Re: Match 3 sets of criteria for select relating to one name

    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.

  14. #14
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

    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.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

    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!

  16. #16
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    140

    Re: Match 3 sets of criteria for select relating to one name

    Hi rooboyz,

    Thanks for clarifying. IMPORTANT THIS IS AN ARRAY USE Shift+Ctrl+Enter:
    Please try:
    PHP Code: 
    =IF(SUM(--($A$2:$A$14=$T3)*--($P$2:$P$14=U$2)*--($B$2:$O$14=ROUNDUP(COLUMN(U$1)/5,0)-4))=0,"",SUM(--($A$2:$A$14=$T3)*--($P$2:$P$14=U$2)*--($B$2:$O$14=ROUNDUP(COLUMN(U$1)/5,0)-4)*($Q$2:$Q$14))) 
    EDITED: MACRO NOT WORKING RIGHT. AUTOFILL ISSUE. DOES NOT FILL 1st Columns and 1st ROWS. LEFT CODE FOR REFERENCE
    Please Login or Register  to view this content.



    LATEST EDIT: CODE ADJUSTED FOR AUTOFILL. ATTACHED WORKING MACRO EXCEL FILE
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by D13L; 05-05-2021 at 12:39 AM.

  17. #17
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

    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?

  18. #18
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Match 3 sets of criteria for select relating to one name

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

  19. #19
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    140

    Re: Match 3 sets of criteria for select relating to one name

    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.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Match 3 sets of criteria for select relating to one name

    @D13L
    Job well done!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How To Compare Two Sets Of Similar Data Sets To Find a Good Match
    By Mark123456789 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2016, 01:29 AM
  2. [SOLVED] Index Match With 2 Sets of Criteria, Each with Multiple Conditions
    By MrMacro2013 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-23-2014, 06:29 PM
  3. [SOLVED] How do I select and copy ranges of about 5000 rows based on sets of criteria?
    By dasseya1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2013, 10:03 PM
  4. How do I select and copy ranges of indefinite numbers of rows based on sets of criteria?
    By patricia hatton in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-02-2013, 10:11 AM
  5. Replies: 0
    Last Post: 09-02-2011, 07:44 AM
  6. Replies: 1
    Last Post: 01-19-2011, 08:49 AM
  7. Count rows that match 3 sets of criteria?
    By EricE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2005, 12:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1