In the sheet1 i have

Australia - Adelaide 61-88x 0.0102
Australia - Melbourne 61-38,39 0.0102

Country - Area, Country Code, Rate

In the Sheet2 I have

Australia - Adelaide 61-88x 61882,61883,61884,61881

Australia - Melbourne 61-38,39 6138,6139

I need all the codes in one tab to each row Please let me know if anyone
have solution for this in Excel. It should look Like below.

Australia - Adelaide 61882 0.0102
Australia - Adelaide 61883 0.0102
Australia - Adelaide 61884 0.0102
Australia - Adelaide 61881 0.0102
Australia - Melbourne 6138 0.0102
Australia - Melbourne 6139 0.0102

Please help me if any one have solution

Start by copying the numbers column on sheet2 to a new sheet (Sheet3)
and separate out using: Data Text to Column Delimited Comma
Finish.

Then fill down the following formulas on another sheet to get the
table:

A1: =LOOKUP(99,SEARCH(B1,Nos)/(Nos<""),Country_Area)
B1: =SMALL(Sheet3!$1:$65536,ROW())
C1: =VLOOKUP(D1,Sheet1!B:C,2,0)
D1: =LOOKUP(99,SEARCH(B1,Nos)/(Nos<""),Code)

where Country_Area, Code and Nos refer to the data in sheet 2.
(You can select the table on sheet 2 and use Insert Names Create
Top Row)

Kishore wrote:

In the sheet1 i have

Australia - Adelaide 61-88x 0.0102
Australia - Melbourne 61-38,39 0.0102

Country - Area, Country Code, Rate

In the Sheet2 I have

Australia - Adelaide 61-88x 61882,61883,61884,61881

Australia - Melbourne 61-38,39 6138,6139

I need all the codes in one tab to each row Please let me know if anyone
have solution for this in Excel. It should look Like below.

Australia - Adelaide 61882 0.0102
Australia - Adelaide 61883 0.0102
Australia - Adelaide 61884 0.0102
Australia - Adelaide 61881 0.0102
Australia - Melbourne 6138 0.0102
Australia - Melbourne 6139 0.0102

Please help me if any one have solution