Multiple Rows for Unique Identifier need to return max value

[IluvPivots]

Using Excel 2016...MAXIF is not an option.
Col A is Employees with unique identifier; each is listed multiple times. I need to know for each what the max hrs were.
Cannot sort data.

Name/ID	Hrs logged	Desired Result (Max value for each unique ONLY in max row)
Bob123	5
Bob123	10
Sara223	3	3
Sara223	1
Bob123	15	15

 

[KRice]

Here is one approach. I used dynamic ranges for the A and B columns to adapt to a changing table size.

Book1
	A	B	C	D
1	Name/ID	Hrs logged
2	Bob	5
3	Bob	10
4	Sara	3		3
5	Sara	1
6	Bob	15		15
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=IF(B2=MAX($B$2:INDEX($B:$B,COUNTA($A:$A))*($A2=$A$2:INDEX($A:$A,COUNTA($A:$A)))),B2,"")

 

[IluvPivots]

Here is one approach. I used dynamic ranges for the A and B columns to adapt to a changing table size.

Book1
	A	B	C	D
1	Name/ID	Hrs logged
2	Bob	5
3	Bob	10
4	Sara	3		3
5	Sara	1
6	Bob	15		15
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=IF(B2=MAX($B$2:INDEX($B:$B,COUNTA($A:$A))*($A2=$A$2:INDEX($A:$A,COUNTA($A:$A)))),B2,"")

Perfect! Thank you!!!

 

[KRice]

You're welcome...happy to help.

 

[IluvPivots]

You're welcome...happy to help.

So I'm able to recreate this exact table and use the (excellent) formula provided. However when I try to recreate on my much more massive spreadsheet, it does not work. The data does start on row4. Any thoughts on what may make it different?

 

[IluvPivots]

So I'm able to recreate this exact table and use the (excellent) formula provided. However when I try to recreate on my much more massive spreadsheet, it does not work. The data does start on row4. Any thoughts on what may make it different?

I think its because the values are the same! They didn't have a max in these cases.

 

[KRice]

There may still be some issues with the dynamic range feature. The COUNTA function is counting items in column A and using that number to determine how far down to extend the range selection. But is that an official Excel table being used? In other words, if you click on a cell in the table, do you see a Table menu category appear on the top of the window, with options for renaming or resizing the table?

 

[IluvPivots]

There may still be some issues with the dynamic range feature. The COUNTA function is counting items in column A and using that number to determine how far down to extend the range selection. But is that an official Excel table being used? In other words, if you click on a cell in the table, do you see a Table menu category appear on the top of the window, with options for renaming or resizing the table?

It's excel, but it is not a labeled table. just cells. I changed a value, and when I reenter the formula on that row it works, but when I copy down from the beginning it turns everything to zero.

 

[KRice]

