Grouping A Column By Max Value

[ec1957]

I think I am making this too hard, any help is appreciated.

Three columns; A, B and C
Column A is filled with all numbers, somtimes these numbers repeat
Column B is filled with three letter text chars, sometimes these numbers repeat
Coumn C is filled with numbers

For each unique value in Column A, I need to pull out the corresponding max value in Column C and then show me what is in Column B. Essentially i'm trying to group on column A based on the largest value in column c which will then show me what value appeared in column B.

Any help would be great. Thanks.

 

[Akashwani]

Hi,

Did you find a solution to this?
See if this helps....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Number	TEXT	Numbers	Rank	Count	*	Count	Number	Numbers	TEXT
2	123	Cat	1231	13	1	*	7	123	1231	Cat
3	123	Dog	1240	7	0	*	*	124	1235	Dog
4	123	Mat	1244	3	0	*	*	125	1232	Cat
5	123	Cow	1246	1	0	*	*	126	1237	Dog
6	124	Dog	1235	11	1	*	*	127	1238	Bat
7	124	Rat	1242	5	0	*	*	130	1241	Cat
8	125	Cat	1232	12	1	*	*	132	1243	Dog
9	125	Pat	1239	8	0	*	*	*	*	*
10	126	Dog	1237	10	1	*	*	*	*	*
11	127	Bat	1238	9	1	*	*	*	*	*
12	130	Cat	1241	6	1	*	*	*	*	*
13	130	Hat	1245	2	0	*	*	*	*	*
14	132	Dog	1243	4	1	*	*	*	*	*
Sheet3

Copy the formulas in D, E, H, I, J down.
Once you sort by column D, the data in H, I, J will change.
The formulas in E, G, H help create a Unique list in H of all the numbers in column A

Probably not the best method and a bit late, but it seems to work.

Ak

 

[MikeWx]

Using Akashwani's data, the following formulas in cells D2 and E2, then copied down, will also provide your desired result. The D2 formula provides a list of unique values in Column A. The E2 formula uses the values in Column D to provide your final answer. Range NUMBER is all values in Column A, range TEXT is all values in Column B, and range NUMBERS is all values in Column C. These formulas use the IFERROR function, so you need at least Excel 2007.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">123</TD><TD>Cow</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>{=IFERROR(INDEX(NUMBER,SMALL(IF(MATCH(NUMBER,NUMBER,0)=ROW(INDIRECT("1:"&ROWS(NUMBER))),MATCH(NUMBER,NUMBER,0),""),ROW(A1))),"")}</TD></TR><TR><TD>E2</TD><TD>{=IFERROR(INDEX(TEXT,SUMPRODUCT(--(MAX((--(NUMBER=D2)*NUMBERS))=NUMBERS),--(D2=NUMBER),ROW(NUMBERS))-1),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Mike