Forum Discussion
Formula to spill an array a certain # of times?
Wondering if there is a Formula to spill an array a certain # of times? Any formulas to make this process easier would be awesome!
I'm creating a lookup template file, and I need to have every combination of two arrays for my rows. My process is very tedious and wondering if some formulas would help. If I could spill Array1 a certain number of times, that'd be nice. Then I need to have each unique value of Array2 copied down to each set of Array1.
Example:
| Array1 | Array2 | |||
| Color | Size | Count Color: | 3 | |
| red | small | Count Size: | 3 | |
| blue | medium | Total combinations | 9 | |
| green | large | |||
| Lookup based on Color and Size | ||||
| # | Color | Size | ||
| 1 | red | small | ||
| 2 | blue | small | ||
| 3 | green | small | ||
| 4 | red | medium | ||
| 5 | blue | medium | ||
| 6 | green | medium | ||
| 7 | red | large | ||
| 8 | blue | large | ||
| 9 | green | large |
Hi DobeJR
Please see the attached workbook with your desired results
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more
2 Replies
Hi DobeJR
Please see the attached workbook with your desired results
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more
ExcelExciting Thanks for this. This is hurting my brain trying to understand how it's working with the MOD formula, but I think I get it.... lol
CHEERS!
