SOLVED

VoIP billing in Excel

Contributor

Hi All, I am trying to create a spreadsheet to calculate my client's VoIP call cost. Unfortunately, all clients are on one excel CSV spreadsheet, so I want it to separate the one spreadsheet into multiple under the client number and then I want it to recognize the service provider and determine which rate to use. Test spreadsheet attached, can someone help?

6 Replies

@Janedb This looks like a job for Power Query (PQ). In the attached file, I transformed your data to a format you can work further with in PQ or outside of PQ. For instance, attaching the correct rates per provider, calculate the cost and summarise everything by client in a pivot table, for instance.

 

Columns K to Q in the attached file contain the output of very basic PQ steps.

 

Are you familiar with PQ? If not, it's going to take a bit getting used to, but once you get the hang of it you'll love it. In a real life scenario you would connect to the CSV file without loading it into Excel first.

Hi Riny, I have never worked on PQ yet. How do I link the csv?
best response confirmed by Janedb (Contributor)
Solution

@Janedb Okay, then you need to do some studying first. A good starting point could be here:

https://exceloffthegrid.com/power-query-introduction/ , and there are many more resources on-line. Just Google and pick a few.

Don't expect it to be super easy from the start, but as said, once you have started using PQ, you will find it difficult to not use for almost everything you do in Excel.

This is definitely something that would trigger the "I want to know" lol
@Riny_van_Eekelen Thank you for your help. Didnt take to long to figure the PQ out. Got the results I was looking for and it works perfectly.

@Janedb Great! Spread the word.