Comparing Values in Multiple Rows and Bringing Back Chosen Values

[gazgazgazgaz]

Hi Folks,

Looking for some assistance with this scenario.

ID	Post	Person	Check Needed	Check in Place	What I want to see
1234	WW1	Bob	Level 1	None	Highest Check Needed (Across ALL posts) for Bob is Level 2 and Highest Level in place is Level 1. Action to be taken.
1234	WW1	Bob	Level 1	Level 1
1234	WW2	Bob	Level 2	Level 1
2555	DR1	Jimmy	Level 3	Level 2	Highest Check Needed (Across ALL posts) for Jimmy is Level 3 and Highest Level in place is Level 2. Action to be taken.
2555	DR1	Jimmy	Level 3	Level 2
3698	TY6	Angela	Level 1	None	Highest Check Needed (Across ALL posts) for Angela is Level 3 and Highest Level in place is Level 3. No action to be taken.
3698	TY8	Angela	Level 3	Level 3
4011	EE3	Xavier	Level 3	None	Highest Check Needed (Across ALL posts) for Xavier is Level 3 and Highest Level in place is Level 2. Action to be taken.
4011	EE4	Xavier	Level 3	Level 1
4011	EE4	Xavier	Level 3	Level 2

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>

Unique ID will be the (ID) but they can have multiple differences in the other fields.

I want to bring back the desired highest check required and highest check in place values for each employee (across multiple rows) but display results in one row.

I've thought about assigning scores to the values and summing or a pivot table but I can't decipher the best options (if Excel is the best solution!)

Thank you.

Gary.

 

[oldbrewer]

ID	Post	Person	Check Needed	Check in Place	HELPER1	HELPER2
1234	WW1	Bob	Level 1	None	1	0
1234	WW1	Bob	Level 1	Level 1	1	1
1234	WW2	Bob	Level 2	Level 1	2	1
2555	DR1	Jimmy	Level 3	Level 2	3	2
2555	DR1	Jimmy	Level 3	Level 2	3	2
3698	TY6	Angela	Level 1	None	1	0
3698	TY8	Angela	Level 3	Level 3	3	3
4011	EE3	Xavier	Level 3	None	3	0
4011	EE4	Xavier	Level 3	Level 1	3	1
4011	EE4	Xavier	Level 3	Level 2	3	2
			check needed	check in place
		Bob	2	1
		Jimmy	3	2
		Angela	3	3
		Xavier	3	2
		the 2 against Bob is obtained by
		=SUMPRODUCT(MAX(($C$2:$C$11=C21)*$F$2:$F$11))

<colgroup><col span="3"><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Legacy 396030]

I hope this helps!

Sheet1

	A	B	C	D	E	F	G	H	I	J	K
1	Data Table		Maximum Values
2	ID	Post	Person	Check Needed Level	Check Level in Place		ID	Post	Person	Highest Check Needed Level	Highest Check Level in Place
3	1234	WW1	Bob	1.00	44.00		2555	DR1	Jimmy	5.00	8.00
4	1234	WW2	Bob	8.00	1.00		2555	DR2	Jimmy	0.00	0.00
5	1234	WW3	Bob	7.00	1.00		1234	WW1	Bob	1.00	44.00
6	2555	DR1	Jimmy	5.00	2.00		1234	WW2	Bob	8.00	1.00
7	2555	DR1	Jimmy	3.00	8.00		1234	WW3	Bob	7.00	1.00
8	3698	TY6	Angela	1.00	0.00		3698	TY6	Angela	1.00	0.00
9	3698	TY8	Angela	2.00	3.00		3698	TY8	Angela	2.00	3.00
10	4011	EE3	Xavier	3.00	0.00		4011	EE3	Xavier	3.00	0.00
11	4011	EE4	Xavier	3.00	1.00		4011	EE4	Xavier	3.00	1.00
12	4011	EE5	Xavier	5.00	2.00		4011	EE5	Xavier	5.00	2.00
13										0.00	0.00
14
15							Minimum Values
16							ID	Post	Person	Highest Check Needed Level	Highest Check Level in Place
17			Comparing Values in Multiple Rows and Bringing Back Chosen Values				2555	DR1	Jimmy	3.00	2.00
18							2555	DR2	Jimmy	0.00	0.00
19							1234	WW1	Bob	1.00	44.00
20							1234	WW2	Bob	8.00	1.00
21							1234	WW3	Bob	7.00	1.00
22							3698	TY6	Angela	1.00	0.00
23							3698	TY8	Angela	2.00	3.00
24							4011	EE3	Xavier	3.00	0.00
25							4011	EE4	Xavier	3.00	1.00
26							4011	EE5	Xavier	5.00	2.00
27										0.00	0.00

<colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 85.6px;"> <col style="width: 85.6px;"> <col style="width: 85.6px;"> <col style="width: 155.2px;"> <col style="width: 183.2px;"> <col style="width: 85.6px;"> <col style="width: 85.6px;"> <col style="width: 85.6px;"> <col style="width: 85.6px;"> <col style="width: 215.2px;"> <col style="width: 256px;"></colgroup> <tbody>
</tbody>

Spreadsheet Formulas

Cell Formula J3 {=MAX(IF($A$3:$A$13=$G3,IF($B$3:$B$13=$H3,IF($C$3:$C$13=$I3,D$3:D$13))))} K3 {=MAX(IF($A$3:$A$13=$G3,IF($B$3:$B$13=$H3,IF($C$3:$C$13=$I3,E$3:E$13))))} J4 {=MAX(IF($A$3:$A$13=$G4,IF($B$3:$B$13=$H4,IF($C$3:$C$13=$I4,D$3:D$13))))} K4 {=MAX(IF($A$3:$A$13=$G4,IF($B$3:$B$13=$H4,IF($C$3:$C$13=$I4,E$3:E$13))))} J5 {=MAX(IF($A$3:$A$13=$G5,IF($B$3:$B$13=$H5,IF($C$3:$C$13=$I5,D$3:D$13))))} K5 {=MAX(IF($A$3:$A$13=$G5,IF($B$3:$B$13=$H5,IF($C$3:$C$13=$I5,E$3:E$13))))} J6 {=MAX(IF($A$3:$A$13=$G6,IF($B$3:$B$13=$H6,IF($C$3:$C$13=$I6,D$3:D$13))))} K6 {=MAX(IF($A$3:$A$13=$G6,IF($B$3:$B$13=$H6,IF($C$3:$C$13=$I6,E$3:E$13))))} J7 {=MAX(IF($A$3:$A$13=$G7,IF($B$3:$B$13=$H7,IF($C$3:$C$13=$I7,D$3:D$13))))} K7 {=MAX(IF($A$3:$A$13=$G7,IF($B$3:$B$13=$H7,IF($C$3:$C$13=$I7,E$3:E$13))))} J8 {=MAX(IF($A$3:$A$13=$G8,IF($B$3:$B$13=$H8,IF($C$3:$C$13=$I8,D$3:D$13))))} K8 {=MAX(IF($A$3:$A$13=$G8,IF($B$3:$B$13=$H8,IF($C$3:$C$13=$I8,E$3:E$13))))} J9 {=MAX(IF($A$3:$A$13=$G9,IF($B$3:$B$13=$H9,IF($C$3:$C$13=$I9,D$3:D$13))))} K9 {=MAX(IF($A$3:$A$13=$G9,IF($B$3:$B$13=$H9,IF($C$3:$C$13=$I9,E$3:E$13))))} J10 {=MAX(IF($A$3:$A$13=$G10,IF($B$3:$B$13=$H10,IF($C$3:$C$13=$I10,D$3:D$13))))} K10 {=MAX(IF($A$3:$A$13=$G10,IF($B$3:$B$13=$H10,IF($C$3:$C$13=$I10,E$3:E$13))))} J11 {=MAX(IF($A$3:$A$13=$G11,IF($B$3:$B$13=$H11,IF($C$3:$C$13=$I11,D$3:D$13))))} K11 {=MAX(IF($A$3:$A$13=$G11,IF($B$3:$B$13=$H11,IF($C$3:$C$13=$I11,E$3:E$13))))} J12 {=MAX(IF($A$3:$A$13=$G12,IF($B$3:$B$13=$H12,IF($C$3:$C$13=$I12,D$3:D$13))))} K12 {=MAX(IF($A$3:$A$13=$G12,IF($B$3:$B$13=$H12,IF($C$3:$C$13=$I12,E$3:E$13))))} J13 {=MAX(IF($A$3:$A$13=$G13,IF($B$3:$B$13=$H13,IF($C$3:$C$13=$I13,D$3:D$13))))} K13 {=MAX(IF($A$3:$A$13=$G13,IF($B$3:$B$13=$H13,IF($C$3:$C$13=$I13,E$3:E$13))))} J17 {=MIN(IF($A$3:$A$13=$G17,IF($B$3:$B$13=$H17,IF($C$3:$C$13=$I17,D$3:D$13))))} K17 {=MIN(IF($A$3:$A$13=$G17,IF($B$3:$B$13=$H17,IF($C$3:$C$13=$I17,E$3:E$13))))} J18 {=MIN(IF($A$3:$A$13=$G18,IF($B$3:$B$13=$H18,IF($C$3:$C$13=$I18,D$3:D$13))))} K18 {=MIN(IF($A$3:$A$13=$G18,IF($B$3:$B$13=$H18,IF($C$3:$C$13=$I18,E$3:E$13))))} J19 {=MIN(IF($A$3:$A$13=$G19,IF($B$3:$B$13=$H19,IF($C$3:$C$13=$I19,D$3:D$13))))} K19 {=MIN(IF($A$3:$A$13=$G19,IF($B$3:$B$13=$H19,IF($C$3:$C$13=$I19,E$3:E$13))))} J20 {=MIN(IF($A$3:$A$13=$G20,IF($B$3:$B$13=$H20,IF($C$3:$C$13=$I20,D$3:D$13))))} K20 {=MIN(IF($A$3:$A$13=$G20,IF($B$3:$B$13=$H20,IF($C$3:$C$13=$I20,E$3:E$13))))} J21 {=MIN(IF($A$3:$A$13=$G21,IF($B$3:$B$13=$H21,IF($C$3:$C$13=$I21,D$3:D$13))))} K21 {=MIN(IF($A$3:$A$13=$G21,IF($B$3:$B$13=$H21,IF($C$3:$C$13=$I21,E$3:E$13))))} J22 {=MIN(IF($A$3:$A$13=$G22,IF($B$3:$B$13=$H22,IF($C$3:$C$13=$I22,D$3:D$13))))} K22 {=MIN(IF($A$3:$A$13=$G22,IF($B$3:$B$13=$H22,IF($C$3:$C$13=$I22,E$3:E$13))))} J23 {=MIN(IF($A$3:$A$13=$G23,IF($B$3:$B$13=$H23,IF($C$3:$C$13=$I23,D$3:D$13))))} K23 {=MIN(IF($A$3:$A$13=$G23,IF($B$3:$B$13=$H23,IF($C$3:$C$13=$I23,E$3:E$13))))} J24 {=MIN(IF($A$3:$A$13=$G24,IF($B$3:$B$13=$H24,IF($C$3:$C$13=$I24,D$3:D$13))))} K24 {=MIN(IF($A$3:$A$13=$G24,IF($B$3:$B$13=$H24,IF($C$3:$C$13=$I24,E$3:E$13))))} J25 {=MIN(IF($A$3:$A$13=$G25,IF($B$3:$B$13=$H25,IF($C$3:$C$13=$I25,D$3:D$13))))} K25 {=MIN(IF($A$3:$A$13=$G25,IF($B$3:$B$13=$H25,IF($C$3:$C$13=$I25,E$3:E$13))))} J26 {=MIN(IF($A$3:$A$13=$G26,IF($B$3:$B$13=$H26,IF($C$3:$C$13=$I26,D$3:D$13))))} K26 {=MIN(IF($A$3:$A$13=$G26,IF($B$3:$B$13=$H26,IF($C$3:$C$13=$I26,E$3:E$13))))} J27 {=MIN(IF($A$3:$A$13=$G27,IF($B$3:$B$13=$H27,IF($C$3:$C$13=$I27,D$3:D$13))))} K27 {=MIN(IF($A$3:$A$13=$G27,IF($B$3:$B$13=$H27,IF($C$3:$C$13=$I27,E$3:E$13))))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[gazgazgazgaz]

Thanks folks. Sorted with assistance!

 

[Peter_SSs]

@BillyRaySpivy
When using Excel jeanie, please use the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.