Create Duplicate records based on possible combinations of other values

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
666
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

Need your help with macros to create the output from the base data.

I have a set of data from A1 to D1 refer below
Create Duplicate records based on possible combinations of other values.xlsx
ABCD
2CountryProduct CodeSub product CodeCommission%
3USA48207708921%
4USA48818620622%
5USA48818696122%
6USA48818648826%
7USA48188267227%
8USA38818648828%
Sheet1


In sheet 2 I want to create , the duplicate of this range based on values in Sheet 1 Col H to J where I have different combinations
Create Duplicate records based on possible combinations of other values.xlsx
HIJ
2StatusPriorityRisk
3ActiveHighHigh Risk
4InactiveMedium Moderate Risk
5LowLow Risk
Sheet1


Ex: I want to duplicate the data for Active and Inactive, and within Active High, Medium, Low and within High, High risk, low Risk and moderate risk and so on.
like below.

Create Duplicate records based on possible combinations of other values.xlsx
ABCDEFG
11CountryProduct CodeSub product CodeCommission%StatusPriorityRisk
12USA48207708921%ActiveHighHigh Risk
13USA48818620622%ActiveHighHigh Risk
14USA48818696122%ActiveHighHigh Risk
15USA48818648826%ActiveHighHigh Risk
16USA48188267227%ActiveHighHigh Risk
17USA38818648828%ActiveHighHigh Risk
18USA48207708921%ActiveHighLow Risk
19USA48818620622%ActiveHighLow Risk
20USA48818696122%ActiveHighLow Risk
21USA48818648826%ActiveHighLow Risk
22USA48188267227%ActiveHighLow Risk
23USA38818648828%ActiveHighLow Risk
24USA48207708921%ActiveHighModerate Risk
25USA48818620622%ActiveHighModerate Risk
26USA48818696122%ActiveHighModerate Risk
27USA48818648826%ActiveHighModerate Risk
28USA48188267227%ActiveHighModerate Risk
29USA38818648828%ActiveHighModerate Risk
30USA48207708921%ActiveLowHigh Risk
31USA48818620622%ActiveLowHigh Risk
32USA48818696122%ActiveLowHigh Risk
33USA48818648826%ActiveLowHigh Risk
34USA48188267227%ActiveLowHigh Risk
35USA38818648828%ActiveLowHigh Risk
36USA48207708921%ActiveLowLow Risk
37USA48818620622%ActiveLowLow Risk
38USA48818696122%ActiveLowLow Risk
39USA48818648826%ActiveLowLow Risk
40USA48188267227%ActiveLowLow Risk
41USA38818648828%ActiveLowLow Risk
42USA48207708921%ActiveLowModerate Risk
43USA48818620622%ActiveLowModerate Risk
44USA48818696122%ActiveLowModerate Risk
45USA48818648826%ActiveLowModerate Risk
46USA48188267227%ActiveLowModerate Risk
47USA38818648828%ActiveLowModerate Risk
48USA48207708921%ActiveMediumHigh Risk
49USA48818620622%ActiveMediumHigh Risk
50USA48818696122%ActiveMediumHigh Risk
51USA48818648826%ActiveMediumHigh Risk
52USA48188267227%ActiveMediumHigh Risk
53USA38818648828%ActiveMediumHigh Risk
54USA48207708921%ActiveMediumLow Risk
55USA48818620622%ActiveMediumLow Risk
56USA48818696122%ActiveMediumLow Risk
57USA48818648826%ActiveMediumLow Risk
58USA48188267227%ActiveMediumLow Risk
59USA38818648828%ActiveMediumLow Risk
60USA48207708921%ActiveMediumModerate Risk
61USA48818620622%ActiveMediumModerate Risk
62USA48818696122%ActiveMediumModerate Risk
63USA48818648826%ActiveMediumModerate Risk
64USA48188267227%ActiveMediumModerate Risk
65USA38818648828%ActiveMediumModerate Risk
66USA48207708921%InactiveHighHigh Risk
67USA48818620622%InactiveHighHigh Risk
68USA48818696122%InactiveHighHigh Risk
69USA48818648826%InactiveHighHigh Risk
70USA48188267227%InactiveHighHigh Risk
71USA38818648828%InactiveHighHigh Risk
72USA48207708921%InactiveHighLow Risk
73USA48818620622%InactiveHighLow Risk
74USA48818696122%InactiveHighLow Risk
75USA48818648826%InactiveHighLow Risk
76USA48188267227%InactiveHighLow Risk
77USA38818648828%InactiveHighLow Risk
78USA48207708921%InactiveHighModerate Risk
79USA48818620622%InactiveHighModerate Risk
80USA48818696122%InactiveHighModerate Risk
81USA48818648826%InactiveHighModerate Risk
82USA48188267227%InactiveHighModerate Risk
83USA38818648828%InactiveHighModerate Risk
84USA48207708921%InactiveLowHigh Risk
85USA48818620622%InactiveLowHigh Risk
86USA48818696122%InactiveLowHigh Risk
87USA48818648826%InactiveLowHigh Risk
88USA48188267227%InactiveLowHigh Risk
89USA38818648828%InactiveLowHigh Risk
90USA48207708921%InactiveLowLow Risk
91USA48818620622%InactiveLowLow Risk
92USA48818696122%InactiveLowLow Risk
93USA48818648826%InactiveLowLow Risk
94USA48188267227%InactiveLowLow Risk
95USA38818648828%InactiveLowLow Risk
96USA48207708921%InactiveLowModerate Risk
97USA48818620622%InactiveLowModerate Risk
98USA48818696122%InactiveLowModerate Risk
99USA48818648826%InactiveLowModerate Risk
100USA48188267227%InactiveLowModerate Risk
101USA38818648828%InactiveLowModerate Risk
102USA48207708921%InactiveMediumHigh Risk
103USA48818620622%InactiveMediumHigh Risk
104USA48818696122%InactiveMediumHigh Risk
105USA48818648826%InactiveMediumHigh Risk
106USA48188267227%InactiveMediumHigh Risk
107USA38818648828%InactiveMediumHigh Risk
108USA48207708921%InactiveMediumLow Risk
109USA48818620622%InactiveMediumLow Risk
110USA48818696122%InactiveMediumLow Risk
111USA48818648826%InactiveMediumLow Risk
112USA48188267227%InactiveMediumLow Risk
113USA38818648828%InactiveMediumLow Risk
114USA48207708921%InactiveMediumModerate Risk
115USA48818620622%InactiveMediumModerate Risk
116USA48818696122%InactiveMediumModerate Risk
117USA48818648826%InactiveMediumModerate Risk
118USA48188267227%InactiveMediumModerate Risk
119USA38818648828%InactiveMediumModerate Risk
Sheet1


