Create Duplicate records based on possible combinations of other values

[aravindhan_31]

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
	A	B	C	D
2	Country	Product Code	Sub product Code	Commission%
3	USA	4	82077089	21%
4	USA	4	88186206	22%
5	USA	4	88186961	22%
6	USA	4	88186488	26%
7	USA	4	81882672	27%
8	USA	3	88186488	28%
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
	H	I	J
2	Status	Priority	Risk
3	Active	High	High Risk
4	Inactive	Medium	Moderate Risk
5		Low	Low 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
	A	B	C	D	E	F	G
11	Country	Product Code	Sub product Code	Commission%	Status	Priority	Risk
12	USA	4	82077089	21%	Active	High	High Risk
13	USA	4	88186206	22%	Active	High	High Risk
14	USA	4	88186961	22%	Active	High	High Risk
15	USA	4	88186488	26%	Active	High	High Risk
16	USA	4	81882672	27%	Active	High	High Risk
17	USA	3	88186488	28%	Active	High	High Risk
18	USA	4	82077089	21%	Active	High	Low Risk
19	USA	4	88186206	22%	Active	High	Low Risk
20	USA	4	88186961	22%	Active	High	Low Risk
21	USA	4	88186488	26%	Active	High	Low Risk
22	USA	4	81882672	27%	Active	High	Low Risk
23	USA	3	88186488	28%	Active	High	Low Risk
24	USA	4	82077089	21%	Active	High	Moderate Risk
25	USA	4	88186206	22%	Active	High	Moderate Risk
26	USA	4	88186961	22%	Active	High	Moderate Risk
27	USA	4	88186488	26%	Active	High	Moderate Risk
28	USA	4	81882672	27%	Active	High	Moderate Risk
29	USA	3	88186488	28%	Active	High	Moderate Risk
30	USA	4	82077089	21%	Active	Low	High Risk
31	USA	4	88186206	22%	Active	Low	High Risk
32	USA	4	88186961	22%	Active	Low	High Risk
33	USA	4	88186488	26%	Active	Low	High Risk
34	USA	4	81882672	27%	Active	Low	High Risk
35	USA	3	88186488	28%	Active	Low	High Risk
36	USA	4	82077089	21%	Active	Low	Low Risk
37	USA	4	88186206	22%	Active	Low	Low Risk
38	USA	4	88186961	22%	Active	Low	Low Risk
39	USA	4	88186488	26%	Active	Low	Low Risk
40	USA	4	81882672	27%	Active	Low	Low Risk
41	USA	3	88186488	28%	Active	Low	Low Risk
42	USA	4	82077089	21%	Active	Low	Moderate Risk
43	USA	4	88186206	22%	Active	Low	Moderate Risk
44	USA	4	88186961	22%	Active	Low	Moderate Risk
45	USA	4	88186488	26%	Active	Low	Moderate Risk
46	USA	4	81882672	27%	Active	Low	Moderate Risk
47	USA	3	88186488	28%	Active	Low	Moderate Risk
48	USA	4	82077089	21%	Active	Medium	High Risk
49	USA	4	88186206	22%	Active	Medium	High Risk
50	USA	4	88186961	22%	Active	Medium	High Risk
51	USA	4	88186488	26%	Active	Medium	High Risk
52	USA	4	81882672	27%	Active	Medium	High Risk
53	USA	3	88186488	28%	Active	Medium	High Risk
54	USA	4	82077089	21%	Active	Medium	Low Risk
55	USA	4	88186206	22%	Active	Medium	Low Risk
56	USA	4	88186961	22%	Active	Medium	Low Risk
57	USA	4	88186488	26%	Active	Medium	Low Risk
58	USA	4	81882672	27%	Active	Medium	Low Risk
59	USA	3	88186488	28%	Active	Medium	Low Risk
60	USA	4	82077089	21%	Active	Medium	Moderate Risk
61	USA	4	88186206	22%	Active	Medium	Moderate Risk
62	USA	4	88186961	22%	Active	Medium	Moderate Risk
63	USA	4	88186488	26%	Active	Medium	Moderate Risk
64	USA	4	81882672	27%	Active	Medium	Moderate Risk
65	USA	3	88186488	28%	Active	Medium	Moderate Risk
66	USA	4	82077089	21%	Inactive	High	High Risk
67	USA	4	88186206	22%	Inactive	High	High Risk
68	USA	4	88186961	22%	Inactive	High	High Risk
69	USA	4	88186488	26%	Inactive	High	High Risk
70	USA	4	81882672	27%	Inactive	High	High Risk
71	USA	3	88186488	28%	Inactive	High	High Risk
72	USA	4	82077089	21%	Inactive	High	Low Risk
73	USA	4	88186206	22%	Inactive	High	Low Risk
74	USA	4	88186961	22%	Inactive	High	Low Risk
75	USA	4	88186488	26%	Inactive	High	Low Risk
76	USA	4	81882672	27%	Inactive	High	Low Risk
77	USA	3	88186488	28%	Inactive	High	Low Risk
78	USA	4	82077089	21%	Inactive	High	Moderate Risk
79	USA	4	88186206	22%	Inactive	High	Moderate Risk
80	USA	4	88186961	22%	Inactive	High	Moderate Risk
81	USA	4	88186488	26%	Inactive	High	Moderate Risk
82	USA	4	81882672	27%	Inactive	High	Moderate Risk
83	USA	3	88186488	28%	Inactive	High	Moderate Risk
84	USA	4	82077089	21%	Inactive	Low	High Risk
85	USA	4	88186206	22%	Inactive	Low	High Risk
86	USA	4	88186961	22%	Inactive	Low	High Risk
87	USA	4	88186488	26%	Inactive	Low	High Risk
88	USA	4	81882672	27%	Inactive	Low	High Risk
89	USA	3	88186488	28%	Inactive	Low	High Risk
90	USA	4	82077089	21%	Inactive	Low	Low Risk
91	USA	4	88186206	22%	Inactive	Low	Low Risk
92	USA	4	88186961	22%	Inactive	Low	Low Risk
93	USA	4	88186488	26%	Inactive	Low	Low Risk
94	USA	4	81882672	27%	Inactive	Low	Low Risk
95	USA	3	88186488	28%	Inactive	Low	Low Risk
96	USA	4	82077089	21%	Inactive	Low	Moderate Risk
97	USA	4	88186206	22%	Inactive	Low	Moderate Risk
98	USA	4	88186961	22%	Inactive	Low	Moderate Risk
99	USA	4	88186488	26%	Inactive	Low	Moderate Risk
100	USA	4	81882672	27%	Inactive	Low	Moderate Risk
101	USA	3	88186488	28%	Inactive	Low	Moderate Risk
102	USA	4	82077089	21%	Inactive	Medium	High Risk
103	USA	4	88186206	22%	Inactive	Medium	High Risk
104	USA	4	88186961	22%	Inactive	Medium	High Risk
105	USA	4	88186488	26%	Inactive	Medium	High Risk
106	USA	4	81882672	27%	Inactive	Medium	High Risk
107	USA	3	88186488	28%	Inactive	Medium	High Risk
108	USA	4	82077089	21%	Inactive	Medium	Low Risk
109	USA	4	88186206	22%	Inactive	Medium	Low Risk
110	USA	4	88186961	22%	Inactive	Medium	Low Risk
111	USA	4	88186488	26%	Inactive	Medium	Low Risk
112	USA	4	81882672	27%	Inactive	Medium	Low Risk
113	USA	3	88186488	28%	Inactive	Medium	Low Risk
114	USA	4	82077089	21%	Inactive	Medium	Moderate Risk
115	USA	4	88186206	22%	Inactive	Medium	Moderate Risk
116	USA	4	88186961	22%	Inactive	Medium	Moderate Risk
117	USA	4	88186488	26%	Inactive	Medium	Moderate Risk
118	USA	4	81882672	27%	Inactive	Medium	Moderate Risk
119	USA	3	88186488	28%	Inactive	Medium	Moderate Risk
Sheet1

