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

Question

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?

Answer 1

You initally said you dont want powerquery for some reason, but it can be done much simpler then your example. Load the first table. Load the second table, and in that query, add column, custom column, with formula =OtherTableName. Expand the new column into rows using arrows at top the column. Here. we start with Table2 and use =Table1 then expand

let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Table1"})
in  #"Expanded Custom"

Answer 2

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

---

• 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,"|")))

---

Answer 3

Here's another couple of dynamic array options for MS365...

(1) To include a single column from each table:

=LET(
    array1, Table1[product],
    array2, Table2[contract],
    HSTACK(
        TOCOL(IF(SEQUENCE(, ROWS(array2)), array1)),
        TOCOL(IF(SEQUENCE(, ROWS(array1)), array2),, TRUE)
    )
)

(2) To include ALL columns from both tables:

=LET(
    a, SEQUENCE(ROWS(Table1)),
    b, SEQUENCE(, ROWS(Table2)),
    HSTACK(
        CHOOSEROWS(Table1, TOCOL(IF(b, a))),
        CHOOSEROWS(Table2, TOCOL(IF(a, b)))
    )
)