attached the file for reference as well.

Thanks for your help :)

Regards
Arvind
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
is this the expected result?
Book1
ABCDEFGH
1StatusPriorityTThis can be removed if you wish itCustom.RiskCountryProduct CodeSub product CodeCommission%
2ActiveHighHigh RiskHigh RiskUSA4820770890.2138
3ActiveHighHigh RiskHigh RiskUSA4881862060.216
4ActiveHighHigh RiskHigh RiskUSA4881869610.2232
5ActiveHighHigh RiskHigh RiskUSA4881864880.2633
6ActiveHighHigh RiskHigh RiskUSA4818826720.271
7ActiveHighHigh RiskHigh RiskUSA3881864880.2818
8ActiveHighHigh RiskModerate RiskUSA4820770890.2138
9ActiveHighHigh RiskModerate RiskUSA4881862060.216
10ActiveHighHigh RiskModerate RiskUSA4881869610.2232
11ActiveHighHigh RiskModerate RiskUSA4881864880.2633
12ActiveHighHigh RiskModerate RiskUSA4818826720.271
13ActiveHighHigh RiskModerate RiskUSA3881864880.2818
14ActiveHighHigh RiskLow RiskUSA4820770890.2138
15ActiveHighHigh RiskLow RiskUSA4881862060.216
16ActiveHighHigh RiskLow RiskUSA4881869610.2232
17ActiveHighHigh RiskLow RiskUSA4881864880.2633
18ActiveHighHigh RiskLow RiskUSA4818826720.271
19ActiveHighHigh RiskLow RiskUSA3881864880.2818
20InactiveMedium Moderate RiskHigh RiskUSA4820770890.2138
21InactiveMedium Moderate RiskHigh RiskUSA4881862060.216
22InactiveMedium Moderate RiskHigh RiskUSA4881869610.2232
23InactiveMedium Moderate RiskHigh RiskUSA4881864880.2633
24InactiveMedium Moderate RiskHigh RiskUSA4818826720.271
25InactiveMedium Moderate RiskHigh RiskUSA3881864880.2818
26InactiveMedium Moderate RiskModerate RiskUSA4820770890.2138
27InactiveMedium Moderate RiskModerate RiskUSA4881862060.216
28InactiveMedium Moderate RiskModerate RiskUSA4881869610.2232
29InactiveMedium Moderate RiskModerate RiskUSA4881864880.2633
30InactiveMedium Moderate RiskModerate RiskUSA4818826720.271
31InactiveMedium Moderate RiskModerate RiskUSA3881864880.2818
32InactiveMedium Moderate RiskLow RiskUSA4820770890.2138
33InactiveMedium Moderate RiskLow RiskUSA4881862060.216
34InactiveMedium Moderate RiskLow RiskUSA4881869610.2232
35InactiveMedium Moderate RiskLow RiskUSA4881864880.2633
36InactiveMedium Moderate RiskLow RiskUSA4818826720.271
37InactiveMedium Moderate RiskLow RiskUSA3881864880.2818
38LowLow RiskHigh RiskUSA4820770890.2138
39LowLow RiskHigh RiskUSA4881862060.216
40LowLow RiskHigh RiskUSA4881869610.2232
41LowLow RiskHigh RiskUSA4881864880.2633
42LowLow RiskHigh RiskUSA4818826720.271
43LowLow RiskHigh RiskUSA3881864880.2818
44LowLow RiskModerate RiskUSA4820770890.2138
45LowLow RiskModerate RiskUSA4881862060.216
46LowLow RiskModerate RiskUSA4881869610.2232
47LowLow RiskModerate RiskUSA4881864880.2633
48LowLow RiskModerate RiskUSA4818826720.271
49LowLow RiskModerate RiskUSA3881864880.2818
50LowLow RiskLow RiskUSA4820770890.2138
51LowLow RiskLow RiskUSA4881862060.216
52LowLow RiskLow RiskUSA4881869610.2232
53LowLow RiskLow RiskUSA4881864880.2633
54LowLow RiskLow RiskUSA4818826720.271
55LowLow RiskLow RiskUSA3881864880.2818
For Result 2 to expand


