replace the value from other worksheet and put value of one row inseparate rows

[myshak]

Hi,

We are looking for macro or formula to get the final spreadsheet.
Any help will be greatly appreciated.


We have to spreadsheets .


SPREADSHHET 1

COL1 COL2
7401 bemasotawtop
101050-1 101054b 101050
ae176yellow ae176love ae176sofa ae176chair
1 101050


SPREADSHEET 2

col1 col2
7401 wILLIAM ANDY
101 hI ARE YOU
101050-1 Mike candy
ae176yellow Any
1 hello



We want to make like following spreadsheet. Match the col1 of both
spreadsheets and replace the value of col1 with col2 of 2nd
spreadsheet. And put col2 of first spreadsheet in separate row like
following.



Final Spread Sheet
Col1 col2
William Andy bemasotawtop
Mike Candy 101054b
Mike Candy 101050
Andy ae176love
Andy ae176sofa
Andy ae176chair
hello 101050


If col2 have same row merge the related col1 with @.

Final spreadsheet

Col1 col2
William Andy bemasotawtop
Mike Candy 101054b
Mike Candy@hello 101050
Andy ae176love
Andy ae176sofa
Andy ae176chair

 

[myshak]

I got one step done.

Now i want to break row into several rows like following.


Available spreadsheet


COL1 COL2
wILLIAM ANDY bemasotawtop
Mike candy 101054b 101050
Andy ae176love ae176sofa ae176chair
hello 101050



We want to make like following

Final Spread Sheet
Col1 col2
William Andy bemasotawtop
Mike Candy 101054b
Mike Candy 101050
Andy ae176love
Andy ae176sofa
Andy ae176chair
hello 101050



Final spreadsheet
Col1 col2
William Andy bemasotawtop
Mike Candy 101054b
Mike Candy@hello 101050
Andy ae176love
Andy ae176sofa
Andy ae176chair