Create unique references (according to date) across two tabs then summarise on third tab

[STUARTXL]

Hi All,

Typically I provide background on the reason why I'm asking the question that I'm asking, but this one is a bit too long winded to explain. So with reference to the attached, I have three tabs:

- Bank Account 1 Activity
- Bank Account 2 Activity
- Summary

For both 'Bank Account 1 Activity' and 'Bank Account 2 Activity':

- As the names suggests, they represent separate bank accounts
- All activity for the bank accounts are downloaded from online banking and pasted into the respective tabs i.e. these tabs represent actual expenditure
- Each type of expenditure (column D) will be grouped into a broader category using the data validation drop down list in column I
- Selecting a type of expenditure in column I will then generate a unique reference in column J

For 'Summary' tab:
- Using the drop down list in G8 the reader of the document will select which category of expenditure he/she wishes to see
- Changing the category in G8 will change the unique reference in column D
- The unique reference in column D acts as the lookup value which, using the INDEX MATCH function, then replicates the bank expenditure of all bank activity within that group of expenditure

The problem I'm having is that the information replicated on the summary tab is only picking up expenditure from 'Bank_Account_1-Activity' tab but I need to incorporate the expenditure from 'Bank_Account_2-Activity' tab too. The data from both tabs need to appear on the 'Summary' tab in date order, with each single item of activity having it's own row on the summary tab. I assume the best way to do this would be to amend the unique values on the two bank tabs so that no 'unique reference' is duplicated (as is currently the case) and so that each unique reference is given according to date, and then amending the INDEX MATCH function so that it incorporates an IF function to look up the second tab if the unqiue reference isn't found on the first....but I don't know where to start.

[JohnTopley]

It would be much easier if you merged your bank account tabs into one and adding an extra column to identify which bank activity any transaction pertains to.

I can see no advantage in having separate tabs but several in having a single one.

[JohnTopley]

Just to add ...

there is nothing (obvious) in the Summary tab that identifies which account the data belongs to so a single tab with A/C ID (to me) is a valid option.

[STUARTXL]

Hi John,

I'd agree with your second point, this is a must, but I hadn't put this in yet as this will be dependent on my achieving the consolidation of data.

The reason I keep the accounts separate is because they also act as a bank reconciliation. On my real version, there are more fields to the bank rec, including a summary (opening balance, incoming, outgoing and closing balance) as well as a field for inserting the bank statement as an object. Keeping them separate allows the readers at Head Office to be able to be able to see that the accounts do in fact reconcile. Combining them would simply confuse matters.

[Glenn Kennedy]

I agree with John T. It's the "in date order" bit... It's easy enough to search through multiple sheets sequentially, but another thing entirely to swap from one sheet to another and back again looking for the next oldest date.

[STUARTXL]

OK in that case I guess I can have a second summary table for the second bank account. That part isn't a deal breaker, just a 'nice to have'. Although I've been told everything is possible on Excel, I take on board your comments over the difficulty involved. At the end of the day, my own ability to maintain the sheet is more important than perfecting presentation. Thanks for your input guys.

[JohnTopley]

Just produce a reconciliation sheet(s) (daily/weekly/monthly): I do it with my own finances at home, reconciling a download from my bank with my own records (Excel-based!), including opening/closing balances/incomings/outgoings.

Having data in one tab should not make any reconciliation more difficult: the reconciliation logic is (surely) the same.

And as Glenn pointed out, having to "jump" between A/Cs based on dates makes it even more difficult.

[JohnTopley]

I've been told everything is possible on Excel

... i doubt "everything"!

However, it would be possible to do what you require in Excel BUT might involved using VBA as a more efficient way of achieving your object.

My own personal reconciliation is a simple VBA-based solution.


And with two summaries, aren't you still left with reconciling the two????

[STUARTXL]

So we've established that the above would be difficult but one other thing I've just thought I could do with is to make the index match dependent on selected dates (or if not INDEX MATCH, use any other formula that will allow this). Basically I will create fields for 'Start Date' period and 'End Date' period on the summary tab and the reader will define these fields e.g. if the reader wanted to see bank activity in Q1 FY2016 he/she would then set the Start Date as 01/01/2016 and the end date as 31/03/2016 and the only transactions to show in the summary table will be those that fall within that period?

And in doing this, would the unique references even be relevant? Could I just create a summary list that assembles data based on the category (G8) and the Start Date and End Date?

[STUARTXL]

So we've established that the above would be difficult but one other thing I've just thought I could do with is to make the index match dependent on selected dates (or if not INDEX MATCH, any other formula that will allow this). Basically I will create fields for 'Start Date' period and 'End Date' period on the summary tab and the reader will define these fields e.g. if the reader wanted to see bank activity in Q1 FY2016 he/she would then set the Start Date as 01/01/2016 and the end date as 31/03/2016 and the only transactions to show in the summary table will be those that fall within that period?

And in doing this, would the unique references even be relevant? Could I just create a summary list that assembles data based on the category (G8) and the Start Date and End Date?

[STUARTXL]

Thanks John. At this time my experience with VBA is minimal and it is essential I be able to look at a formula and at least know what it is doing. With VBA, I would be out of my depth and I don't want to be reliant on the help of people on this forum to correct errors etc. So for now the two sheet approach is preferred. Additionally, on my actual sheet, my bank reconciliation sheets are formatted such that when the online downloads are copied and pasted, the data is arranged in a format that allows direct upload to accounts software, so this is another reason for keeping the spreadsheets separate.

[Glenn Kennedy]

The only obvious (to me) bodge-job would be to merge the two sheets on another (hidden) sheet and then do a nice simple Index-Match or Index-Small on it... Not elegant... but.

[JohnTopley]

Yes a date-based extraction is viable BUT if you need to extract from 2 summaries in date order then we still have problem highlighted earlier by Glenn.