attached the file for reference as well.

Thanks for your help

Regards
Arvind

 

[Radoslaw Poprawski]

is this the expected result?

Book1
	A	B	C	D	E	F	G	H
1	Status	Priority	TThis can be removed if you wish it	Custom.Risk	Country	Product Code	Sub product Code	Commission%
2	Active	High	High Risk	High Risk	USA	4	82077089	0.2138
3	Active	High	High Risk	High Risk	USA	4	88186206	0.216
4	Active	High	High Risk	High Risk	USA	4	88186961	0.2232
5	Active	High	High Risk	High Risk	USA	4	88186488	0.2633
6	Active	High	High Risk	High Risk	USA	4	81882672	0.271
7	Active	High	High Risk	High Risk	USA	3	88186488	0.2818
8	Active	High	High Risk	Moderate Risk	USA	4	82077089	0.2138
9	Active	High	High Risk	Moderate Risk	USA	4	88186206	0.216
10	Active	High	High Risk	Moderate Risk	USA	4	88186961	0.2232
11	Active	High	High Risk	Moderate Risk	USA	4	88186488	0.2633
12	Active	High	High Risk	Moderate Risk	USA	4	81882672	0.271
13	Active	High	High Risk	Moderate Risk	USA	3	88186488	0.2818
14	Active	High	High Risk	Low Risk	USA	4	82077089	0.2138
15	Active	High	High Risk	Low Risk	USA	4	88186206	0.216
16	Active	High	High Risk	Low Risk	USA	4	88186961	0.2232
17	Active	High	High Risk	Low Risk	USA	4	88186488	0.2633
18	Active	High	High Risk	Low Risk	USA	4	81882672	0.271
19	Active	High	High Risk	Low Risk	USA	3	88186488	0.2818
20	Inactive	Medium	Moderate Risk	High Risk	USA	4	82077089	0.2138
21	Inactive	Medium	Moderate Risk	High Risk	USA	4	88186206	0.216
22	Inactive	Medium	Moderate Risk	High Risk	USA	4	88186961	0.2232
23	Inactive	Medium	Moderate Risk	High Risk	USA	4	88186488	0.2633
24	Inactive	Medium	Moderate Risk	High Risk	USA	4	81882672	0.271
25	Inactive	Medium	Moderate Risk	High Risk	USA	3	88186488	0.2818
26	Inactive	Medium	Moderate Risk	Moderate Risk	USA	4	82077089	0.2138
27	Inactive	Medium	Moderate Risk	Moderate Risk	USA	4	88186206	0.216
28	Inactive	Medium	Moderate Risk	Moderate Risk	USA	4	88186961	0.2232
29	Inactive	Medium	Moderate Risk	Moderate Risk	USA	4	88186488	0.2633
30	Inactive	Medium	Moderate Risk	Moderate Risk	USA	4	81882672	0.271
31	Inactive	Medium	Moderate Risk	Moderate Risk	USA	3	88186488	0.2818
32	Inactive	Medium	Moderate Risk	Low Risk	USA	4	82077089	0.2138
33	Inactive	Medium	Moderate Risk	Low Risk	USA	4	88186206	0.216
34	Inactive	Medium	Moderate Risk	Low Risk	USA	4	88186961	0.2232
35	Inactive	Medium	Moderate Risk	Low Risk	USA	4	88186488	0.2633
36	Inactive	Medium	Moderate Risk	Low Risk	USA	4	81882672	0.271
37	Inactive	Medium	Moderate Risk	Low Risk	USA	3	88186488	0.2818
38		Low	Low Risk	High Risk	USA	4	82077089	0.2138
39		Low	Low Risk	High Risk	USA	4	88186206	0.216
40		Low	Low Risk	High Risk	USA	4	88186961	0.2232
41		Low	Low Risk	High Risk	USA	4	88186488	0.2633
42		Low	Low Risk	High Risk	USA	4	81882672	0.271
43		Low	Low Risk	High Risk	USA	3	88186488	0.2818
44		Low	Low Risk	Moderate Risk	USA	4	82077089	0.2138
45		Low	Low Risk	Moderate Risk	USA	4	88186206	0.216
46		Low	Low Risk	Moderate Risk	USA	4	88186961	0.2232
47		Low	Low Risk	Moderate Risk	USA	4	88186488	0.2633
48		Low	Low Risk	Moderate Risk	USA	4	81882672	0.271
49		Low	Low Risk	Moderate Risk	USA	3	88186488	0.2818
50		Low	Low Risk	Low Risk	USA	4	82077089	0.2138
51		Low	Low Risk	Low Risk	USA	4	88186206	0.216
52		Low	Low Risk	Low Risk	USA	4	88186961	0.2232
53		Low	Low Risk	Low Risk	USA	4	88186488	0.2633
54		Low	Low Risk	Low Risk	USA	4	81882672	0.271
55		Low	Low Risk	Low Risk	USA	3	88186488	0.2818
For Result 2 to expand

