Microsoft Office excel 2007 - upgrade

hello there,

i need help as i need to rank 3 different groups from one data colum, i can separate the groups with and "IF" funtion and then i can rank those, however i need to have them in the same cell for working purposesName Group Total

AAB XY 256.22

AAC XZ

GHAG XX 241835.31

JERHJE XY 103.27

NEJ XZ

NMEK XX 2725.87

NMN XY 3071.4

NMEK XZ 0.01

KIUHJ XX 1110.45

JNEL XY 2113.48

NUE XZ 59723.16

AAAS XX

ENJKN XY 36755.94

SRJR XZ 128750.99

DEKE XX 5939.43

EEK XY 48364.91

UEJE XZ 24783.11

BER XX 54323that needs to rank only within their groups.

can this be done??

thanks

Name Group Total

AAB XY 256.22

AAC XZ

GHAG XX 241835.31

JERHJE XY 103.27

NEJ XZ

NMEK XX 2725.87

NMN XY 3071.4

NMEK XZ 0.01

KIUHJ XX 1110.45

JNEL XY 2113.48

NUE XZ 59723.16

AAAS XX

ENJKN XY 36755.94

SRJR XZ 128750.99

DEKE XX 5939.43

EEK XY 48364.91

UEJE XZ 24783.11

BER XX 54323

hope this looks better, the groups are XX,XY,XZ

Please repost your data after reading the How To referenced below.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Name Group Total AAB XY 256.22 AAC XZ GHAG XX 241835.31 JERHJE XY 103.27 NEJ XZ NMEK XX 2725.87 NMN XY 3071.4 NMEK XZ 0.01 KIUHJ XX 1110.45 JNEL XY 2113.48 NUE XZ 59723.16 AAAS XX ENJKN XY 36755.94 SRJR XZ 128750.99 DEKE XX 5939.43 EEK XY 48364.91 UEJE XZ 24783.11 BER XX 54323 *rank if group (XX...) rank total -(with group)-

Why not use Data...Text To Columns, then sort them with 2 criteria and then use CONCATENATE to put them back together?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

thanks but that will not give me what i need, i need it to look las per below, all in one formula!... Do you know how? i hope this helps Ranked Cells According to Total and Group XY XZ XX Name Group Total 5 AAB XY 256.22 5 AAC XZ 1 GHAG XX 241835.31 6 JERHJE XY 103.27 5 NEJ XZ 4 NMEK XX 2725.87 3 NMN XY 3071.4 4 NMEK XZ 0.01 5 KIUHJ XX 1110.45 4 JNEL XY 2113.48 2 NUE XZ 59723.16 6 AAAS XX 2 ENJKN XY 36755.94 1 SRJR XZ 128750.99 3 DEKE XX 5939.43 1 EEK XY 48364.91 3 UEJE XZ 24783.11 2 BER XX 54323

You are using Column labels (Name, Group, etc.) in your examples but you also said:

"I need to have them in the same cell"I can't tell what is in which column. Please add A, B C, etc above your columns (before and after) so that I tell what you mean by

"rank 3 different groups from one data column"and"I need to have them in the same cell".

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Apologies, what meant was i need the formula which

1.- find only the total if the group is XX,XY,XZ accordingly

2.- Rank the data according to ONLY the values which are on XX,XY,XZ accordinglyfrom my example, i have previously used a formula that has given me all the values according to the groups so fo exapmle cell A2 will have the formula =IF(E2="XY",F2,"")

XY (colum A), XZ (columB) etc... and rows 1,2,3, 4,... etc

what i need is a formula which i can put in A2 which will say, =IF(E2="XY",F2,"") and also Rank the data where it has found values...

does that explain it better??

please refer to the last table for the complete dataA B C D E F 1, XY XZ XX Name Group Total 2, 5 AAB XY 256.22 3, 5 AAC XZ 4, 1 GHAG XX 241835.31

OK, I'm only going to claim about 10% credit for this.

The use of SUMPRODUCT for the ranking based on multiple criteria was not my idea. I found it via a Google search.

I simply adapted it to match your ranges and then added the IF portion to do the ranking based on the XX, XY and XZ column labels.

Just so there is no confusion I am using the layout you described in Response 7. Column labels in Row 1, data begins in Row 2. (In some of your earlier posts, there appears to be a blank row above the data. That's why it is important to use Column letters and Row numbers when posting. Since we can't see your spreadsheet from where we're sitting, you have to be very specific about your layout.)

Try this:

Enter this in A2 and drag it across to C2, then drag all three cells down to the bottom of your list:

=IF($E2=A$1,SUMPRODUCT(--($E$2:$E$19=A$1),--($F2<$F$2:$F$19))+1,"")

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

thats great!! many thanks!... can you please illustrate me in the actual impact the "--" have in a formula? i am still learning much about excel

kindest regards

Daniel

The "--", also known as the double unary, is used to coerce the SUMPRODUCT into returning 1's & 0's as opposed to returning TRUE's and FALSE's.There are other coercion methods (e.g. *1) but the double unary is considered the most efficient.

When SUMPRODUCT is used as a "logical test" (e.g. $E$2:$E$19=A$1) it will return an array of TRUE's and FALSE's. In order to SUM multiple arrays of TRUE's and FALSE's, we need to coerce them into being 1's and 0's.

Try this in a cell:

=TRUE

=-TRUE

=--TRUESee the 5th post in the following thread for more detailed explanation, or Google something like

sumproduct coercionhttp://www.ozgrid.com/forum/showthr...

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History