excelpowerquerycartesian-productcross-join

Repeat Value for Every Instance of Another Value in Excel without using Power Query

I have about a thousand products in a table. In another table, I have 33 price contracts. I need an instance of each product for every contract.

For example, if I had the 4 following products:

product
ProductA
ProductB
ProductC
ProductD

and the four following contracts:

contract
contract1
contract2
contract3
contract4

I could load the following table in to Power Query

product contract1 contract2 contract3 contract4
productA 1 1 1 1
productB 1 1 1 1
productC 1 1 1 1
productD 1 1 1 1

and after unpivoting the contract columns get this

product attribute
productA contract1
productA contract2
productA contract3
productA contract4
productB contract1
productB contract2
productB contract3
productB contract4
productC contract1
productC contract2
productC contract3
productC contract4
productD contract1
productD contract2
productD contract3
productD contract4

This seems like an inefficient and roundabout way of getting the last table. Is there a more efficient way of doing this in excel?

Solution

  • If using MS365 then could try one of the following formulas as well:

    enter image description here

    • Formula used in cell F2

    =LET(
     _Merged, TOCOL(B3:B6&"|"&TOROW(D3:D6)),
     TEXTSPLIT(TEXTAFTER("|"&_Merged,"|",{1,2}),"|"))

    Or, another way:

    =LET(
     _Merged, TOCOL(B3:B6&"|"&TOROW(D3:D6)),
     HSTACK(TEXTBEFORE(_Merged,"|"),TEXTAFTER(_Merged,"|")))