expand list of values with vba

Hi,

i am pretty new with VBA and i would like some assistance to expand the below format with VBA in another sheet:

rep A industry A country A
rep A Industry B country B
rep A Industry C country c


to:

rep A industry A country A
rep A industry B country A
rep A industry C country A
rep A industry A country B
rep A industry B country B
rep A industry C country B
rep A industry A country C
rep A industry B country C
rep A industry C country C

so for each country the rep A needs to cover all industries in column B

let me know if more details are required.

Kind regards,
Dumitru

Try this:

Please Login or Register  to view this content.

works almost ok

the result is not including Rep A (and i would like to have too in sheet 2) it only shows the Industry A for country A B C (which is what i was looking for)
However i noticed something in my file:
for Rep A i have has more industries than countries:

rep A industry A country A
rep A Industry B country B
rep A Industry C country c
rep A Industry D
rep A Industry E

and for this case the result should be like:

rep A industry A country A
rep A industry B country A
rep A industry C country A
rep A industry D country A
rep A industry E country A
rep A industry A country B
rep A industry B country B
rep A industry C country B
rep A industry D country B
rep A industry E country B
rep A industry A country C
rep A industry B country C
rep A industry C country C
rep A industry D country C
rep A industry E country C



This the situation also for the reps following.

One other thing i noticed is that this code does not "stop" for one rep and it took the list of countries from the other reps until the end of the sheet:

from
rep A industry A country A
rep A Industry B country B
rep A Industry C country c
rep B Industry x country z
rep b industry y country q


to

rep A industry A country A
rep A industry A country B
rep A industry A country C
rep A industry A country D
rep A industry A country E
rep A Industry A country Z
rep A industry A country Q
which should not happen

Thanks for the prompt reply.

Kind regards,
Dumitru