+ Reply to Thread
Results 1 to 11 of 11

Compare values and output cell titles, able to output multiple results

  1. #1
    Registered User
    Join Date
    05-07-2016
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    5

    Compare values and output cell titles, able to output multiple results

    Hi everybody!

    I'm new to this forum so hello to everyone around the world, hope you are having a great weekend.

    I liked to think until today that I am self-sufficient on Excel but alas, I am definitely not...

    I have a spreadsheet where I am comparing weather conditions in multiple cities. I want the 'winner' to be outputted in a cell but if there is a tie I would like all of the equal results to be displayed.

    Here's a quick example of what I want...

    Highest Temperature London Temperature Paris Temperature Madrid Temperature
    Paris, Madrid 21 24 24

    At present I have a formula like so for the 'highest temperature' cells, it doesn't seem to work: =INDEX($E$2:$AF$2,MATCH(MIN(H28,L28,P28,T28,X28,AB28,AF28),H28,L28,P28,T28,X28,AB28,AF28,0)). I get the error 'you've entered too many arguments for this function'. Any ideas why? The first group is the cell titles e.g. London, Paris, Madrid, going across the top row.

    I realise this won't yet output multiple values if there is a tie also, so if anyone has any ideas on that I'm all ears! Please note I can't use ':' for the second and third group selection because there are cells in-between being used for other things.

    Thanks to anyone who might be able to help me

    Mark

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare values and output cell titles, able to output multiple results

    This looks like a nightmare!

    You ask for the highest temps but your formula is using the MIN (lowest temp) function.

    It looks like you want the duplicate temp results to be in a single cell? If so, that will require a VBA function.

    Your formula indexes the range E2:AF2 but your MIN function starts at column H.

    This should be doable but it will be very complicated.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    05-07-2016
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Compare values and output cell titles, able to output multiple results

    Hi Tony,

    Thank you for the reply! It looks like I copied the wrong piece of code for you, the one I am trying to use is indeed using MAX. Sorry about that. Here's the correct piece, which excel returns the 'too many arguments' error for.

    =INDEX($E$2:$AF$2,MATCH(MAX(G5,K5,O5,S5,W5,AA5,AE5),G5,K5,O5,S5,W5,AA5,AE5,0))

    First figures are the city titles, second and third groups are the cells which contain the numerical temperature for the respective cities.

    Thanks in advance
    Mark

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare values and output cell titles, able to output multiple results

    What about the duplicates?

    Are you OK with using a VBA function?

  5. #5
    Registered User
    Join Date
    05-07-2016
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Compare values and output cell titles, able to output multiple results

    I haven't yet attempted the duplicate functionality. I have never used a VBA function before, is it straightforward or a bit of a nightmare?

    Is there another, simpler way of achieving a similar result for outputting multiple identical values?

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare values and output cell titles, able to output multiple results

    Quote Originally Posted by TMG2016 View Post
    I haven't yet attempted the duplicate functionality. I have never used a VBA function before, is it straightforward or a bit of a nightmare?
    At first, you'll think it's a nightmare but once you get it setup you won't even notice it.

    Is there another, simpler way of achieving a similar result for outputting multiple identical values?
    Yes!

    Put duplicates (if any) in separate cells. That way no VBA required.

  7. #7
    Registered User
    Join Date
    05-07-2016
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Compare values and output cell titles, able to output multiple results

    Ok, I can do that, thanks Tony .

    Do you have any idea as to where I might be going wrong with my formula?

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare values and output cell titles, able to output multiple results

    Quote Originally Posted by TMG2016 View Post

    Do you have any idea as to where I might be going wrong with my formula?
    You need to add a test for the column numbers.

    Give me a few minutes...

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare values and output cell titles, able to output multiple results

    Try this for the lowest temp. For the highest temp replace all references to MIN with MAX.

    Data Range
    G
    H
    I
    J
    K
    L
    M
    N
    O
    5
    City1
    City2
    City3
    6
    50
    62
    50
    7
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    8
    9
    City1
    10
    City3
    11


    This array formula** entered in G9:

    =IFERROR(INDEX(G$5:O$5,SMALL(IF(MOD(COLUMN(G$5:O$5),4)=3,IF(G$6:O$6=MIN(G$6:O$6),COLUMN(G$6:O$6)-COLUMN(G$6)+1)),ROWS(G$9:G9))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

  10. #10
    Registered User
    Join Date
    05-07-2016
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Compare values and output cell titles, able to output multiple results

    Thanks so much for taking the time to do this Tony! I haven't got it working just yet but I think I might be able to tweak a few things on my sheet to get it working.

    The main issue I think I have is that there is other data in the cells between the temperature cells, so I can't select a range (i.e. I have to enter "cell, cell, cell" instead of "cell:cell"). Any way around that or shall I just re-work the sheet so I can use arrays?

    If it's not too much to ask, would you mind explaining to me what the static numbers represent in that formula such as the 4, 3 and +1?

    Appreciate all the time you've given!

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare values and output cell titles, able to output multiple results

    Quote Originally Posted by TMG2016 View Post

    The main issue I think I have is that there is other data in the cells between the temperature cells, so I can't select a range (i.e. I have to enter "cell, cell, cell" instead of "cell:cell").
    Yes you can!

    If there are numbers in the between cells then use this version...

    Data Range
    G
    H
    I
    J
    K
    L
    M
    N
    O
    5
    City1
    header
    header
    header
    City2
    header
    header
    header
    City3
    6
    15
    15
    0
    0
    23
    1
    5
    15
    15
    7
    8
    9
    City1
    10
    City3


    =IFERROR(INDEX(G$5:O$5,SMALL(IF(MOD(COLUMN(G$5:O$5),4)=3,IF(G$6:O$6=MIN(IF(MOD(COLUMN(G$5:O$5),4)=3,G$6:O$6)),COLUMN(G$6:O$6)-COLUMN(G$6)+1)),ROWS(G$9:G9))),"")

    Still array entered.

    If it's not too much to ask, would you mind explaining to me what the static numbers represent in that formula such as the 4, 3 and +1?
    The 4 and 3 are used to identify the columns where the data of interest is located.

    We look at the column number and divide it by a number that returns a common value that we use as a pattern that we search for.

    For example:

    G5 = column number 7
    K5 = column number 11
    O5 = column number 15

    If we divide those column numbers by 4 we get a remainder of 3:

    MOD(7,4) = 3
    MOD(11,4) = 3
    MOD(15,4) = 3

    So, we look for columns that return 3 when their column number is divided by 4.

    The +1 is used as an "offset correction".

    The cell references in the INDEX function are RELATIVE to the range.

    INDEX(X22:X32...)

    Cell X22 is in position 1 relative to the range X22:X32.

    Cell X25 is in position 4 relative to the range X22:X32.

    In the formula we have to convert the ABSOLUTE column references to RELATIVE references to be compatible with the INDEX function.

    COLUMN(G$6:O$6)-COLUMN(G$6)+1

    COLUMN(G$6)-COLUMN(G$6)+1 = 1
    COLUMN(H$6)-COLUMN(G$6)+1 = 2
    COLUMN(I$6)-COLUMN(G$6)+1 = 3
    COLUMN(J$6)-COLUMN(G$6)+1 = 4
    etc
    etc

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cell reference, output dates to numeric. Should output as text
    By lifeseeker1019 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2015, 05:51 PM
  2. How to compare values and output the higher of the two?
    By a-man in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-25-2014, 03:13 AM
  3. Display Multiple Results in 1 Message Box Output
    By ajxxx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2013, 01:46 AM
  4. [SOLVED] Paste variable output (SQL Output) into a single cell.
    By fblaze in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 06:24 AM
  5. vLookupAll output to multiple rows instead of multiple values in cell?
    By s_t_e_i_n in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-03-2012, 09:13 AM
  6. How to compare two worksheets and output the results
    By MrDisco1 in forum Excel General
    Replies: 1
    Last Post: 03-25-2009, 05:17 PM
  7. [SOLVED] Multiple input and output results
    By smoothie in forum Excel General
    Replies: 0
    Last Post: 06-12-2006, 01:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1