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

Options
✭✭

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?

Tags:
«1

• ✭✭✭✭✭✭
Options

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.

• ✭✭
edited 05/17/23
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

The error comes across as #INVALID VALUE.

• ✭✭✭✭✭✭
Options

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?

• ✭✭
Options

It comes back as 1.

• ✭✭✭✭✭✭
Options

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)

• ✭✭
Options

Here are the results.

• ✭✭✭✭✭✭
Options

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?

• ✭✭
Options

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.

• ✭✭
Options

It happens with Sonia and Hans as well

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!