Repeat one table for each item in another

[chaithanyakrishnagck]

I have a two ranges likes below:

Range1

Product

CPQ

SalesforceIQ

Quip

MC_Advertising_Studio

MC_Social_Studio

DataCloud

DataCloud

Analytics

Commerce_Cloud

Heroku

DMP_Krux

Essentials

Range 2: Countries

Brazil

Sweden

UK

Ireland

Turkey

India

Philippines

China

Argentina

Russia

Output: Populate the same list of products for each country like below:

Brazil	CPQ
Brazil	SalesforceIQ
Brazil	Quip
Brazil	MC_Advertising_Studio
Brazil	MC_Social_Studio
Brazil	DataCloud
Brazil	DataCloud
Brazil	Analytics
Brazil	Commerce_Cloud
Brazil	Heroku
Brazil	DMP_Krux
Brazil	Essentials
Sweden	CPQ
Sweden	SalesforceIQ
Sweden	Quip
Sweden	MC_Advertising_Studio
Sweden	MC_Social_Studio
Sweden	DataCloud
Sweden	DataCloud
Sweden	Analytics
Sweden	Commerce_Cloud
Sweden	Heroku
Sweden	DMP_Krux
Sweden	Essentials
UK	CPQ
UK	SalesforceIQ
UK	Quip
UK	MC_Advertising_Studio
UK	MC_Social_Studio
UK	DataCloud
UK	DataCloud
UK	Analytics
UK	Commerce_Cloud
UK	Heroku
UK	DMP_Krux
UK	Essentials

 

[Trebor76]

Hi *chaithanyakrishnagck,

Welcome to MrExcel!!

Try this:

Option Explicit
Sub Macro2()

    Dim rngProduct As Range
    Dim rngCountry As Range
    Dim wsOutput As Worksheet
    Dim lngOutputRow As Long
    
    'Loop through each item in named ranges called 'Products' and 'Countries'
    'and output to columns A and B (respectively) of sheet 'wsOutput'.
    'Change to suit.
    
    Application.ScreenUpdating = False
    
    Set wsOutput = ThisWorkbook.Sheets("Sheet2")
    lngOutputRow = 2
    
    For Each rngCountry In ThisWorkbook.Names("Countries").RefersToRange
        For Each rngProduct In ThisWorkbook.Names("Products").RefersToRange
            With wsOutput
                .Range("A" & lngOutputRow).Value = rngCountry.Value
                .Range("B" & lngOutputRow).Value = rngProduct.Value
                lngOutputRow = lngOutputRow + 1
            End With
        Next rngProduct
    Next rngCountry
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[chaithanyakrishnagck]

Robert - Thank you very much for responding to my note. I have edited the code as below but it shows the error at Set OutputRange line. Could you please help me with this?

Sub LoopForCombinations()

Dim CountryRange, ProductRange As Range
Dim OutputRange As Range
Dim lngoutputrow As Long

Application.ScreenUpdating = False

Set CountryRange = ActiveSheet.Range("B9:B18")
Set ProductRange = ActiveSheet.Range("C22:C33")

Set OutputRange = ThisWorksheet.Range("K15")

For Each CountryRange In CountryRange.RefersToRange
For Each ProductRange In ProductRange.RefersToRange
With OutputRange
.Range("K" & lngoutputrow).Value = CountryRange.Value
.Range("L" & lngoutputrow).Value = ProductRange.Value
lngoutputrow = lngoutputrow + 1
End With
Next ProductRange
Next CountryRange

Application.ScreenUpdating = True

End Sub

 

[Trebor76]

Try this:

Option Explicit
Sub LoopForCombinations()

    Dim CountryRange, ProductRange As Range
    Dim Country As Range, Product As Range
    Dim lngOutputrow As Long
    
    Application.ScreenUpdating = False
    
    Set CountryRange = Range("B9:B18")
    Set ProductRange = Range("C22:C33")
    lngOutputrow = 15
    
    For Each Country In CountryRange
        For Each Product In ProductRange
            Range("K" & lngOutputrow).Value = Country.Value
            Range("L" & lngOutputrow).Value = Product.Value
            lngOutputrow = lngOutputrow + 1
        Next Product
    Next Country
    
    Application.ScreenUpdating = True

End Sub

 

[chaithanyakrishnagck]

Robert - It worked so perfectly. Thanks a ton. May I know how I can appreciate you for your help? Do we have some thumbsup button or recommendation, or some token of appreciation? Please advise. I am new to this. I thoroughly appreciate your time and support.

 

[Peter_SSs]

Welcome to the MrExcel board!

Do we have some thumbsup button

Have a look at the bottom right of Robert's post(s)

BTW, you could also do this with formulas if you wanted. Each formula below copied down.

20 02 26.xlsm
	A	B	C	D
1	Product		Country	Product
2	CPQ		Brazil	CPQ
3	SalesforceIQ		Brazil	SalesforceIQ
4	Quip		Brazil	Quip
5	MC_Advertising_Studio		Brazil	MC_Advertising_Studio
6	MC_Social_Studio		Brazil	MC_Social_Studio
7	DataCloud		Brazil	DataCloud
8	DataCloud		Brazil	DataCloud
9	Analytics		Brazil	Analytics
10	Commerce_Cloud		Brazil	Commerce_Cloud
11	Heroku		Brazil	Heroku
12	DMP_Krux		Brazil	DMP_Krux
13	Essentials		Brazil	Essentials
14			Sweden	CPQ
15			Sweden	SalesforceIQ
16	Countries		Sweden	Quip
17	Brazil		Sweden	MC_Advertising_Studio
18	Sweden		Sweden	MC_Social_Studio
19	UK		Sweden	DataCloud
20	Ireland		Sweden	DataCloud
21	Turkey		Sweden	Analytics
22	India		Sweden	Commerce_Cloud
23	Philippines		Sweden	Heroku
24	China		Sweden	DMP_Krux
25	Argentina		Sweden	Essentials
26	Russia		UK	CPQ
27			UK	SalesforceIQ
28			UK	Quip
List

Cell Formulas
Range	Formula
C2:C28	C2	=IF(ROWS(C$2:C2)>ROWS(A$2:A$13)*ROWS(A$17:A$26),"",INDEX(A$17:A$26,INT((ROWS(C$2:C2)-1)/ROWS(A$2:A$13))+1))
D2:D28	D2	=IF(C2="","",INDEX(A$2:A$13,MOD(ROWS(D$2:D2)-1,ROWS(A$2:A$13))+1))

 

[Trebor76]

Hi *chaithanyakrishnagck,

You're welcome. Glad we got it sorted.

Hi Peter,

Thanks for the nifty formulas. The user now has a choice of both worlds.

Regards,

Robert

 

[chaithanyakrishnagck]

Awesome, you both (Robert and Peter) are the best. I can't thank you both enough for such a quick response with an accurate solution. I am completely new to VBA and I started learning today. Your help and support means a lot to me.

 

[Peter_SSs]

Thanks for the nifty formulas. The user now has a choice of both worlds.

Cheers.
My C2 formula could instead just be

=IFERROR(INDEX(A$17:A$26,INT((ROWS(C$2:C2)-1)/ROWS(A$2:A$13))+1),"")