Finding max value of each group and paste to another cell

Dear masters,

I have One Excel sheet. that contains groups and each group contains set of data. so i want to extract each group of data with respect of specified column max value and paste to another cell with corresponding remaining cell values. please find attachment for clarity. kindly make suitable formula for getting required data from input data.


Thanks in advance.

with a pivot table and match.

I made a pivot table of the data.

If you want to learn pivot table, google pivot table and you will find a lot of hits.

maybe other forummembers will solve it with formula, I solved it with pivot table.

Dear All Masters,

I have One Excel sheet. that contains groups and each group contains set of data. so i want to extract each group of data with respect of specified column max value and paste to another cell with corresponding remaining cell values. please find attachment for clarity. kindly make suitable formula for getting required data from input data.


Thanking you all Excel Masters.

you can use this macro to fill most of the data (not the last values).

Please Login or Register  to view this content.

If I understand correctly then the attached file should give you the results you want. The formulas that look up the desired values are in the form:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

It is NOT allowed to add the same question twice on the forum.

5. Don't duplicate threads. If you have posted the question in one forum, do not post it again in another forum. You are duplicating efforts. If you feel the thread is in the wrong forum and needs to be moved to another forum, PM a mod / admin to do it for you.


http://www.excelforum.com/showthread...t=#post4451940

Originally Posted by JeteMc

If I understand correctly then the attached file should give you the results you want. The formulas that look up the desired values are in the form:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

Thank you for providing formula, how you get O3, O4, O5, O6, 07 (-1.45,-10.23, -3.99, -15.11, 2.1) ? Because these values should be produced by formula. kindly make the modification.

Thanking you sir.

Please do NOT post duplicate threads. I will merge this with your other thread

Dear sir,

I want Formula based version instead of pivot table version.

Kindly provide Formulas based Version.

Also in that case it is usefull to stick in the same treat.

Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile says 2003, but your file indicates at least 2007

@pvsvprasad

Since ypu get answers from several members, it is alway good to add to whom you are replying.

Please Login or Register  to view this content.

I took the MIN values and that is -2.00 instead of 2.10 (as you stated).

Why should it be 2.10, In that cause the other answers would be wrong.

Originally Posted by pvsvprasad

...how you get O3, O4, O5, O6, 07 (-1.45,-10.23, -3.99, -15.11, 2.1) ? Because these values should be produced by formula.

Assuming that 'Beam No.'s' will be filled in manually this formula will find the 'Max Y':

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

The array entered formula* for the beam numbers includes a reference to a helper column (L), which may be hidden for aesthetic purposes:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

You can try the attached file (All with formulas).

2.10 is Bigger than -2.00. so kindly modify the code.

my set is

2.10
-0.16
-1.50
-1.80
-2.00


So 2.10 is Bigger than -2.00

0,65
-0,31
-0,88
-0,28
-1,45

Then I think that the max of this data 0.65 is and not -1,45 (as you stated).

Originally Posted by sanram

You can try the attached file (All with formulas).

Thank you very much sir, if i want to increase to more rows, what is the procedure? any idea? because if i have hundreds of rows what i do? kindly help me.

Originally Posted by oeldere

0,65
-0,31
-0,88
-0,28
-1,45

Then I think that the max of this data 0.65 is and not -1,45 (as you stated).

Yes Sir exactly, i want to look up maximum of specified column i.e F Column and paste with relative remaining columns. kindly modify the your code.

Originally Posted by JeteMc

The array entered formula* for the beam numbers includes a reference to a helper column (L), which may be hidden for aesthetic purposes:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

Thank you Very much sir,Your code is now worked well.

Kindly provide your code for A3 to F503 (Range) . because i dont know to edit your code. please help me.

Originally Posted by pvsvprasad

Kindly provide your code for A3 to F503 (Range) .

1) Copy the formula in L27, the helper column, down to L503.
2) Change the $27 in each of the formulas for columns M:P to $503
3) Re-activate the array entered formulas in columns M and O by pressing Ctrl, Shift, and Enter simultaneously.
Let me know if you have any questions.

File with formulas updated as described in post #32.
Let me know if you have any questions.

Thank you very much sir.

iam adding some rows at A28 to A30,

But not developing results at M8 to P8,

What is the problem? kindly check and debug please.

Because in future if i want to add rows at A to K , I want produce the results at M to P rows

The formulas in columns N through P have now been amended so that they begin with =IF(M3="","",... This prevents the #N/A from showing when there is no value in those columns. The formulas in columns M through P have been copied down to row 503 by selecting M7:P7 and double clicking the fill handle in the lower right corner of P7.
Let me know if you have any questions.

Originally Posted by sanram

I have made some adjustment to the formulas. See the attachment. Now it will work for all rows.

Dear sir,

Thank you for your kind reply.

I am tested your file.with help your file results are not produced at M to P Rows. kindly check and modify.

i need develop the results at M to P, until data is available at A to F rows(i.e End of page).

Thanking you.

Originally Posted by pvsvprasad

now code is good.

You're Welcome and thank you for the feedback. If that answers your question, please select Thread Tools from the menu link above the first post on this page and mark this thread as SOLVED. I hope that you have a blessed day.