Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

In list of invoice data - columns are date; customer, value, value paid; data is 5 consecutive years; how would you extract the statement of account?

Hint: Answer should utilise filters

Method should be easier than "deselect all" and searching and finding the customer name and ticking the box? 

 

 

 

user-image
Question added by Deleted user
Date Posted: 2015/01/20
Deleted user
by Deleted user

The pivot table is the most efficient and usefull way.

I guess you are looking to recover your unpaid invoices.

With a pivot table you'll manage those datas according what you are looking for. You'll be able to have a subtotal of unpaid invoices by customers, by year,..and many more.

You can have a global overiew and dig into details.

Imran Akhtar
by Imran Akhtar , Accountant , National Foods Products

The best solution is to use pivot table but as simple solution, add anothor column of open amount and put difference of total value minus value paid. After this Add filter > Number filter > Does not equal zero.Then it'll show all over/underpaid/unpaid invoices. Put subtotal formula after last value. Now come to customer column filter and select one by one each customer and take print. After print better copy the filtered data to another sheet for your future soft record so after receiving payments you can reconcile customer accounts as well.

Deleted user
by Deleted user

just got to know about Pivot table, hope this ll help you in this regard.  You can Group data according to your requirements in case of multiple records against one customer/Client using Pivot table 

AWAIS BUTT
by AWAIS BUTT , Accounting Manager , Glocalization Systems Limited

I am not sure i understood your query completely but in my opinion You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of customers contained in the range say A2:C10. now suppose the customers ID numbers are stored in the first column of the range, If you know thecustomer  ID number, you can use the VLOOKUP function to return the full account statement of specific customer but u have to link that statement1st with your sheet where you applied the vlookup i hope i was helpful .regards

Umair Habib
by Umair Habib , Inventory Controller , Landmark Group

You can use vlook up function to extract the statement of account.

TILAL YASSIN
by TILAL YASSIN , مسؤول سلامة وصحة مهنية , شركة توزيع الكهرباء السودانية

the best for SQL server query

Faraz Shiwani
by Faraz Shiwani , Managing Partner , Shiwani Traders

If you can send me your file, I can give you some possible solutions, inbox me. if you are agreed.

 

You can change your clients name as1,2,3, and items as a, b, c, etc. and re-set when you get the file back.

Deleted user
by Deleted user

The easiest way is to use the DATA TAB

Click on the "Filter" icon

The on the name of the customer ARROW, right click; "text filters" and type in the customers name

 

The filter function will reflect only that customer;

You may also then filter the "date"

 

The data remaining is easily copied into a statement or worksheet to enable reconciliation and explanation to your customer. 

 

There is also Groupings that can be used in the DATA TAB.

 

All other answers are correct, I just use the easiest method.

Firas Shahadi
by Firas Shahadi , Human Resources Director , SAUDI UNION

Fast and easy way to use filter. Or you can use advance filter using customer field as filter value.

Thanks

More Questions Like This