Hi Douglas,
I'm having some issues copying the formulas, but I hope I can make myself clear.
I used Named Ranges to make formulas easier to read, but you can get the same result by using Array Formulas.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
|---|
1 | | | January | | | | February | | | | March | | | | |
2 | Contract | Total | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
3 | Orange | 30 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | 1 | 1 |
4 | Apple | 1 | | | | | | | | | | | | | |
5 | Pear | 5 | | | | | | | | | | 1 | 1 | 1 | 1 |
6 | Bannana | 17 | | | | | | | | | | | | | |
7 | Tangerine | 27 | | | | | | | | | | | | | |
8 | Pineapple | 10 | | | | | | | | | | | | | |
9 | Mango | 3 | | | | | | | | | | | | | |
<tbody>
</tbody>
Using the dummy worksheet shown above, I created Named Ranges for the data cells, the week headers and the fruit names (RowIDs). I also named a cell 'Week' to make it more readable.
Workbook Defined Names
| Name | Refers To |
|---|
| ActiveRows | =IF(WeekColumn>0,RowIDs,"") |
|---|
| Data | =Hoja2!$C$3:$BB$9 |
|---|
| Headers | =Hoja2!$C$2:$BB$2 |
|---|
| RowIDs | =Hoja2!$A$3:$A$9 |
|---|
| Week | =Hoja2!$A$12 |
|---|
| WeekColumn | =INDEX(Data,,MATCH(Week,Headers,0)) |
|---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>
I then created a range called WeekColumn that returns an array with all the cells corresponding to that week. It first finds the week within the headers using MATCH() and then retrieves the entire data column using INDEX(). Notice the second argument for INDEX() is empty, this tells Excel you want it to return the entire column as an array.
| WeekColumn | =INDEX(Data,,MATCH(Week,Headers,0)) |
|---|
<tbody>
</tbody>
You can the use the IF() function to return the fruit names (RowIDs) if the corresponding cell has a value greater than 0, otherwise return an empty string.
| ActiveRows | =IF(WeekColumn>0,RowIDs,"")
|
|---|
<tbody>
</tbody>
You could stop there and paste this formula over 7 cells, hit F2, CTRL+SHIFT+ENTER and get the results seen in cells C12:C18. If you want the results in one cell, separated by commas, head on to the next formula.
| A | B | C | D |
|---|
| 11 | Week | WeekColumn | ActiveRows | |
| 12 | 11 | 1 | Orange | Orange, Pear |
| 13 | | 0 | | |
| 14 | | 1 | Pear | |
| 15 | | 0 | | |
| 16 | | 0 | | |
| 17 | | 0 | | |
| 18 | | 0 | | |
<tbody>
</tbody>
This will only work if you have a relatively small number of fruit names cause it will otherwise become to boring to build the formula. In this case it was fast to write the formula.
- It uses INDEX() to get the nth value of the ActiveRows array and concatenates it with the next one separated by a space.
- TRIM() then reduces the numer of spaces to 1 between each word, because there will be spaces also for empty strings in the array.
- Having only one space separating each work, SUBSTITUTE() can change each for a comma and a space ", " to make it easier to read.
Worksheet Formulas
| Cell | Formula |
|---|
| D12 | =SUBSTITUTE( TRIM(
INDEX(ActiveRows,1)&" "&
INDEX(ActiveRows,2)&" "&
INDEX(ActiveRows,3)&" "&
INDEX(ActiveRows,4)&" "&
INDEX(ActiveRows,5)&" "&
INDEX(ActiveRows,6)&" "&
INDEX(ActiveRows,7)
),
" ",
", "
)
|
|---|
<tbody>
</tbody> |
<tbody>
</tbody>
I hope it helps.
PS. This is how it works with array formulas, just enter them using CTRL+SHIFT+ENTER.
| A
| B
| C
| D
|
|---|
11 | Week | WeekColumn | ActiveRows | |
12 | 11 | {=INDEX(Data,,MATCH(Week,Headers,0))} | {=IF(WeekColumn>0,RowIDs,"")} | =SUBSTITUTE(
TRIM(
INDEX(ActiveRows,1)&" "&
INDEX(ActiveRows,2)&" "&
INDEX(ActiveRows,3)&" "&
INDEX(ActiveRows,4)&" "&
INDEX(ActiveRows,5)&" "&
INDEX(ActiveRows,6)&" "&
INDEX(ActiveRows,7)
)," ",", "
) |
<tbody>
</tbody>
