How to convert multiple monthly worksheets to single sheet using Pivot table|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Please try adding the worksheet again. Don't forget to press "Start upload" before you submit your reply. 

Thanks for the file. Tried to open it on my Mac but did not succeed. Tried on a PC and could open the file, noting that it it's in viewing mode. As soon as I enable editing the month sheets produce #REF errors as some of the data is linked to a file on your own machine.

Regarding the pivot table, look in the design tab. Press Subtotals / do not show subtotal. Then press Report Layout / show in tabular form

That will show one row per Cons.No and Name.

And one piece of advice would be to not predefine a table with over a million empty rows in it. Limit the "Entries" table to the rows used and it will expand automatically (including formulas) when you add entries at the bottom. The pivot table will also include the new entries upon a refresh.

Created a new file with only the Entries table (for used rows only) and a pivot table. It reduced the file size from 2.9MB to 23KB.

Having said all that, I'm not really sure what you are trying to achieve here. So perhaps you can clarify a bit.

Thanks. Even though I'm an accountant myself I have a hard time figuring out what you are trying to do. For instance, where would the amounts for "Amount Pd Last Meeting" come from with the data set you provided?

Regarding the "bf Balance due", that could be based on a pivot table that sums the Net Balance column filtering only amounts up to and including September 2022.

Perhaps all clear in your mind, but not in mine. Sorry!

I took a closer look at the Entries table and am confused. 

Let's have a look at 133/Colin. in May-22 I see three rows. Income 85 and Expense 5 Monthly Subs, Expense 45 Annual Levy, 35 Donation. It seems like Colin paid 85 into your bank account and you want to allocate it as 5 subs, 45 annual levy and 35 donation received. All three items are Income for your organisation, yet you put the amounts in the expense column. Or are you thinking in terms of income for the organisation and expenses for the member?

Clive is a bit different. From May-22 to September-22 he seems to have made two lump-sum payments totalling 207 (1 in May and 1 in September). Then you included 6 separate expense items (2 in May, 1 in June and 2 in September)

I believe you need to re-think the whole process. The attached file contains an example of how I would approach it. Create a long list of transactions where you record/allocate the activity attendance fees due for each member on each respective date. One on each row. Then enter the incoming amounts from the bank statements on their respective dates. Now, you can use pivot tables to analyse per member, per month, per activity. You name it.

I reshaped the Entries data and note that I inserted a first row for the Opening Balance in the Bank. That's just my guess. But you need it to be able to reconcile your bank balance.

See if this is useful.