This is my first time posting and I am hoping you can help me out with this problem in Excel.

I have a Final sheet which has locations repeated a fixed n number of times (in this case, n=2) and has its own property columns as shown below:

Location | Property3 | Property4 |

X | ||

X | ||

Y | ||

Y | ||

Z | ||

Z | ||

XX | ||

XX |

<tbody>

</tbody>

I would like to duplicate rows for individual locations and its accompanying property 3 and 4 columns as well as add two new property columns based on Master List as shown below:

Location | Property1 | Property2 |

X | a | 1 |

X | a | 2 |

Y | a | 3 |

Y | b | 3 |

Z | a | 4 |

XX | a | 5 |

XX | b | 6 |

<tbody>

</tbody>

Modified Final sheet should look like:

Location | Property1 | Property2 | Property3 | Property4 |

X | a | 1 | ||

X | a | 1 | ||

X | a | 2 | ||

X | a | 2 | ||

Y | a | 3 | ||

Y | a | 3 | ||

Y | b | 3 | ||

Y | b | 3 | ||

Z | a | 4 | ||

Z | a | 4 | ||

XX | a | 5 | ||

XX | a | 5 | ||

XX | b | 6 | ||

XX | b | 6 |

<tbody>

</tbody>

Locations X, Y and XX have been duplicated twice as it has had to capture the unique values in property columns in 1 and 2. Z only once as it has unique Property 1 and 2 values.

Thanks a lot!