How to Create Product Bundles with Automatic Codes and Prices in Spreadsheets
Question details
The user needs to generate every possible combination of configurable product options to create product bundles, automatically calculating each bundle's complete product code, combined description, and total price.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing an e-commerce catalog or inventory where users must generate all possible variations of customizable products from separate option lists.
- Observed behavior
- A method is required to generate the Cartesian product of multiple option lists, concatenate the corresponding text strings, and sum up the individual prices into a new dataset.
Ensure your individual product options, including their respective base codes, descriptions, and prices, are organized in separate, clearly labeled data tables before attempting to generate combinations.
Use Power Query to Generate Product Combinations
Power Query is the most efficient and robust way to generate a Cartesian product (all combinations) of multiple data tables without writing complex code.
By utilizing the cross-join functionality within Power Query, you can automatically pair every item in your first option list with every item in the second. You can then easily add custom columns to calculate the final price and concatenate the product codes.
Select your first product option list (e.g., Base Product) and press Ctrl+T to format it as a Table. Repeat this for your second option list (e.g., Accessories).
Click anywhere inside your first table, go to the 'Data' tab, and select 'From Table/Range'. In the Power Query Editor, go to 'Close & Load To' and select 'Only Create Connection'. Do the same for the second table.
Open the first query in the Power Query Editor. Go to 'Add Column' > 'Custom Column'. In the formula box, simply type the exact name of your second query (e.g., = Accessories) and click OK. This adds a column containing the entire second table in every row.
Click the double-arrow icon on the header of the new custom column to expand the fields (select Code, Description, and Price). Now, go to 'Add Column' > 'Custom Column' again to create your combined fields (e.g., =[Base Code] & "-" & [Accessory Code] for the product code, and =[Base Price] + [Accessory Price] for total price).
Once your complete product codes, combined descriptions, and total prices are calculated, go to 'Home' > 'Close & Load' to place the generated bundle list into a new worksheet.
Use Formulas to Concatenate and Sum Bundle Data
If you already have combinations listed or prefer using formulas, you can calculate the bundle codes and prices using built-in spreadsheet functions.
Automate Product Bundles with WPS Spreadsheet
WPS Spreadsheet offers powerful data management tools, including advanced formulas and easy-to-use table management, helping you generate product combinations, format text strings, and calculate totals effortlessly.
- 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the product option lists.
- 2. Set Up Output Columns: Create designated columns for your target data: Complete Code, Description, and Price.
- 3. Apply Formulas: Use standard formulas like =A2&"-"&B2 for codes and =C2+D2 for prices.
- 4. Auto-Fill Data: Double-click the bottom-right corner of the formula cells to instantly apply calculations across thousands of bundle combinations.
Frequently Asked Questions
What is a Cartesian product in spreadsheets?
A Cartesian product is a mathematical operation that returns a set from multiple lists where every item from the first list is paired with every item from the second list. In inventory management, this generates all possible product combinations and variations.
How do I combine text from multiple cells to make a single product code?
You can use the ampersand symbol (&) or the CONCAT function. For example, the formula `=A2 & "-" & B2` will join the text located in cell A2 and cell B2, placing a hyphen between them.
Can bundle prices update automatically if individual option prices change?
Yes. If your bundle total price column uses formulas that reference the original option tables (e.g., using a simple addition formula or lookups), any updates you make to the base option prices will automatically trigger a recalculation of the bundle totals.
