top of page Search

# Generate X number of entries for values listed with a total count

If you have a spreadsheet listing values (one of each kind) in one column, and a count ('X') for those values in an adjacent column, you can generate X number of multiple entries in another column for each value following the below steps.

1. We begin with a worksheet listing each type of a product once in column B. Column C lists the number of each product that is in the warehouse. In column A, we enter 1 in the cell to the left of the first product, and then the formula: =A2+C2 in the below cell to the left of the second product. Then pull down the formula in column A (press CTRL + D) for the rest of the products listed in column B. 2. In column D on the right, enter 1 through the grand total of the count in column C, the total number of products of all kinds. In this case it's 21. (If you enter a 1 in cell D2, select the 20 cells below, and the go to Home . . . Fill . . . Series, check off the Trend box, and enter a step value of 1, the full number range will be generated.) 3. Finally in column E, in cell E2, enter a VLOOKUP formula which will generate the multiple entries of the product.

=VLOOKUP(D2,A:B,2)

. . . reference the number of the multiple entry list, then the range of products and their totals, followed by a reference to the second column in that range, and then omit the usual reference to TRUE or FALSE at the end of the VLOOKUP formula. Pull this formula down using CTRL + D as well and one of each product in stock will be generated. bottom of page