Need a formula to get start balance and end balance by year

[NeedExcelHelp2021]

I have a lot of cells so a formula that can do this would be a great help, and can you make it work on filtered cells Thank You.

Book1
	A	B	C	D	E	F	G	H
1	2009	$ 10,000		Year	Start Balance	End Balance
2	2009	$ 15,000		2009	$ 10,000	$ 30,000
3	2009	$ 20,000		2010	$ 35,000	$ 50,000
4	2009	$ 40,000		2011	$ 70,000	$ 105,000
5	2009	$ 30,000
6	2010	$ 35,000
7	2010	$ 40,000
8	2010	$ 45,000
9	2010	$ 50,000
10	2010	$ 60,000
11	2010	$ 55,000
12	2010	$ 50,000
13	2011	$ 70,000
14	2011	$ 75,000
15	2011	$ 80,000
16	2011	$ 85,000
17	2011	$ 160,000
18	2011	$ 150,000
19	2011	$ 100,000
20	2011	$ 105,000
21
22
23
Sheet1

 

[alansidman]

This will work in Power Query so long as the the ending balance is the max balance and the starting balance is the min balance

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"min", each List.Min([Column2]), type nullable number}, {"max", each List.Max([Column2]), type nullable number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Column1", "year"}, {"min", "start"}, {"max", "end"}})
in
    #"Renamed Columns"

 

[JEC]

In E2:

=INDEX($B$1:$B$20,MIN(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))

In F2:

=INDEX($B$1:$B$20,MAX(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))

 

[NeedExcelHelp2021]

In E2:

=INDEX($B$1:$B$20,MIN(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))

In F2:

=INDEX($B$1:$B$20,MAX(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))
[/QUOTE]

Thank You it's works, but not on filtered cells ... still Thank You Much Appreciated.

 

[alansidman]

Did you test/try my suggestion? This might help you to understand it better.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[NeedExcelHelp2021]

Did you test/try my suggestion? This might help you to understand it better.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thank You So Much

I watched the videos....i always wander if i can combine my spreadsheets data, this will be great help to me THANK YOU.

 

[Peter_SSs]

a formula that can do this would be a great help, and can you make it work on filtered cells

See if this does what you want. Since you said a filtered list I assumed that the data actually has a header row.

List unfiltered:

21 10 03.xlsm
	A	B	C	D	E	F
1	Year	Balance		Year	Start Balance	End Balance
2	2009	10000		2009	10000	30000
3	2009	15000		2010	35000	50000
4	2009	20000		2011	70000	105000
5	2009	40000
6	2009	30000
7	2010	35000
8	2010	40000
9	2010	45000
10	2010	50000
11	2010	60000
12	2010	55000
13	2010	50000
14	2011	70000
15	2011	75000
16	2011	80000
17	2011	85000
18	2011	160000
19	2011	150000
20	2011	100000
21	2011	105000
22
Start End Filtered

Cell Formulas
Range	Formula
E2:E4	E2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4	F2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)

List filtered:

21 10 03.xlsm
	A	B	C	D	E	F
1	Year	Balance		Year	Start Balance	End Balance
2	2009	10000		2009	10000	40000
3	2009	15000		2010	40000	55000
4	2009	20000		2011	150000	150000
5	2009	40000
8	2010	40000
9	2010	45000
12	2010	55000
19	2011	150000
22
Start End Filtered

Cell Formulas
Range	Formula
E2:E4	E2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4	F2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)

 

[NeedExcelHelp2021]

See if this does what you want. Since you said a filtered list I assumed that the data actually has a header row.

List unfiltered:

21 10 03.xlsm
	A	B	C	D	E	F
1	Year	Balance		Year	Start Balance	End Balance
2	2009	10000		2009	10000	30000
3	2009	15000		2010	35000	50000
4	2009	20000		2011	70000	105000
5	2009	40000
6	2009	30000
7	2010	35000
8	2010	40000
9	2010	45000
10	2010	50000
11	2010	60000
12	2010	55000
13	2010	50000
14	2011	70000
15	2011	75000
16	2011	80000
17	2011	85000
18	2011	160000
19	2011	150000
20	2011	100000
21	2011	105000
22
Start End Filtered

Cell Formulas
Range	Formula
E2:E4	E2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4	F2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)

List filtered:

21 10 03.xlsm
	A	B	C	D	E	F
1	Year	Balance		Year	Start Balance	End Balance
2	2009	10000		2009	10000	40000
3	2009	15000		2010	40000	55000
4	2009	20000		2011	150000	150000
5	2009	40000
8	2010	40000
9	2010	45000
12	2010	55000
19	2011	150000
22
Start End Filtered

Cell Formulas
Range	Formula
E2:E4	E2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4	F2	=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)

OMG!!! Works Brilliantly.

THANK YOU SO MUCH HAVE A GREAT DAY.

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the follow-up.