How to return the starting balance of a bank account in a master list of bank accounts

[Lyryx]

Hi everyone,


I've built a workbook that evaluates a client's banking information and processes multiple bank accounts at a given time.

I'd like to find a formula that will return the starting balance of each unique bank account from my dataset to reduce human error with data input.

The working paper houses all bank info on a master tab that looks similar to the table at the bottom of this post. Information from this table is then pulled to an Overview tab that updates as more bank info is added to the workbook.

Currently, I need to manually enter the opening balance of the accounts on an overview tab of my workbook to calculate the running total being displayed in the master tab, I'd like to find a formula that will always return the first balance in the "Running Total" column of the table below for each unique Account# to prevent this. Originally, I planned to use a SUMIFS formula based on the account# and a description "Opening Balance" however, this value isn't always included in bank statements or changes to "Balance Forward" or some other variant. I was thinking the Lookup formula could find the first unique Account# Value and then look over 5 columns and return that value (which would be the opening account balance) but I couldn't get it to work...


I appreciate the help with this if anyone has a solutions. Let me know if you need any further clairfications.


Thanks in advance,



The master banking table looks like this.

Bank	Account#	Transaction Date	Description	Withdrawal	Deposits	Running Total
ABC	ABC-123	2020-01-01	Opening Balance		-	50.00
ABC	ABC-123	2020-02-10	Cash withdrawal	25		25.00
CBA	CBA-321	2020-01-01	Balance Forward		-	75.00
CBA	CBA-321	2020-02-10	Cash Deposit		25	100.00

 

[Toadstool]

Hi Lyryx,

Do you just need the first which appears or do you need the earliest date (i.e. the data may not be in date sequence)?

Assuming your Overview sheet already has the account then an INDEX & MATCH would work.

Dataset

Book1
	A	B	C	D	E	F	G
1	Bank	Account#	Transaction Date	Description	Withdrawal	Deposits	Running Total
2	ABC	ABC-123	1/1/2020	Opening Balance		-	50
3	ABC	ABC-123	2/10/2020	Cash withdrawal	25		25
4	CBA	CBA-321	1/1/2020	Balance Forward		-	75
5	CBA	CBA-321	2/10/2020	Cash Deposit		25	100
Dataset

Overview with formula

Book1
	A	B
1	Account#	First Entry
2	ABC-123	50
3	CBA-321	75
Overview

Cell Formulas
Range	Formula
B2:B3	B2	=INDEX(Dataset!G:G,MATCH(A2,Dataset!B:B,0))

 

[Lyryx]

I can't believe I didn't consider index match

Thanks so much!
The data is usually sorted by oldest to newest date so the INDEX Match gets the job done beautifully.