Create Duplicate records based on possible combinations of other values

[aravindhan_31]

Cross post:
https://www.mrexcel.com/board/thread...alues.1174658/

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, based on the combinations which are there on H to J, I want to create the output in sheet 2.
create duplicates.PNG

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.

I have attached the file for reference.

Regards
Arvind

[Kaper]

Note that on mrexcel there is already elegant solution with PQ

[aravindhan_31]

Hi Kaper,

Power query is good, but I would request a VBA solution as I don't know power query and would be difficult for me to explain the logic to my team.

I will continue to explore PQ, but needed macro

Need some help here..

Regards
Arvind

[zhouyiqing]

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Please Login or Register  to view this content.

[aravindhan_31]

Hi Zhou,

apologies for the delay in response, got busy and couldn't respond
This worked, Thanks a lot

Regards
Arvind

[aravindhan_31]

Hello Kv,

Thanks for your help, this one as well worked, I assume you changed I4 and I5 just to get the result in an order, else that will work based on values on those fields.

The data I provided is sample and actual data and values would be different.


Regards
Arvind

[kvsrinivasamurthy]

If problem is solved, pl mark the thread SOLVED.

[aravindhan_31]

Hi All,
I know I have marked this as solved, when i tried with actual data like this its not working, may be due to the number of rows in col H were 2 and now its 7, these ranges might change, hence wanted to keep it dynamic


Dupl.PNG

Thanks and sorry for the confusion

Regards
Arvind

[kvsrinivasamurthy]

Here is the code for macro.

Maindata=A3:d8
ClmData=H2:K7

Ranges can be changed.
In both the ranges rows can be increased Columns cannot be increased. If columns are to be increased code should be edited.

Please Login or Register  to view this content.