Generate Combinations With Repetition in Excel Using LAMBDA
Question details
Generate every combination with repetition from a list of items, such as four selections from six items.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to list selections in which items can repeat and selection order does not create a separate combination.
- Observed behavior
- The desired output contains each combination once, with one item per column; large result sets may exceed calculation or memory limits.
Place six distinct items in A2:A7 and enter the selection size, such as 4, in B1. In Excel, check that your version supports dynamic arrays and the LET, LAMBDA, REDUCE, TAKE, and MAKEARRAY functions.
Generate combinations with an Excel LAMBDA formula
Use this dynamic-array formula to build each combination from the previous one without generating permutations.
The formula treats each item as a position in A2:A7 and keeps those positions in nondecreasing order. For six items and four selections, it returns 126 rows.
Use a positive whole number in B1. The formula accumulates all rows in memory, so large results may be slow or exceed Excel limits.
Put the items in A2:A7 and enter 4 in B1. Adjust the A2:A7 reference in the formula if your list has a different size.
Select an empty cell with room to spill down and right, then enter: =LET(items,A2:A7,k,B1,n,ROWS(items),total,COMBIN(n+k-1,k),start,MAKEARRAY(1,k,LAMBDA(rowNum,colNum,1)),states,IF(total=1,start,REDUCE(start,SEQUENCE(total-1),LAMBDA(acc,i,LET(last,TAKE(acc,-1),p,XMATCH(TRUE,last<n,0,-1),VSTACK(acc,MAKEARRAY(1,k,LAMBDA(rowNum,colNum,IF(colNum<p,INDEX(last,1,colNum),INDEX(last,1,p)+1)))))))),MAKEARRAY(ROWS(states),k,LAMBDA(rowNum,colNum,INDEX(items,INDEX(states,rowNum,colNum)))))
Press Enter. The results should spill into four columns; clear any cells blocking the spill area if Excel displays #SPILL!.

Generate combinations with Python in Excel
Use Python's combinations_with_replacement function when Python in Excel is available and a code-based approach is preferable.
List four-item combinations with WPS Spreadsheet
Use helper columns and INDEX formulas in WPS Spreadsheet to generate combinations with repetition for six items, without requiring LAMBDA or Python.
- 1. Enter the items: In WPS Spreadsheet, enter six distinct items in A2:A7 and enter 6 in B1 as the number of items.
- 2. Start the index sequence: Enter 1 in each of D2, E2, F2, and G2. These four cells represent the first selection.
- 3. Add the next-row formulas: Enter these formulas: D3: =IF(E2<$B$1,D2,IF(D2<$B$1,D2+1,"")); E3: =IF(D3="","",IF(F2<$B$1,E2,IF(E2<$B$1,E2+1,D3))); F3: =IF(D3="","",IF(G2<$B$1,F2,IF(F2<$B$1,F2+1,E3))); G3: =IF(D3="","",IF(G2<$B$1,G2+1,F3)).
- 4. Fill the sequence down: Select D3:G3 and drag the fill handle down through row 128. Rows 2–127 contain the 126 combinations; row 128 should be blank.
- 5. Show the item values: In H2, enter =IF(D2="","",INDEX($A$2:$A$7,D2)), then fill it across through K2 and down through row 128. Columns H:K display the combinations.

Frequently Asked Questions
How many combinations are there for six items taken four at a time with repetition?
There are 126. Enter =COMBIN(6+4-1,4) in a worksheet cell to calculate the count.
Does the formula treat different orders as separate combinations?
No. It keeps item positions in nondecreasing order, so selections such as A, B and B, A appear only once.
Why does the Excel formula show #SPILL!?
The result needs empty cells below and to the right of the formula. Clear the blocked spill area or move the formula to an empty part of the worksheet.
What if the combination list is too large?
First calculate =COMBIN(n+k-1,k), replacing n and k with your item count and selection size. Reduce the inputs or process the results in smaller batches if the count exceeds available worksheet or memory capacity.
Can duplicate items in the source list create duplicate-looking results?
Yes. The methods distinguish source positions, not just displayed values. Remove duplicate values from the source list first if you need unique value combinations.




