/ / Solve this email query - populating values based on max value

Solve this email query - populating values based on max value

I recently received a query from one our readers - Pedro Costa. Here is the query that I received through email:
Email:
Hi Mohit,
The purpose is to copy the values from beginning in AA19 till the end of the column to the column painted in yellow (G19 till …). The column to copy is determined by the Maximum value between Cell J2 to cell DD2. 
I hope You understand the problem. Thanks in advance for your attention and help.              
Pedro
Explanation of the query:
Here is the attachment - PedroCosta_query.xls (I have cleaned up the columns which are not required and have pasted the formulas as values to maintain confidentiality of Pedro's data).
In this spreadsheet Pedro has some data in a table spread across columns 'B' to 'I'. And there is some data through columns - 'K' to 'DE' and 'DG' to 'HA'. There are a few rows above this data which does some calculations on the given table. 
What is required - In the sheet attached, Pedro needs to populate the column 'G' with that column for which row '2' gives the highest value. For example, if 'T2' cell has the maximum value, then range 'T19' to 'T166' should be populated in the corresponding range in the column 'G'.
So, guys I leave this query open to all of you. Help Pedro in solving this problem. You can either email me the solution or can also post your replies as comment to this post. For those who will send me an email, I will post them as comment myself.
Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

6 comments:

  1. UnknownJuly 17, 2009 at 2:12 AM

    Hello,
    You may use this formula: "INDEX($J$19:$GZ$166,ROW()-18,MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))" in cell G19 and then drag the formula till where you want the data to be copied. Now whenever the data in row 2 changes the data in column G will be populated with the data from the column where the highest value exists in row 2 (the data will be pulled from the range $J$19:$GZ$166).

    Hope this solves Pedro's problem!!

    Thanks,
    hp

    ReplyDelete
  2. UnknownJuly 17, 2009 at 7:58 PM

    Insert the following formula into cell G19 and copy down:

    INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1),MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))

    This should still work even if more blank rows are inserted before row 2 or between rows 2 and 19.

    Jason
    ---

    ReplyDelete
  3. AnonymousJuly 17, 2009 at 8:00 PM

    Try this:

    INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1),MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))

    This will work even if more blank rows are inserted before row 2 and between rows 2 and 19.

    Jason

    ReplyDelete
  4. Excel MaticJuly 18, 2009 at 2:03 AM

    @hp @Jason @Anonymous - guys great work! it works really great. The code has also taken care of inserting a new row between rows 2 and 9. Thanks guys.

    @Jason @Anonymous - what a co-incidence!! You both guys submitted exactly the same code ;-)

    ReplyDelete
  5. Excel MaticJuly 18, 2009 at 2:07 AM

    Here is my code for the same query:

    =INDIRECT(ADDRESS(ROW($I19),MATCH(MAX($K$2:$DE$2),$A$2:$DE$2,0)))

    ReplyDelete
  6. AnonymousJuly 30, 2009 at 7:13 PM

    Guys Pretty Simple and good logic..

    Prakash

    ReplyDelete

Subscribe to: Post Comments ( Atom )

Friday, July 17, 2009

Solve this email query - populating values based on max value

I recently received a query from one our readers - Pedro Costa. Here is the query that I received through email:
Email:
Hi Mohit,
The purpose is to copy the values from beginning in AA19 till the end of the column to the column painted in yellow (G19 till …). The column to copy is determined by the Maximum value between Cell J2 to cell DD2. 
I hope You understand the problem. Thanks in advance for your attention and help.              
Pedro
Explanation of the query:
Here is the attachment - PedroCosta_query.xls (I have cleaned up the columns which are not required and have pasted the formulas as values to maintain confidentiality of Pedro's data).
In this spreadsheet Pedro has some data in a table spread across columns 'B' to 'I'. And there is some data through columns - 'K' to 'DE' and 'DG' to 'HA'. There are a few rows above this data which does some calculations on the given table. 
What is required - In the sheet attached, Pedro needs to populate the column 'G' with that column for which row '2' gives the highest value. For example, if 'T2' cell has the maximum value, then range 'T19' to 'T166' should be populated in the corresponding range in the column 'G'.
So, guys I leave this query open to all of you. Help Pedro in solving this problem. You can either email me the solution or can also post your replies as comment to this post. For those who will send me an email, I will post them as comment myself.

Solve this email query - populating values based on max valueSocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:53 AM
Labels:

6 comments:

Unknown,  July 17, 2009 at 2:12 AM  

Hello,
You may use this formula: "INDEX($J$19:$GZ$166,ROW()-18,MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))" in cell G19 and then drag the formula till where you want the data to be copied. Now whenever the data in row 2 changes the data in column G will be populated with the data from the column where the highest value exists in row 2 (the data will be pulled from the range $J$19:$GZ$166).

Hope this solves Pedro's problem!!

Thanks,
hp

Unknown,  July 17, 2009 at 7:58 PM  

Insert the following formula into cell G19 and copy down:

INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1),MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))

This should still work even if more blank rows are inserted before row 2 or between rows 2 and 19.

Jason
---

Anonymous,  July 17, 2009 at 8:00 PM  

Try this:

INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1),MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))

This will work even if more blank rows are inserted before row 2 and between rows 2 and 19.

Jason

Excel Matic,  July 18, 2009 at 2:03 AM  

@hp @Jason @Anonymous - guys great work! it works really great. The code has also taken care of inserting a new row between rows 2 and 9. Thanks guys.

@Jason @Anonymous - what a co-incidence!! You both guys submitted exactly the same code ;-)

Excel Matic,  July 18, 2009 at 2:07 AM  

Here is my code for the same query:

=INDIRECT(ADDRESS(ROW($I19),MATCH(MAX($K$2:$DE$2),$A$2:$DE$2,0)))

Anonymous,  July 30, 2009 at 7:13 PM  

Guys Pretty Simple and good logic..

Prakash

Post a Comment

Subscribe to: Post Comments (Atom)