Area Codes
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
|