here is the link to file:

This was done in PQ, which is simpler.

I also added the reversed result table if you need it.
 

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
666
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Rado,

Thanks for the quick turn around, the result seems ok, but not completely, I have given the sample output on the same file. It has to create rows with all combinations,
Active this will have > High > under that 3 options ( High risk, moderate risk and low risk)
Active again, medium > and 3 options ( High risk, moderate risk and low risk)
Again Active, Low > and 3 options ( High risk, moderate risk and low risk)

and same set above for inactive
InActive this will have > High > under that 3 options ( High risk, moderate risk and low risk)
InActive again, medium > and 3 options ( High risk, moderate risk and low risk)
InAgain Active, Low > and 3 options ( High risk, moderate risk and low risk)

Regards
Arvind
 

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Now?
Expanded.xlsx
ABCDEFG
1StatusPriorityRiskCountryProduct CodeSub product CodeCommission%
2ActiveHighHigh RiskUSA4820770890.2138
3ActiveHighHigh RiskUSA4881862060.216
4ActiveHighHigh RiskUSA4881869610.2232
5ActiveHighHigh RiskUSA4881864880.2633
6ActiveHighHigh RiskUSA4818826720.271
7ActiveHighHigh RiskUSA3881864880.2818
8ActiveHighModerate RiskUSA4820770890.2138
9ActiveHighModerate RiskUSA4881862060.216
10ActiveHighModerate RiskUSA4881869610.2232
11ActiveHighModerate RiskUSA4881864880.2633
12ActiveHighModerate RiskUSA4818826720.271
13ActiveHighModerate RiskUSA3881864880.2818
14ActiveHighLow RiskUSA4820770890.2138
15ActiveHighLow RiskUSA4881862060.216
16ActiveHighLow RiskUSA4881869610.2232
17ActiveHighLow RiskUSA4881864880.2633
18ActiveHighLow RiskUSA4818826720.271
19ActiveHighLow RiskUSA3881864880.2818
20ActiveMedium High RiskUSA4820770890.2138
21ActiveMedium High RiskUSA4881862060.216
22ActiveMedium High RiskUSA4881869610.2232
23ActiveMedium High RiskUSA4881864880.2633
24ActiveMedium High RiskUSA4818826720.271
25ActiveMedium High RiskUSA3881864880.2818
26ActiveMedium Moderate RiskUSA4820770890.2138
27ActiveMedium Moderate RiskUSA4881862060.216
28ActiveMedium Moderate RiskUSA4881869610.2232
29ActiveMedium Moderate RiskUSA4881864880.2633
30ActiveMedium Moderate RiskUSA4818826720.271
31ActiveMedium Moderate RiskUSA3881864880.2818
32ActiveMedium Low RiskUSA4820770890.2138
33ActiveMedium Low RiskUSA4881862060.216
34ActiveMedium Low RiskUSA4881869610.2232
35ActiveMedium Low RiskUSA4881864880.2633
36ActiveMedium Low RiskUSA4818826720.271
37ActiveMedium Low RiskUSA3881864880.2818
38ActiveLowHigh RiskUSA4820770890.2138
39ActiveLowHigh RiskUSA4881862060.216
40ActiveLowHigh RiskUSA4881869610.2232
41ActiveLowHigh RiskUSA4881864880.2633
42ActiveLowHigh RiskUSA4818826720.271
43ActiveLowHigh RiskUSA3881864880.2818
44ActiveLowModerate RiskUSA4820770890.2138
45ActiveLowModerate RiskUSA4881862060.216
46ActiveLowModerate RiskUSA4881869610.2232
47ActiveLowModerate RiskUSA4881864880.2633
48ActiveLowModerate RiskUSA4818826720.271
49ActiveLowModerate RiskUSA3881864880.2818
50ActiveLowLow RiskUSA4820770890.2138
51ActiveLowLow RiskUSA4881862060.216
52ActiveLowLow RiskUSA4881869610.2232
53ActiveLowLow RiskUSA4881864880.2633
54ActiveLowLow RiskUSA4818826720.271
55ActiveLowLow RiskUSA3881864880.2818
56InactiveHighHigh RiskUSA4820770890.2138
57InactiveHighHigh RiskUSA4881862060.216
58InactiveHighHigh RiskUSA4881869610.2232
59InactiveHighHigh RiskUSA4881864880.2633
60InactiveHighHigh RiskUSA4818826720.271
61InactiveHighHigh RiskUSA3881864880.2818
62InactiveHighModerate RiskUSA4820770890.2138
63InactiveHighModerate RiskUSA4881862060.216
64InactiveHighModerate RiskUSA4881869610.2232
65InactiveHighModerate RiskUSA4881864880.2633
66InactiveHighModerate RiskUSA4818826720.271
67InactiveHighModerate RiskUSA3881864880.2818
68InactiveHighLow RiskUSA4820770890.2138
69InactiveHighLow RiskUSA4881862060.216
70InactiveHighLow RiskUSA4881869610.2232
71InactiveHighLow RiskUSA4881864880.2633
72InactiveHighLow RiskUSA4818826720.271
73InactiveHighLow RiskUSA3881864880.2818
74InactiveMedium High RiskUSA4820770890.2138
75InactiveMedium High RiskUSA4881862060.216
76InactiveMedium High RiskUSA4881869610.2232
77InactiveMedium High RiskUSA4881864880.2633
78InactiveMedium High RiskUSA4818826720.271
79InactiveMedium High RiskUSA3881864880.2818
80InactiveMedium Moderate RiskUSA4820770890.2138
81InactiveMedium Moderate RiskUSA4881862060.216
82InactiveMedium Moderate RiskUSA4881869610.2232
83InactiveMedium Moderate RiskUSA4881864880.2633
84InactiveMedium Moderate RiskUSA4818826720.271
85InactiveMedium Moderate RiskUSA3881864880.2818
86InactiveMedium Low RiskUSA4820770890.2138
87InactiveMedium Low RiskUSA4881862060.216
88InactiveMedium Low RiskUSA4881869610.2232
89InactiveMedium Low RiskUSA4881864880.2633
90InactiveMedium Low RiskUSA4818826720.271
91InactiveMedium Low RiskUSA3881864880.2818
92InactiveLowHigh RiskUSA4820770890.2138
93InactiveLowHigh RiskUSA4881862060.216
94InactiveLowHigh RiskUSA4881869610.2232
95InactiveLowHigh RiskUSA4881864880.2633
96InactiveLowHigh RiskUSA4818826720.271
97InactiveLowHigh RiskUSA3881864880.2818
98InactiveLowModerate RiskUSA4820770890.2138
99InactiveLowModerate RiskUSA4881862060.216
100InactiveLowModerate RiskUSA4881869610.2232
101InactiveLowModerate RiskUSA4881864880.2633
102InactiveLowModerate RiskUSA4818826720.271
103InactiveLowModerate RiskUSA3881864880.2818
104InactiveLowLow RiskUSA4820770890.2138
105InactiveLowLow RiskUSA4881862060.216
106InactiveLowLow RiskUSA4881869610.2232
107InactiveLowLow RiskUSA4881864880.2633
108InactiveLowLow RiskUSA4818826720.271
109InactiveLowLow RiskUSA3881864880.2818
Expanded data


