MAX Value in a Complex Environment

wdgor

Board Regular
Joined
Jan 21, 2009
Messages
90
Office Version
  1. 365
Platform
  1. Windows
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:

1693870091573.png
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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 NameIdentifier for Max Value
American Housing17170657933
Stonecrest Living17170657933
WestHaven Senior Living17170657933
Distinctive Living17170657933
Ohana Ventures17170657933
Inspirit Senior Living17170657933
 
Upvote 0
Maybe something like this.

Book2
ABCDEFG
3Record IDParentTotal ContactsRecord IDParentMax
41Walker15Walker222
52Walker18Smith88
63Walker316Jones68
74Walker5
85Walker222
96Walker5
107Walker8
118Smith88
129Smith45
1310Smith1
1411Smith1
1512Smith6
1613Smith48
1714Jones1
1815Jones5
1916Jones68
2017Jones6
Sheet1
Cell Formulas
RangeFormula
F4:F6F4=UNIQUE(B4:B20)
E4:E6E4=FILTER($A$4:$A$20,($B$4:$B$20=F4)*($C$4:$C$20=G4))
G4:G6G4=MAXIFS($C$4:$C$20,$B$4:$B$20,F4)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:C20Expression=$A$4:$A$20=$E$4:$E$6textNO
 
Upvote 0
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?
 
Upvote 0
Sounds like there is more than one ID that fits your criteria in some cases.

Try:
Book1
ABCDEFGH
1Record IDParentTotal ContactsParentMaxRecord ID
21Walker1Walker2225
32Walker1Smith888, 10
43Walker3Jones6816
54Walker5
65Walker222
76Walker5
87Walker8
98Smith88
109Smith45
1110Smith88
1211Smith48
1312Smith6
1413Smith4
1514Jones1
1615Jones5
1716Jones68
1817Jones6
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=UNIQUE(B2:B18)
G2:G4G2=MAXIFS($C$2:$C$18,$B$2:$B$18,F2)
H2:H4H2=TEXTJOIN(", ",1,FILTER($A$2:$A$18,($B$2:$B$18=F2)*($C$2:$C$18=G2)))
Dynamic array formulas.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top