MAX Value in a Complex Environment

[wdgor]

I have spent hours trying to figure this out with no success. I decided to turn to the experts.

I have a worksheet that has:

Column A has a unique record identifier, Column F has Parent Accounts (there are a lot of duplicates because a parent can be associated with a number of children), Column I has the total number of contacts per unique identifier. I need to look at each parent account and designate the one with the most contacts. In the example below, it would be line 11337. In column J, I concatenated the Parent Name with the Total separated by an "*" in one attempt to use FIND to try and find the maximum value for the parent (probably not the best approach). I could not get it to work for all 12,000 records. Using the right formula in a Conditional Formatting formula would be an approach, i.e. the MAX value for each parent would be formatted with a Yellow fill. Or, on a separate worksheet in the workbook, I could have a non-duplicating list of Parent accounts and have the unique identifier of the record with the MAX count of total contacts in a separate column, i.e. have the Parent in column A and the identifier in column B. I anxiously await the thinking of this august group as my brain is getting fried. I am sure one of you will have an "easy" solution that escapes me. Thanks in advance.

Example:

 

[RayFrye]

Assumption:

• Your data is in a worksheet named "Sheet1."
• Column A contains unique record identifiers.
• Column F contains Parent Accounts.
• Column I contains the total number of contacts per unique identifier.

Steps:

1. Create a new worksheet where you want to display the results. Let's call it "Unique Parents."
2. In cell A1 of the "Unique Parents" worksheet, enter "Parent Name."
3. In cell B1 of the "Unique Parents" worksheet, enter "Max Contact Record Identifier."
4. In cell A2 of the "Unique Parents" worksheet, enter the first parent name.
5. In cell B2 of the "Unique Parents" worksheet, enter the following formula to find the record identifier with the maximum contacts for the first parent:
   excel
   =INDEX(Sheet1!$A$2:$A$12001,MATCH(MAXIFS(Sheet1!$I$2:$I$12001,Sheet1!$F$2:$F$12001,A2),Sheet1!$I$2:$I$12001,0))
   This formula will find the record identifier with the maximum contacts for the parent specified in cell A2.
6. Drag the formula in cell A2 and B2 down to fill in the rest of the unique parent names and their respective record identifiers.
7. Now, you have a list of unique parent names and the corresponding record identifier with the most contacts in the "Unique Parents" worksheet.

If you want to use conditional formatting to highlight the maximum values in your original worksheet, you can follow these steps:

1. Select the range in Column F (Parent Accounts) where you have duplicates.
2. Go to the "Home" tab in the Excel ribbon.
3. Click on "Conditional Formatting" and select "New Rule."
4. Choose "Use a formula to determine which cells to format."
5. Enter the following formula:
   excel
   =I2=MAXIFS(I:I,F2,F2)
   This formula will check if the total contacts in the current row (cell I2) is equal to the maximum total contacts for the corresponding parent (using MAXIFS).
6. Click the "Format" button to set the formatting style (e.g., yellow fill).
7. Click "OK" to apply the conditional formatting.

Now, the rows with the maximum total contacts for each parent will be highlighted with a yellow fill in your original worksheet.
These steps should help you find the parent with the most contacts and highlight them as needed in your Excel workbook.

 

[wdgor]

Unfortunately, that doesn't seem to work. I get the same Record ID for every condition.

With this formula:

=INDEX(Test!$A$2:$A$13000,MATCH(MAXIFS(Test!$A$2:$A$13000,Test!$F$2:$F$13000,H2),Test!$I$2:$I$13000,0))

Test is the name of "Sheet1". The "Unique Name" is column H on my new worksheet and "Identifier for Max Value" is column I. I would also like the Parent Name and actual MAX value. I can certainly do a lookup from the unique identifier but would be great to have the Name and value along with the identifier, e.g., 17170657933 - Stonecrest Living - 34. Thanks for your insight.

Unique Name	Identifier for Max Value
American Housing	17170657933
Stonecrest Living	17170657933
WestHaven Senior Living	17170657933
Distinctive Living	17170657933
Ohana Ventures	17170657933
Inspirit Senior Living	17170657933

 

[AhoyNC]

Maybe something like this.

Book2
	A	B	C	D	E	F	G
3	Record ID	Parent	Total Contacts		Record ID	Parent	Max
4	1	Walker	1		5	Walker	222
5	2	Walker	1		8	Smith	88
6	3	Walker	3		16	Jones	68
7	4	Walker	5
8	5	Walker	222
9	6	Walker	5
10	7	Walker	8
11	8	Smith	88
12	9	Smith	45
13	10	Smith	1
14	11	Smith	1
15	12	Smith	6
16	13	Smith	48
17	14	Jones	1
18	15	Jones	5
19	16	Jones	68
20	17	Jones	6
Sheet1

Cell Formulas
Range	Formula
F4:F6	F4	=UNIQUE(B4:B20)
E4:E6	E4	=FILTER($A$4:$A$20,($B$4:$B$20=F4)*($C$4:$C$20=G4))
G4:G6	G4	=MAXIFS($C$4:$C$20,$B$4:$B$20,F4)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:C20	Expression	=$A$4:$A$20=$E$4:$E$6	text	NO

 

[wdgor]

Getting closer - thanks.

I get a number of #SPILL! errors in the Record ID column E. I formatted the column to wrap text, but the error persists. There really shouldn't be a #SPILL! error because there should be only one Record ID that fits the criteria in Column G, right?

 

[AhoyNC]

Sounds like there is more than one ID that fits your criteria in some cases.

Try:

Book1
	A	B	C	D	E	F	G	H
1	Record ID	Parent	Total Contacts			Parent	Max	Record ID
2	1	Walker	1			Walker	222	5
3	2	Walker	1			Smith	88	8, 10
4	3	Walker	3			Jones	68	16
5	4	Walker	5
6	5	Walker	222
7	6	Walker	5
8	7	Walker	8
9	8	Smith	88
10	9	Smith	45
11	10	Smith	88
12	11	Smith	48
13	12	Smith	6
14	13	Smith	4
15	14	Jones	1
16	15	Jones	5
17	16	Jones	68
18	17	Jones	6
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=UNIQUE(B2:B18)
G2:G4	G2	=MAXIFS($C$2:$C$18,$B$2:$B$18,F2)
H2:H4	H2	=TEXTJOIN(", ",1,FILTER($A$2:$A$18,($B$2:$B$18=F2)*($C$2:$C$18=G2)))
Dynamic array formulas.

 

[wdgor]

Yes, you are right. There are a number of instances where the MAX number is the same across multiple companies under the same Parent. I should have thought of the TEXTJOIN myself.

Thank you very much for your excellent solution - it is very much appreciated.

 

[AhoyNC]

You're welcome. Thanks for the feedback.