File link in prev post is updated. so download that 1 if this is the solution.
 

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
666
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thanks Rado, this works and thats the output that I am expecting, I will explore this for my learning.

Do you think we can have VBA solution to this? considering I don't know power query and wont able to explain the logic to anyone :(

Regards
Arvind
 

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
im no good with VBA.

The logicc is simple:

Thois is ur base Table you provided:
Expanded.xlsx
ABC
1StatusPriorityRisk
2ActiveHighHigh Risk
3InactiveMedium Moderate Risk
4LowLow Risk
Sheet2


now what I did is referenced that exact table 3 times:
1 for Status
1 for priority
1 for risk.

from Status i deletad all other columns
from priority I deleted all other columns
from risk I deleted all other columns

now we need to create our all mash up values.
so I made new blank query and I referenced Status,
now I'm simple adding a column to which I am inserting all values from priority and expanding it - this results in all combinations of Status and priority.
now I'm adding 3rd column and I am referencing Risk and expanding it receiving all mash up combo of all 3 (status, Priority, Risk)
Lastly I add a column and I am inserting full Data table. This data is visible in each row, meaning that if I expand it it'll have all the above combinations.

It also should autoupdate since its referenced and not duplicated.

Duplicate is a copy and duplicates do not affect each other
Reference on the other hand is dependable. meaning each change in base table will affect the referenced query. Thats why if you would change High to just H it'll auto change.

and to refresh a query from excel sheet press alt+ctrl+f5
 

Forum statistics

Threads
1,141,410
Messages
5,706,293
Members
421,439
Latest member
JordsdoExcel

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