here is the link to file:

Expanded.xlsx

Microsoft Excel Workbook

1drv.ms

This was done in PQ, which is simpler.

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

 

[aravindhan_31]

Cross post: Create Duplicate records based on possible combinations of other values

 

[aravindhan_31]

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]

Now?

Expanded.xlsx
	A	B	C	D	E	F	G
1	Status	Priority	Risk	Country	Product Code	Sub product Code	Commission%
2	Active	High	High Risk	USA	4	82077089	0.2138
3	Active	High	High Risk	USA	4	88186206	0.216
4	Active	High	High Risk	USA	4	88186961	0.2232
5	Active	High	High Risk	USA	4	88186488	0.2633
6	Active	High	High Risk	USA	4	81882672	0.271
7	Active	High	High Risk	USA	3	88186488	0.2818
8	Active	High	Moderate Risk	USA	4	82077089	0.2138
9	Active	High	Moderate Risk	USA	4	88186206	0.216
10	Active	High	Moderate Risk	USA	4	88186961	0.2232
11	Active	High	Moderate Risk	USA	4	88186488	0.2633
12	Active	High	Moderate Risk	USA	4	81882672	0.271
13	Active	High	Moderate Risk	USA	3	88186488	0.2818
14	Active	High	Low Risk	USA	4	82077089	0.2138
15	Active	High	Low Risk	USA	4	88186206	0.216
16	Active	High	Low Risk	USA	4	88186961	0.2232
17	Active	High	Low Risk	USA	4	88186488	0.2633
18	Active	High	Low Risk	USA	4	81882672	0.271
19	Active	High	Low Risk	USA	3	88186488	0.2818
20	Active	Medium	High Risk	USA	4	82077089	0.2138
21	Active	Medium	High Risk	USA	4	88186206	0.216
22	Active	Medium	High Risk	USA	4	88186961	0.2232
23	Active	Medium	High Risk	USA	4	88186488	0.2633
24	Active	Medium	High Risk	USA	4	81882672	0.271
25	Active	Medium	High Risk	USA	3	88186488	0.2818
26	Active	Medium	Moderate Risk	USA	4	82077089	0.2138
27	Active	Medium	Moderate Risk	USA	4	88186206	0.216
28	Active	Medium	Moderate Risk	USA	4	88186961	0.2232
29	Active	Medium	Moderate Risk	USA	4	88186488	0.2633
30	Active	Medium	Moderate Risk	USA	4	81882672	0.271
31	Active	Medium	Moderate Risk	USA	3	88186488	0.2818
32	Active	Medium	Low Risk	USA	4	82077089	0.2138
33	Active	Medium	Low Risk	USA	4	88186206	0.216
34	Active	Medium	Low Risk	USA	4	88186961	0.2232
35	Active	Medium	Low Risk	USA	4	88186488	0.2633
36	Active	Medium	Low Risk	USA	4	81882672	0.271
37	Active	Medium	Low Risk	USA	3	88186488	0.2818
38	Active	Low	High Risk	USA	4	82077089	0.2138
39	Active	Low	High Risk	USA	4	88186206	0.216
40	Active	Low	High Risk	USA	4	88186961	0.2232
41	Active	Low	High Risk	USA	4	88186488	0.2633
42	Active	Low	High Risk	USA	4	81882672	0.271
43	Active	Low	High Risk	USA	3	88186488	0.2818
44	Active	Low	Moderate Risk	USA	4	82077089	0.2138
45	Active	Low	Moderate Risk	USA	4	88186206	0.216
46	Active	Low	Moderate Risk	USA	4	88186961	0.2232
47	Active	Low	Moderate Risk	USA	4	88186488	0.2633
48	Active	Low	Moderate Risk	USA	4	81882672	0.271
49	Active	Low	Moderate Risk	USA	3	88186488	0.2818
50	Active	Low	Low Risk	USA	4	82077089	0.2138
51	Active	Low	Low Risk	USA	4	88186206	0.216
52	Active	Low	Low Risk	USA	4	88186961	0.2232
53	Active	Low	Low Risk	USA	4	88186488	0.2633
54	Active	Low	Low Risk	USA	4	81882672	0.271
55	Active	Low	Low Risk	USA	3	88186488	0.2818
56	Inactive	High	High Risk	USA	4	82077089	0.2138
57	Inactive	High	High Risk	USA	4	88186206	0.216
58	Inactive	High	High Risk	USA	4	88186961	0.2232
59	Inactive	High	High Risk	USA	4	88186488	0.2633
60	Inactive	High	High Risk	USA	4	81882672	0.271
61	Inactive	High	High Risk	USA	3	88186488	0.2818
62	Inactive	High	Moderate Risk	USA	4	82077089	0.2138
63	Inactive	High	Moderate Risk	USA	4	88186206	0.216
64	Inactive	High	Moderate Risk	USA	4	88186961	0.2232
65	Inactive	High	Moderate Risk	USA	4	88186488	0.2633
66	Inactive	High	Moderate Risk	USA	4	81882672	0.271
67	Inactive	High	Moderate Risk	USA	3	88186488	0.2818
68	Inactive	High	Low Risk	USA	4	82077089	0.2138
69	Inactive	High	Low Risk	USA	4	88186206	0.216
70	Inactive	High	Low Risk	USA	4	88186961	0.2232
71	Inactive	High	Low Risk	USA	4	88186488	0.2633
72	Inactive	High	Low Risk	USA	4	81882672	0.271
73	Inactive	High	Low Risk	USA	3	88186488	0.2818
74	Inactive	Medium	High Risk	USA	4	82077089	0.2138
75	Inactive	Medium	High Risk	USA	4	88186206	0.216
76	Inactive	Medium	High Risk	USA	4	88186961	0.2232
77	Inactive	Medium	High Risk	USA	4	88186488	0.2633
78	Inactive	Medium	High Risk	USA	4	81882672	0.271
79	Inactive	Medium	High Risk	USA	3	88186488	0.2818
80	Inactive	Medium	Moderate Risk	USA	4	82077089	0.2138
81	Inactive	Medium	Moderate Risk	USA	4	88186206	0.216
82	Inactive	Medium	Moderate Risk	USA	4	88186961	0.2232
83	Inactive	Medium	Moderate Risk	USA	4	88186488	0.2633
84	Inactive	Medium	Moderate Risk	USA	4	81882672	0.271
85	Inactive	Medium	Moderate Risk	USA	3	88186488	0.2818
86	Inactive	Medium	Low Risk	USA	4	82077089	0.2138
87	Inactive	Medium	Low Risk	USA	4	88186206	0.216
88	Inactive	Medium	Low Risk	USA	4	88186961	0.2232
89	Inactive	Medium	Low Risk	USA	4	88186488	0.2633
90	Inactive	Medium	Low Risk	USA	4	81882672	0.271
91	Inactive	Medium	Low Risk	USA	3	88186488	0.2818
92	Inactive	Low	High Risk	USA	4	82077089	0.2138
93	Inactive	Low	High Risk	USA	4	88186206	0.216
94	Inactive	Low	High Risk	USA	4	88186961	0.2232
95	Inactive	Low	High Risk	USA	4	88186488	0.2633
96	Inactive	Low	High Risk	USA	4	81882672	0.271
97	Inactive	Low	High Risk	USA	3	88186488	0.2818
98	Inactive	Low	Moderate Risk	USA	4	82077089	0.2138
99	Inactive	Low	Moderate Risk	USA	4	88186206	0.216
100	Inactive	Low	Moderate Risk	USA	4	88186961	0.2232
101	Inactive	Low	Moderate Risk	USA	4	88186488	0.2633
102	Inactive	Low	Moderate Risk	USA	4	81882672	0.271
103	Inactive	Low	Moderate Risk	USA	3	88186488	0.2818
104	Inactive	Low	Low Risk	USA	4	82077089	0.2138
105	Inactive	Low	Low Risk	USA	4	88186206	0.216
106	Inactive	Low	Low Risk	USA	4	88186961	0.2232
107	Inactive	Low	Low Risk	USA	4	88186488	0.2633
108	Inactive	Low	Low Risk	USA	4	81882672	0.271
109	Inactive	Low	Low Risk	USA	3	88186488	0.2818
Expanded data

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

 

[aravindhan_31]

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]

im no good with VBA.

The logicc is simple:

Thois is ur base Table you provided:

Expanded.xlsx
	A	B	C
1	Status	Priority	Risk
2	Active	High	High Risk
3	Inactive	Medium	Moderate Risk
4		Low	Low 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

 

[kvsrinivasamurthy]

VBA solution given in here