How can I repeat a list of items attached to a unique list?

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
77
I've got a table that contains a data set from which I create a UNIQUE list of companies, so for example:
Amazon
Avis
Best Buy
Burger Town

I've also got a table of promotional data that I need to connect with each of these that contains multiple criteria, like so:
AdvertiserStart DateEnd DateCost
Washington Post02/01/202102/05/2021$50,000
Miami Herald02/03/202102/04/2021$40,000

There are hundreds of entries for each table.

Is there a formulaic way to repeat the promotional data for each of the companies? to get an output like the following?
CompanyAdvertiserStart DateEnd DateCost
AmazonWashington Post02/01/202102/05/2021$50,000
AmazonMiami Herald02/03/202102/04/2021$40,000
AvisWashington Post02/01/202102/05/2021$50,000
AvisMiami Herald02/01/202102/04/2021$40,000
Best BuyWashington Postetc. etc.

I keep trying to combine the two with the UNIQUE and SORT function, but I'm not having luck there. I even thought Power Query might be the answer, but I can't seem to get it to combine the two tables, and I'd really like a formula solution that doesn't require updating the query each time. Can this be done?

Thanks!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Book1
ABCDEF
1Amazon
2Avis
3Best Buy
4Burger Town
5
6
7AdvertiserStart DateEnd DateCost
8Washington Post2/1/20212/5/2021$50,000
9Miami Herald2/3/20212/4/2021$40,000
10
11
12
13
14CompanyAdvertiserStart DateEnd DateCost
15AvisWashington Post2/1/20212/5/2021$ 50,000
16AvisMiami Herald2/3/20212/4/2021$ 40,000
17Best BuyWashington Post2/1/20212/5/2021$ 50,000
18Best BuyMiami Herald2/3/20212/4/2021$ 40,000
19Burger TownWashington Post2/1/20212/5/2021$ 50,000
20Burger TownMiami Herald2/3/20212/4/2021$ 40,000
21     
22     
Sheet1
Cell Formulas
RangeFormula
A15:A22A15=IFERROR(INDEX($A$2:$A$4,CEILING(ROWS($A$15:A15),ROWS($A$8:$A$9))/ROWS($A$8:$A$9)),"")
B15:E22B15=IF($A15<>"",INDEX(A$8:A$9,MOD(ROWS(B$15:B15)-1,ROWS(A$8:A$9))+1),"")
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,961
Members
417,249
Latest member
serrulate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top