INDEX COLLECT, how to collect multiple values in a range when the criteria matches

Molly H

I am pulling employee hours from a timesheet to a billing sheet. The billing sheet has a column that pulls the employee name from the timesheet based on the date, project number and cost code. I copy and paste the transactions into the billing sheet

=IFERROR(INDEX(COLLECT({Timesheet | Employee Name}, {Timesheet | Project}, [Project Number]@row, {Timesheet | Cost Code}, [Final Cost Code]@row, {Timesheet | Date}, Date@row), 1), "")

There's a few issues I'm running into: Sometimes two employees will work on the same project on the same date under the same cost code. So the formula isn't sophisticated enough to identify two values in the range. And each employee needs it's own row as they all have different labor rates.

Is it possible to solve both of these issues?

Paul Newcome

Give this a try:

Insert an auto number column (called "Auto" in this example).

Insert a text/number column (called "Row" in this example) and use the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in your INDEX/COLLECT you can change it from a 1 (the row to pull from indicator) to the following:

=IFERROR(INDEX(COLLECT({Timesheet | Employee Name}, {Timesheet | Project}, [Project Number]@row, {Timesheet | Cost Code}, [Final Cost Code]@row, {Timesheet | Date}, Date@row), COUNTIFS(Date:Date, @cell = Date@row, [Final Cost Code]:[Final Cost Code], @cell = [Final Cost Code]@row, Row:Row, @cell <= Row@row)), "")

So if you have three rows where the date and final cost code are the same in your billing sheet, the COUTNIFS will output 1 for the first row, 2 for the second, and 3 for the third. This tells the INDEX to pull the 1st/2nd/3rd entry from the array generated by the COLLECT function.

Molly H

Hey @Paul Newcome

Thanks for the help. I think the Auto Column and the Row Column need to be in the Timesheet, as that's where the multiple employees are clocking in with the same criteria. The billing sheet is just pulling the names over. And then how do I work out the need for an additional row whenever there's another employee for that transaction entry?

In my original question, the top screenshot is of the billing sheet, the timesheet is the bottom screenshot.

Paul Newcome

The auto and row columns would go on the billing sheet. You would have to manually enter extra rows for the extra people on the billing sheet, but you could add a column with a COUNTIFS to show how many people you need to accommodate for that particular date/cost code.

Molly H

I don't understand how that works - but it DOES!! Date 3/07 has both employees getting pulled - very exciting! However, I'm getting some blanks, see Mike on 1/17 and 1/24. Any idea on why that would happen?

Paul Newcome

Try removing the IFERROR from the formula and let me know what happens then?

Molly H

The error comes across as #INVALID VALUE.

Paul Newcome

Ok. That means for some reason we are not getting a match on those two rows.

Lets insert a temporary text/number column in the billing sheet to do some testing. We can delete this column out once we get this all sorted.

In both of those rows use this formula:

=COUNTIFS({Timesheet | Employee Name}, "Enter Mike's Name Here", {Timesheet | Project}, [Project Number]@row, {Timesheet | Cost Code}, [Final Cost Code]@row, {Timesheet | Date}, Date@row)

What do we get?

Molly H

It comes back as 1.

Paul Newcome

That's odd. We are definitely getting a match on the various range/criteria sets. What about this in the helper column:

=INDEX(COLLECT({Timesheet | Employee Name}, {Timesheet | Project}, [Project Number]@row, {Timesheet | Cost Code}, [Final Cost Code]@row, {Timesheet | Date}, Date@row), 1)

Molly H

Here are the results.

Paul Newcome

Hmm... The COUNTIFS is working to show that we are getting a match on our range/criteria sets. The INDEX works no problem on it's own, but when we combine the two we get an error on random dates (but all for Mike). Looking at your source data, I can't even tell where the difference is (outside of the date).

For example...

1/24/23 - 4717 - 1040 - Only one entry for that date/code/project - Does NOT work

1/31/23 - 4717 - 1040 - Only one match for that date/code/project - Does work

So... How exactly is the date column in both sheets being populated?

Molly H

I copy and paste from an excel file, which also has some helper columns such as LEFT and RIGHT formulas to clean up the data.

Molly H

It happens with Sonia and Hans as well

Paul Newcome

Of course you just had to to bring up Sonia and Hans... Haha. Ok. What happens if you go into the source sheet and re-enter the dates for those entries that are causing issues? I am wondering if some of the dates coming over from the excel file are being imported as text values.

Molly H

If we don't include Sonia and Hans, then what are we even doing here? :)

I updated the dates, still no changes. If it helps, the date column type is a Date type for both sheets.