Okay, I think there are a couple of issues. The formula I offered assumes that we can rely on column A to count a contiguous range of something, and that number is used to establish the last cell reference in the A and B columns (in my example). You've adapted that to work on columns E and DA, using column E for counting....no problem... but the data begins in row 4. So the COUNTA(E:E) doesn't "see" anything in rows 1 and 2, which will cause the count to be off by two and you'll miss the bottom two cells in the range...but there's more. You're also applying column filters. The COUNTA function doesn't recognize this and counts as though a filter is not present, which also skews the count. In short, this will cause problems, as the two ranges of interest E4:Exx (not sure, can't see in the image...and same for DA4 and down) will not be correctly sized. Is there anything that you are filtering from view that might possibly be taken as the max value? For example if I'm interesting in looking at Sara and perhaps another column is Years, if I were to filter to show only 2024 and hide 2023, and Sara has an exceptionally large value in 2023, the formula would return that...unless special steps are taken to ensure that filtered rows are ignored. Is that clear?...if not, I'll try to explain better. I'll post back shortly with an alternative.

 

[KRice]

As my last post suggested, this gets a bit more complicated. I'll discuss two things that you'll need to be aware of to assess what works for your situation.

The first concerns the use of a dynamic range. I used the COUNTA function assuming the column whose items are counted had something in all cells...as suggested by your first post. But based on the image posted more recently, there are some blanks above the data table. I can't tell from the image whether something is in cell A1, but A2 appears to be empty. So COUNTA will deliver a count that is 1 or 2 short of the desired number. We want the number returned to represent the last row where data are found. I think a safer resolution for this, assuming we want to use dynamic ranges rather than fixed range references, is to allocate a column somewhere out of the way (can be hidden later) and use a different formula to determine the last row number:

=LOOKUP(2,1/(A:A<>""),ROW(A:A))

...we'll refer to this cell several times, so it makes sense to calculate it once and tuck it aside in a helper column. I placed the formula above in cell L2.

The second issue to address concerns the use of filters. This has the potential to cause problems because some functions will deliver results that take into account all cells regardless of their filter status, while other functions can take the filter into account and consider only visible cells. Based on what you're filtering, this may or may not be a problem...but I suspect it is a problem. To resolve this, we need some way to instruct the formula to consider only the visible data. To do that, I've relied on the SUBTOTAL function, which has the ability to ignore filtered/hidden rows. The following formula is placed in cell L4. On my system (Excel 365), this formula automatically spills, but I think you should be able to confirm its entry using Ctrl-Shift-Enter to enter it as an array formula. And then drag the formula down the helper column to cover all of the data rows. This part is difficult for me to test because I don't have Excel 2016, so let me know if you encounter a problem here. You should see a column of 1's appear. The benefit of setting up this column: when filters are applied to the table, for any rows that are hidden, the corresponding value in the VisRow helper column will become 0 rather than 1. So we can multiply this VizRow column by the Hrs Logged column to effectively zero out any of the rows hidden by the filter.

=SUBTOTAL(3,OFFSET($A$4,ROW($A$4:INDEX($A:$A,$L$2))-ROW($A$4),0))

In the example below, the original formula from the earlier post is shown, and a sample data table includes "something" in cell A1. When we COUNTA column A, we'll get 16 rather than 17, so any dynamic ranges formed using this method will miss the last row of data...unless something else is done to resolve the problem. To illustrate the problem, I intentionally made Sara's "hours logged" the largest in the last row (27). I've also added a couple of columns to the data table to provide some filtering capabilities that will be discussed below: one column can be filtered by year, another by some arbitrary letter code.

1. You'll see the formula (column F...original dynamic range) fails to recognize Sara's max value in the last row due to the blank/COUNTA issue. Instead, the formula assigns the max value as 25.
2. An easy fix involves changing how the dynamic range is formed...by using the more reliable helper value in cell L2. That is done in the column G formula...revised dynamic range formula. It considers the full range of data and correctly "sees" the last max value.
3. Still another option is to avoid dynamic ranges altogether and revise the formula with overly large fixed ranges (shown in the column H formula...fixed range formula): this would ensure coverage of all of the data, but it does require periodic inspection to confirm that the data table hasn't outgrown the specified range size.
4. Still, none of these address the filtering/hidden rows issue. If we take the fixed range formula and simply multiply the arrays inside the MAX function by the VisRow helper range, we'll isolate only visible values for MAX to operate on. This is shown in column I...visrow fixed range formula.
5. Finally, this same idea can be extended to the dynamic range version, as shown in column J...visrow dynamic range formula.

At first glance, there appears to be no difference in the results...but see example further below.

MrExcel_20240305A.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Something											LastRow Helper
2												17
3	Name/ID	Hrs logged	Year	Other		orig dyn rng	rev dyn rng	fixed rng	visrow fixed rng	visrow dyn rng		VisRow Helper
4	Bob	5	2024	a								1
5	Bob	10	2024	b								1
6	Sara	3	2024	a								1
7	Sara	1	2024	b								1
8	Bob	18	2024	a								1
9	Bob	11	2024	b								1
10	Sara	13	2024	a								1
11	Bob	17	2024	b								1
12	Sara	11	2023	a								1
13	Bob	2	2023	b								1
14	Bob	23	2023	a		23	23	23	23	23		1
15	Sara	25	2023	b		25						1
16	Bob	18	2023	a								1
17	Sara	27	2023	b			27	27	27	27		1
Sheet2

Cell Formulas
Range	Formula
L2	L2	=LOOKUP(2,1/(A:A<>""),ROW(A:A))
F4:F17	F4	=IF($B4=MAX($B$4:INDEX($B:$B,COUNTA($A:$A))*($A4=$A$4:INDEX($A:$A,COUNTA($A:$A)))),$B4,"")
G4:G17	G4	=IF($B4=MAX($B$4:INDEX($B:$B,$L$2)*($A4=$A$4:INDEX($A:$A,$L$2))),$B4,"")
H4:H17	H4	=IF($B4=MAX($B$4:$B$25 * ($A4=$A$4:$A$25)),$B4,"")
I4:I17	I4	=IF($B4=MAX(($B$4:$B$25)*($L$4:$L$25)*($A4=$A$4:$A$25)),$B4,"")
J4:J17	J4	=IF($B4=MAX(($B$4:INDEX($B:$B,$L$2)) * ($L$4:INDEX($L:$L,$L$2)) * ($A4=$A$4:INDEX($A:$A,$L$2))),$B4,"")
L4:L17	L4	=SUBTOTAL(3,OFFSET($A$4,ROW($A$4:INDEX($A:$A,$L$2))-ROW($A$4),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$A$3:$D$17	L4:L17, L2, J4:J17, F4:G17

Same data, but year filter applied to shown 2024: the first three formulas fail to display any results because the maximum values are in hidden rows. The visrow formulas display results based on the visible rows.

MrExcel_20240305A.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Something											LastRow Helper
2												17
3	Name/ID	Hrs logged	Year	Other		orig dyn rng	rev dyn rng	fixed rng	visrow fixed rng	visrow dyn rng		VisRow Helper
4	Bob	5	2024	a								1
5	Bob	10	2024	b								1
6	Sara	3	2024	a								1
7	Sara	1	2024	b								1
8	Bob	18	2024	a					18	18		1
9	Bob	11	2024	b								1
10	Sara	13	2024	a					13	13		1
11	Bob	17	2024	b								1
18
Sheet2

Cell Formulas
Range	Formula
L2	L2	=LOOKUP(2,1/(A:A<>""),ROW(A:A))
F4:F11	F4	=IF($B4=MAX($B$4:INDEX($B:$B,COUNTA($A:$A))*($A4=$A$4:INDEX($A:$A,COUNTA($A:$A)))),$B4,"")
G4:G11	G4	=IF($B4=MAX($B$4:INDEX($B:$B,$L$2)*($A4=$A$4:INDEX($A:$A,$L$2))),$B4,"")
H4:H11	H4	=IF($B4=MAX($B$4:$B$25 * ($A4=$A$4:$A$25)),$B4,"")
I4:I11	I4	=IF($B4=MAX(($B$4:$B$25)*($L$4:$L$25)*($A4=$A$4:$A$25)),$B4,"")
J4:J11	J4	=IF($B4=MAX(($B$4:INDEX($B:$B,$L$2)) * ($L$4:INDEX($L:$L,$L$2)) * ($A4=$A$4:INDEX($A:$A,$L$2))),$B4,"")
L4:L11	L4	=SUBTOTAL(3,OFFSET($A$4,ROW($A$4:INDEX($A:$A,$L$2))-ROW($A$4),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$A$3:$D$17	L4:L11, L2, J4:J11, F4:G11

A more complicated filtering example using the same data, with 2023 turned on and "a" turned on. Again the visrow formulas display results based on visible rows, while the other formulas consider all data, even data in the hidden rows.

MrExcel_20240305A.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Something											LastRow Helper
2												17
3	Name/ID	Hrs logged	Year	Other		orig dyn rng	rev dyn rng	fixed rng	visrow fixed rng	visrow dyn rng		VisRow Helper
12	Sara	11	2023	a					11	11		1
14	Bob	23	2023	a		23	23	23	23	23		1
16	Bob	18	2023	a								1
18
Sheet2

Cell Formulas
Range	Formula
L2	L2	=LOOKUP(2,1/(A:A<>""),ROW(A:A))
F12,F14,F16	F12	=IF($B12=MAX($B$4:INDEX($B:$B,COUNTA($A:$A))*($A12=$A$4:INDEX($A:$A,COUNTA($A:$A)))),$B12,"")
G12,G14,G16	G12	=IF($B12=MAX($B$4:INDEX($B:$B,$L$2)*($A12=$A$4:INDEX($A:$A,$L$2))),$B12,"")
H12,H14,H16	H12	=IF($B12=MAX($B$4:$B$25 * ($A12=$A$4:$A$25)),$B12,"")
I12,I14,I16	I12	=IF($B12=MAX(($B$4:$B$25)*($L$4:$L$25)*($A12=$A$4:$A$25)),$B12,"")
J12,J14,J16	J12	=IF($B12=MAX(($B$4:INDEX($B:$B,$L$2)) * ($L$4:INDEX($L:$L,$L$2)) * ($A12=$A$4:INDEX($A:$A,$L$2))),$B12,"")
L12,L14,L16	L12	=SUBTOTAL(3,OFFSET($A$4,ROW($A$4:INDEX($A:$A,$L$2))-ROW($A$4),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$A$3:$D$17	L16, L12, L14, L2, J16, J14, J12, F16:G16, F14:G14, F12:G12

I'm hopeful that one of these will fit well with your usage conditions. But you'll need to assess whether anything that is hidden by filters should be considered or ignored.