Converting the Excel rate sheet with one-line per title and one-cell per comma-separated list of prefixes into a worksheet with one-line per prefix

Emin Gabrielyan

Switzernet

2010-08-04

Below is an example of a rate sheet of a vendor, where only one line is provided per destination name. The comma separated list of prefixes representing each destination is provided in a text string within a singe cell.

[xls]

We cannot upload the rates in such a format into the billing system requiring one line per prefix.

Below we present a procedure for creating another worksheet containing the rates in the billing compatible format, i.e. one row per prefix.

Extending one-row per title into one-row per prefix

Create a copy of the Excel file of the Vendor. In the new file, rename the worksheet of rates ‘International Destinations’ by ‘i’.

You can also delete the worksheet of national destinations, as its content is short and can be uploaded manually.

Insert a new worksheet in the file:

In the new worksheet, select the cell A1, and copy past the following two lines. These two lines represent tab-separated Excel formulas which must fulfill the A1:H2 area in your new Excel sheet. Open the text file from the joined ZIP file, using notepad only in order to not be affected by artifacts, such as replacements of tabulation symbols by a browser [zip].

After insertion of the formulas from the text file, your Excel worksheet must look as follows. It shows data obtained from ‘i’ worksheet.

In Cell A1 you must change the row number (which is here set to 80), by the row number of the first rate appearing in the worksheet ‘i’.

Return to the new worksheet, select the column F, and in the cell format pane indicate that the values must be displayed as dates:

Select the cell A2, and Freeze Panes:

Select the entire worksheet, press on Ctrl-1, and set the vertical alignment to center, and select text wrapping for all cells.

Select the 2^nd row (area A2:H2) in your worksheet with formulas, and copy the formulas down by dragging down the right bottom corner of the selected area (highlighted by a red circle below). Rearrange the column widths and row heights for a better visibility.

Select the first three columns and set their font color to light gray:

You now need to drag down the formulas further down, until the last destination with its last prefix is shown on the new Excel worksheet. After the last prefix of the last destination is covered, if you continue dragging down the formulas, the next rows in your new Excel sheet will show empty cells in the fields of the destination name and of the prefix.

[xls]

In the above screenshot you can ignore that the rate sheet with formulas is renamed into ‘out’ and also that the filename is changed.

Now it remains you to copy the values of the obtained data into a new Excel sheet. In this example the concerned area is D1:H10440.

Select in the new Excel file the cell A1, right click, and choose Values in the Paste Special pane of the pop-up menu:

You will need also to specify that the values of the column C must be displayed as dates:

[xls]

The obtained file is ready for uploading. However, you may need to change the uploading template or create a new one if the column positions are not the same.

Self references:

http://switzernet.com/2/public/100804-all-in-cell-to-prefix-per-row/

http://switzernet.com/2/support/100804-all-in-cell-to-prefix-per-row/

http://unappel.ch/2/public/100804-all-in-cell-to-prefix-per-row/

http://unappel.ch/2/support/100804-all-in-cell-to-prefix-per-row/

* * *