Hello all,
Is there code or some trick that can convert excel data that has various amounts of columns into more of a "database" look. Below is what the current data looks like:
<table border="0" cellpadding="0" cellspacing="0" width="919"><col style="width: 89pt;" width="119"> <col style="width: 75pt;" width="100"> <col style="width: 69pt;" width="92"> <col style="width: 66pt;" width="88"> <col style="width: 57pt;" span="3" width="76"> <col style="width: 55pt;" span="4" width="73"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 89pt;" height="20" width="119">Destination</td> <td class="xl69" style="border-left: medium none; width: 75pt;" width="100">Carrier</td> <td class="xl70" style="border-left: medium none; width: 69pt;" width="92">Country Code</td> <td class="xl70" style="border-left: medium none; width: 66pt;" width="88">PRICE</td> <td class="xl71" style="border-left: medium none; width: 57pt;" width="76">City Code 1</td> <td class="xl71" style="border-left: medium none; width: 57pt;" width="76">City Code 2</td> <td class="xl71" style="border-left: medium none; width: 57pt;" width="76">City Code 3</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 4</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 5</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 6</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">E</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">F</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">R</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">T</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">L</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">M</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">N</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">O</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
I'm trying to get it too look like:
<table border="0" cellpadding="0" cellspacing="0" width="475"><col style="width: 89pt;" width="119"> <col style="width: 75pt;" width="100"> <col style="width: 69pt;" width="92"> <col style="width: 66pt;" width="88"> <col style="width: 57pt;" width="76"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 89pt;" height="20" width="119">Destination</td> <td class="xl68" style="border-left: medium none; width: 75pt;" width="100">Carrier</td> <td class="xl69" style="border-left: medium none; width: 69pt;" width="92">Country Code</td> <td class="xl69" style="border-left: medium none; width: 66pt;" width="88">City Code</td> <td class="xl69" style="border-left: medium none; width: 57pt;" width="76">Price</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">E</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">F</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">G</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">R</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">T</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">L</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">M</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">N</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">O</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> </tbody></table>
There are over 200+ countries and multiple carriers per country so copy and pasting values (transpose) isn't the best option. Thank you for your help.
Is there code or some trick that can convert excel data that has various amounts of columns into more of a "database" look. Below is what the current data looks like:
<table border="0" cellpadding="0" cellspacing="0" width="919"><col style="width: 89pt;" width="119"> <col style="width: 75pt;" width="100"> <col style="width: 69pt;" width="92"> <col style="width: 66pt;" width="88"> <col style="width: 57pt;" span="3" width="76"> <col style="width: 55pt;" span="4" width="73"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 89pt;" height="20" width="119">Destination</td> <td class="xl69" style="border-left: medium none; width: 75pt;" width="100">Carrier</td> <td class="xl70" style="border-left: medium none; width: 69pt;" width="92">Country Code</td> <td class="xl70" style="border-left: medium none; width: 66pt;" width="88">PRICE</td> <td class="xl71" style="border-left: medium none; width: 57pt;" width="76">City Code 1</td> <td class="xl71" style="border-left: medium none; width: 57pt;" width="76">City Code 2</td> <td class="xl71" style="border-left: medium none; width: 57pt;" width="76">City Code 3</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 4</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 5</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 6</td> <td class="xl71" style="border-left: medium none; width: 55pt;" width="73">City Code 7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">E</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">F</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">R</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">T</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">L</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">M</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">N</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">O</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
I'm trying to get it too look like:
<table border="0" cellpadding="0" cellspacing="0" width="475"><col style="width: 89pt;" width="119"> <col style="width: 75pt;" width="100"> <col style="width: 69pt;" width="92"> <col style="width: 66pt;" width="88"> <col style="width: 57pt;" width="76"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 89pt;" height="20" width="119">Destination</td> <td class="xl68" style="border-left: medium none; width: 75pt;" width="100">Carrier</td> <td class="xl69" style="border-left: medium none; width: 69pt;" width="92">Country Code</td> <td class="xl69" style="border-left: medium none; width: 66pt;" width="88">City Code</td> <td class="xl69" style="border-left: medium none; width: 57pt;" width="76">Price</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">E</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">F</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Orange Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">G</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">R</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">T-Mobile</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">T</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.03 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">L</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">M</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">N</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">UNITED KINGDOM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">O2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">O</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 0.02 </td> </tr> </tbody></table>
There are over 200+ countries and multiple carriers per country so copy and pasting values (transpose) isn't the best option. Thank you for your help.