Display max value and other related fields

[#1]

Hello everyone,

I need help in accomplishing the final product.  See step by step information below: 

Raw data:

Name	Dept	Dept % 1	Dept % 2	Role
Maria	LT	25%	80%	Core
Maria	BT	55%	0%	Embed
John	LT	75%	25%	Embed
Lisa	CT	0%	100%	Core
Lisa	LT	60%	20%	Embed

Step 1:

create column 5. See formula in column title for more information.

1	2	3	4	5	6
Name	Dept	Dept % 1	Dept % 2	=IF(Dept % 2 >=0.35, '>35%','<35%')	Role
Maria	LT	25%	80%	>35%	Core
Maria	BT	55%	0%	<35%	Embed
John	LT	75%	25%	<35%	Embed
Lisa	CT	0%	100%	>35%	Core
Lisa	LT	60%	20%	<35%	Embed

Step 2:

If duplicate name, select the row that has max value = (Dept %1 + Dep % 2). 

Step 3: FINAL:

After eliminating duplicates,

Count name by role

Final Product:

Dept	=IF(Dept % 2 >=0.35, '>35%','<35%')	Count of Name by role = Core	Count of name by role = Embed
BT	>35%	1
JT	>35%		1
LT	<35%	1
	>35%	1

[nirav_bhimani]

Hi,

You should do the group by Name, then find max out of that in the script only and then do the join with your main table with resident table which contain few columns in that.

Regards,

Nirav Bhimani

[sushil353 · Accepted solution]

Hi,

Please find the attached app.

HTH

Sushil

[#4]

Thank you Sushil for making time to help me with this.  This worked.

[#5]

Thank you Sushil for making time to help me.  This worked.