Hi I need help trying to list the max values in a new column. If my data is as follows:



Column A; Column B; Column C;
1. Adelaide; Sara Poon; 1
2. King; Jason Ex; 2
3. Wellington; John Smith 1
4. Bill; John Smith; 1
5. King; Jason Ex; 3
6. Adelaide; Joe Shoe; 2
7. Adelaide; Joe Shoe; 1
8. King; Jason Ex; 1

I need to results in column D to read the highest value from column C if column A and B match and return a value of 0 for all other columns. For example rows 2, 5, and 8 have the same name in column A and B but I want to capture the highest value from column C which is row 5. The output I am wanting is as follows:

Column A; Column B; Column C; Column D
2. King; Jason Ex; 2; 0
5. King; Jason Ex; 3; 3
8. King; Jason Ex; 1;0

Try this is array formula in D1:

=IF(C1=MAX(IF(($A$1:$A$8=A1)*($B$1:$B$8=B1),$C$1:$ C$8)),C1,0)

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down

Thank you this is very helpful, but it is only capturing the max value, which is 3.
A- Location; B- Name; C - number of visits; D - max number of visits
1. Adelaide; Sara Poon; 1;0
2. King; Jason Ex; 2;0
3. Wellington; John Smith; 1 ; 0
4. Bill; John Smith; 1; 0
5. King; Jason Ex; 3; 3
6. Adelaide; Joe Shoe; 2; 0
7. Adelaide; Joe Shoe; 1; 0
8. King; Jason Ex; 1;0

But what I am looking for is to capture is if columns A (location) and B (name) match, then capture the max number of visits D from column C
A- Location; B- Name; C - number of visits; D - max number of visits
1. Adelaide; Sara Poon; 1;1
2. King; Jason Ex; 2;0
3. Wellington; John Smith; 1 ; 1
4. Bill; John Smith; 1; 1
5. King; Jason Ex; 3; 3
6. Adelaide; Joe Shoe; 2; 2
7. Adelaide; Joe Shoe; 1; 0
8. King; Jason Ex; 1;0

Hi,

When you enter this formula you need to confirm it with CTRL+SHIFT+ENTER first (you will see { } brackets appear around the formula)... then copy it down. It is a special Array formula

Thank you again!! This is so helpful!! I got it to work and capture max value including and above 2 visits, however if the Value in Column A and B only occur once the max value in column C is 1. I need to be able to capture the "1" in column C if that is the max value. Right now I am getting a 0. Any chance you might know how to solve this?

My formula should capture that.

See attached. Let me know what is wrong with it or amend it to show what you are requiring then re-attach.

Attached Files

Book2.xlsx (8.7 KB, 11 views)

The "1" you have in column D are "0" when I run the formula. All other values in column D "2" and "3" are the same as you have.

Hmm... are you referencing the correct relative cells?

Can you attach your spreadsheet (if not confidential) otherwise, a sample showing same issue...?

it's confidential and it's a pretty big file to change the content. I did try the formula in a small table of data and it works, but I'm working with close to 100,000 rows of data and for some reason it's not capturing the Max Value "1" in column D.

We figured it out!! Your formula is correct and thank you soooo much for all your help. The issue we had was not in the formula but in some of our data. The 0 in column D we are now capturing as 1. Thank you again!!!

Great! You are welcome.