Skip to main content

Matching voice usage to Sub-accounts and Locations in the Bandwidth App

To match your voice usage to your Bandwidth App Sub-accounts and Locations, complete the following steps:

  1. Download the Full Phone Numbers Details report (select the CSV format and complete other fields as needed, but please don't customize the date ranges). Rename the document as you’d like and save it as an .xls file.

  2. Download the Voice BDRs - Statement report for your desired statement date. Please note that the statement date aligns with your invoiced month, not the usage period.

  3. In the downloaded Voice BDRs - Statement file, hover over Column E, right-click, and select Insert. In the newly-created cell E2, insert =Right(F2,10)*1.

  4. In the lower-right corner of the cell, highlight until your cursor changes to + and then double-click it to populate the rest of the column. Name this column New CallSource.

  5. Hover over Column G, right-click, and select Insert. In the newly-created cell G2, insert =Right(H2,10)*1.

  6. In the lower-right corner of the cell, highlight until your cursor changes to + and then double-click it to populate the rest of the column. Name this column New CallDestination.

  7. Hover over Column I and select Insert. Then hover over the newly-created Column I and select Insert again. Name the newly-created Column I Sub-Account and Column J Location.

  8. Perform a VLOOKUP in Column I (Sub-Account). You can use the following example formula (please replace 'full-phone-number-details-20200615-164000 subaccountingtest.xls' with the name of your .xls file from step 1):

    =IF(OR(D2="INTRASTATE",D2="INTERSTATE",D2="OUTBOUND TOLL-FREE"),VLOOKUP($E2,'full-phone-number-details-20200615-164000 subaccountingtest.xls'!$A:$R,10,FALSE),VLOOKUP(G2,'full-phone-number-details-20200615-164000 subaccounting test.csv'!$A:$R,10,FALSE))

  9. Once you paste the VLOOKUP formula, hit Enter. This will display a pop-up asking you to select a file – use your Full Phone Number Details report .xls file.

  10. The pop-up will come up one more time – please select the same .xls file again. This will then populate the cell with the Sub-account information.

  11. Perform a VLOOKUP in Column J (Location). Again, you can use the following example formula (please replace 'full-phone-number-details-20200615-164000 subaccountingtest.xls' with the name of your .xls file from step 1):

    =IF(OR(D2="INTRASTATE",D2="INTERSTATE",D2="OUTBOUND TOLL-FREE"),VLOOKUP($E2,'full-phone-number-details-20200615-164000 subaccountingtest.csv'!$A:$R,11,FALSE),VLOOKUP(G2,'full-phone-number-details-20200615-164000 subaccounting test.csv'!$A:$R,11,FALSE))

  12. Once you paste the VLOOKUP formula, hit Enter. This will display a pop-up asking you to select a file – use your Full Phone Number Details report .xls file.

  13. The pop-up will come up one more time – please select the same .xls file again. This will then populate the cell with the Location information.

VLOOKUP tips

  • Make sure to apply the formula to the entire column

  • D2 = Call Type on your Voice BDRs - Statement report

  • E2 = New CallSource Field on your Voice BDRs - Statement report

  • G2 = New CallDestination Field on your Voice BDRs - Statement report

  • Select columns A-K on Full Phone Number Details report

  • 10 = the 10th column (starting from column A) on your Full Phone Number Details Report

  • 11 = the 11th column (starting from column A) on Full Phone Number Details report

  • A parameter of FALSE means that the VLOOKUP is looking for an exact match for the lookup value (in this case, the phone numbers)

Additional information

  • The Full Phone Number Details report shows what Sub-account and Location a phone number is on at the time the report is downloaded, so the above process isn't meant to show what's true at the time of the call. Please note that the Sub-Accounts and Locations aren't guaranteed to populate/match.

  • If the call is Termination and the CallSource didn't originate from a Bandwidth telephone number (TN), it will show #N/A. Likewise, if the call is Origination and the CallDestination didn't originate from a Bandwidth TN, it will also show #N/A. While this is rare, it's possible that call legs in a call forwarding scenario will appear this way. If the call originated from your IP address, it's still a valid call and will be billed accordingly.

  • The Full Phone Number Details report provides the TN on Sub-account/Location at the time the report is pulled. If the Sub-Account/Location shows #N/A, the TN may no longer be in your account and therefore won't be listed on your report.

  • There's a 1 million row limit in Excel. If you have more than 1 million calls given in your selected time frame, please use a CSV splitter to complete the above steps.

  • While individual Location line items on your invoice may be displayed incorrectly, the charge summary is accurate.

Questions? Please open a ticket with your Bandwidth Billing Team or call (855) 864-7776!

Did this answer your question?