I’m looking for, or looking to construct something that will work a bit like SUMIF, something that will apply a criterion to a range in row 2 and, where they match the criterion, do something with cells in that column in row 1.
But instead of having the matching items in row 1 treated as numbers and summed, I want them treated as strings and concatenated.
Illustration:
I have a table showing project numbers (one per column), and subcontractors (one per row):
| | A | B | C | D | E |
| 1 | | 23001 | 23002 | 23003 | 23004 |
| 2 | Ashley | | | | |
| 3 | Blair | | | | |
| 4 | Charlie | | | | |
When a subcontractor submits an invoice, its amount is entered in the column for the project it relates to:
| | A | B | C | D | E |
| 1 | | 23001 | 23002 | 23003 | 23004 |
| 2 | Ashley | 20.50 | | | 95.00 |
| 3 | Blair | | 34.22 | | 47.00 |
| 4 | Charlie | 72.00 | | 21.00 | |
Now, for each subcontractor, I’d like to construct a string listing all the projects for which they’ve submitted an invoice, so “23001 23004” for Ashley, “23002 23004” for Blair, etc.
Is there a way to construct a formula that scans through Ashley’s row, for example, and collects all the project numbers that correspond to all the cells in that row that contain a value > 0?
If it made things easier, it would be possible to swap the axes along which the data is laid out, i.e. to have each project in a row and each contractor in a columns.