CODEWITHSUNDEEP
excelpowerquerycartesian-productcross-join
PT_21
PT_21

Reputation: 26

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?

Upvotes: -1

Views: 213

Answers (3)

DjC
DjC

Reputation: 1282

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)))
    )
)

merge_tables2.png

Upvotes: 5

horseyride
horseyride

Reputation: 21318

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

enter image description here

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"

Upvotes: 3

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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

Upvotes: 1

Related Questions