0

Having issues trying to set up the right formula to find the first instance of a partial match that has an associated non-zero value. Basically, I have a project that logs estimated and actual hours worked by role. If a role has no estimated hours but does have actual hours, it needs to be rolled up into the lines with actuals OR display in a separate line under the role variation that does have actual hours.

Projects can include estimates for any level of seniority within roles. However, if there are only estimates for one level of seniority but actuals on a different level of seniority, the estimates must roll up to display under the level of seniority that has actuals. Seniority levels between roles (where the role is "ROLE") include:

  • ROLE Associate
  • ROLE snr.
  • ROLE jnr.

The reference tab is formatted like:

Name Role Estimated Hours Needed Actual Hours Worked
Director Amanda 0 20
Director Bob 0 25
Director Associate n/a 50 0
Secretary Kathy 0 30
Secretary jnr n/a 40 0

But, I need to collapse the rows since no "Director Associate" or "Secretary jnr" worked on the project; those estimated hours instead need to be put on Amanda's and Cathy's lines since they were the ones who worked on the job. The main tab needs to be formatted like:

Name Role Estimated Hours Needed Actual Hours Worked
Director Amanda 50 20
Director Bob 50 25
Secretary Cathy 40 30

I think also acceptable would be:

Name Role Estimated Hours Needed Actual Hours Worked
Director n/a 50 0
Director Amanda 0 20
Director Bob 0 25
Secretary n/a 40 0
Secretary Cathy 0 30

Easy enough to get names and roles but I cannot figure out how to do a partial search of the title PLUS the first non-zero value. In Amanda's case, I need to do a partial match search for "Director" and pull the first non-zero value for roles that include "Director" (in this case, it would be 50 hours under "Director Associate"). I tried and index/match with &"*" but the problem is "Director" doesn't have any additional characters after it, so it wouldn't be helpful in a use case where "Director" has the first non-zero value.

Including an example sheet here with some notes on what I've tried and found: https://docs.google.com/spreadsheets/d/1M0DO0lh3V1dDHcSBCZlE4ty7Y2TWJ0zLKWGlQSztHa0/edit#gid=0

Summary of notes:

  • Adding a &"*" at the end of my search doesn't work since the mid-tier seniority titles don't have additional strings after (ie, Art Director vs. Art Director jnr)
  • I'm unable to collapse roles with VLOOKUP but I set it up in the sheet anyway since it might help with referencing (I'm not as comfortable with index/match but it seems like it might be the way to go)
  • Creating a pivot table gets me the closest to what I'm looking for, except I can't collapse the roles, which is key

I came across this formula =INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)) in my research but I'm not sure how to apply it to the dataset I have since I need to be able to reference another sheet.

6
  • First, your alternate data representations don't seem to say the same thing. In the original data, you have a total of 90 estimated hours, but in your first "expected return" chart, you have 140 estimated hours: 50 being assigned to each director, Amanda and Bob. And that is not accurate. The second "expected return" chart is doable. But I think this goes beyond the kind of help that a free, volunteer-run forum is designed to provide. For best chances, I recommend A.) sharing a link to a sample spreadsheet with more data and B.) showing your research and attempts in more detail first.
    – Erik Tyler
    Jun 16 at 4:29
  • It is my assumption here that you have more than five total lines of data in your real-world data set. Otherwise, you could do all of this manually in 60 seconds. However, please confirm that this is the case (i.e., that your real data set contains more roles and people than shown in our post).
    – Erik Tyler
    Jun 16 at 11:45
  • Thanks for your feedback! I've added an example sheet as well as some more notes, you're right that the data is more extensive.
    – Kristy
    Jun 16 at 14:51
  • When I look at your Example Sheet, the data in the "Reference" tab is already in the format shown in your post (second "expected return"). Re: your Pivot sheet, your note ("...gets the layout/info we need, but is unable to combine/collapse roles") does not makes sense to me either, since it appears your pivot table does combine roles and allow you to collapse them. So it's unclear what your overall desired result is in either case, since both appear to already be achieved currently in the shared spreadsheet.
    – Erik Tyler
    Jun 16 at 23:49
  • Taking a look at both tabs, "Art Director" has actual hours but no estimates while "Art Director jnr." has estimates but no actual hours. What I'm looking for is to roll up the estimates into "Art Director" since it is the one with actuals but no estimate (like how the third table in my question has the estimates from Director Associate rolled up under Director.
    – Kristy
    Jun 17 at 14:57

1 Answer 1

1

Use vlookup() to replace associates, juniors and seniors with N/A in the Resource column with the first non-N/A resource that has a Role that matches the role, and query() to aggregate the results, like this:

=arrayformula( query( 
  { 
    if( 
      B1:B = "N/A", 
      iferror( 
        vlookup( 
          regexextract(A1:A, "(.+) (?:(?i)Associate|jnr|snr)"), 
          A1:B, { 1, 2 }, false 
        ), 
        A1:B 
      ), 
      A1:B 
    ), 
    C1:F 
  }, 
  "select Col1, Col2, sum(Col3), sum(Col4) 
   where Col1 is not null 
   and Col1 <> 'Total:' 
   group by Col1, Col2", 
  1 
) )

If there are just a few such cases, you can hard code those exceptions, like this:

=arrayformula( query( 
  { 
    if( A1:A = "Director Associate", { "Director", "Amanda" }, 
    if( A1:A = "Secretary jnr", { "Secretary", "Kathy" }, 
    A1:B ) ), 
    C1:D 
  }, 
  "select Col1, Col2, sum(Col3), sum(Col4) 
   where Col1 is not null 
   group by Col1, Col2", 
  1 
) )
5
  • Thank you for your answer! I think this may not be sustainable if I have a larger set of data, however; I've linked a sheet in my question above that gives a more accurate picture of the dataset I'm working with.
    – Kristy
    Jun 16 at 14:53
  • Your question does not specify how to determine the dependencies between various directors, associates, secretaries and secretary juniors. Please ask only one question per post. Jun 16 at 17:59
  • Apologies, I'm uncertain what you mean by dependencies? If you mean that it is unclear what the seniority levels are within a position, I will go ahead and add that to the question.
    – Kristy
    Jun 16 at 19:26
  • I tested it out and it seems to return the table exactly as is. I'm starting to think the issue is that the Role column needs to be edited and this is a lost cause of a problem, but I sincerely appreciate the help!
    – Kristy
    Jun 17 at 15:18
  • Paste the first formula in cell Reference!H1. You will see that the Art Director jnr. N/A row gets consolidated under Art Director Diane, among others. The second formula will not be useful unless you edit it to take exact titles and names into account, including the difference between jnr in your question and the jnr. in the sample spreadsheet. Jun 18 at 3:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.