MAXIF and return another column
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: MAXIF and return another column

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MAXIF and return another column

    Hi,

    Please can somebody help?


    The purpose is to get the Max of column C for each AssetID(column B) and then return whether it’s a Cross Arm or Pole but I’d need to returnall values for Poles and Cross Arm if the highest occurs more than once per AssetID.

    So just for the below example, the Max is
    4.20825744623599 which is true for both the L Pole and R Pole. How would I write the formula around my MAXIF so that it returns 'L Pole, R Pole'?


    I do have a spreadsheet I can attach but can't see a way of attaching it.





    A B C D
    10029396C KPO-TMU-A0008 2.43650068130681
    CROSS ARM
    10029396L
    KPO-TMU-A0008 4.20825744623599
    L POLE
    10029396R
    KPO-TMU-A0008
    4.20825744623599
    R POLE








  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,474
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: MAXIF and return another column

    Try:

    ABCDEFG
    1AAsset IDCDAsset IDValue
    210029396CKPO-TMU-A00082.4365CROSS ARMKPO-TMU-A0008L POLE, R POLE
    310029396LKPO-TMU-A00084.20826L POLE
    410029396RKPO-TMU-A00084.20826R POLE

    Sheet3



    Array Formulas
    CellFormula
    G2{=TEXTJOIN(", ",1,IF(C2:C4=MAXIFS(C:C,B:B,F2),D2:D4,""))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAXIF and return another column

    Hi Eric,

    Thank you so much - I tried to use the formula for a much larger dataset but failed. I've got just over 31,000 rows so I tweaked C2:C4 and D2:D4 to extend accordingly but didn't work.

    In the F column, I've got just over 13,000 rows with the unique AssetIDs that are being matched up on column B. Also Asset IDs could have anywhere between 1 to 5 rows with different properties on the D column so I would need to display all of D with the Max of C.

    Hope that makes sense.

    Am currently trying to figure out a way to post a snapshot of a slightly bigger dataset on here but also failing on that!

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MAXIF and return another column

    Hi, Can you tell which version of excel are you using? See if you have TEXTJOIN function.

    If you have try below, slight tweak in the formula in #2 :

    ABCDEFG
    1ABCDAsset ID Value
    210029396CKPO-TMU-A00082.436500681CROSS ARMKPO-TMU-A0008L POLE, R POLE
    310029396LKPO-TMU-A00084.208257446L POLE
    410029396RKPO-TMU-A00084.208257446R POLE

    Sheet9



    Array Formulas
    CellFormula
    G2{=TEXTJOIN(", ",1,IF((($C$2:$C$4)*($B$2:$B$4=F2))=MAXIFS($C$2:$C$4,$B$2:$B$4,F2),$D$2:$D$4,""))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAXIF and return another column

    Quote Originally Posted by Aryatect View Post
    Hi, Can you tell which version of excel are you using? See if you have TEXTJOIN function.
    Hi - I am using 2016 so TEXTJOIN works.

    I've finally worked out how to paste from Excel using the add-ins! Here's a snapshot of my data sorted via AssetID. The left handside contains all the data for each asset and the right handside contains the unique Asset IDs with what should be a concatenation of Pole and Cross Arm if joint Max HI (column C). I've copied the formula you've kindly given and just extended it to 31884 but all are coming up as errors.

    What am I doing wrong?

    A B C D E F G
    1 C1 (UT_HI197) Asset ID HI Pole and Cross Arm Identifier Distinct AssetID Return Cross Arm or Pole based on Max HI
    2 10175251M AHA-DOB-A0241A 1.362298087 M POLE AHA-DOB-A0241A #VALUE!
    3 10274203C AHA-DOB-A0242 1.580660109 CROSSARM AHA-DOB-A0242 #VALUE!
    4 10274203M AHA-DOB-A0242 0.889005093 M POLE AHA-DOB-A0243 #VALUE!
    5 10175258M AHA-DOB-A0243 7.401968309 M POLE AHA-DOB-A0244 #VALUE!
    6 10175258C AHA-DOB-A0243 6.118525986 CROSSARM AHA-DOB-A0245 #VALUE!
    7 10274204M AHA-DOB-A0244 0.941667698 M POLE AHA-DOB-A0246 #VALUE!
    8 10175264C AHA-DOB-A0245 3.09334561 CROSSARM AHA-DOB-A0247 #VALUE!
    9 10175264M AHA-DOB-A0245 1.362298087 M POLE AHA-DOB-A0248 #VALUE!
    10 10175267M AHA-DOB-A0246 7.401968309 M POLE AHA-DOB-A0249 #VALUE!
    11 10175267C AHA-DOB-A0246 7.401968309 CROSSARM AHA-DOB-A0250 #VALUE!
    12 10175270M AHA-DOB-A0247 1.362298087 M POLE AHA-DOB-A0251 #VALUE!
    13 10175270C AHA-DOB-A0247 3.09334561 CROSSARM AHA-DOB-A0252 #VALUE!
    14 10175273M AHA-DOB-A0248 1.744746795 M POLE AHA-DOB-A0253 #VALUE!
    15 10298647M AHA-DOB-A0249 0.80383286 M POLE AHA-DOB-A0254 #VALUE!
    16 10175279C AHA-DOB-A0250 3.09334561 CROSSARM AHA-DOB-A0255 #VALUE!
    17 10175279M AHA-DOB-A0250 1.362298087 M POLE AHA-DOB-A0256 #VALUE!
    18 12053027C AHA-DOB-A0251 NULL CROSSARM AHA-DOB-A0257 #VALUE!
    19 12053027M AHA-DOB-A0251 NULL M POLE AHA-DOB-A0258 #VALUE!
    20 13161276L AHA-DOB-A0252 NULL L POLE AHA-DOB-A0259 #VALUE!
    21 13161276R AHA-DOB-A0252 NULL R POLE AHA-DOB-A0260 #VALUE!
    22 10175288M AHA-DOB-A0253 7.401968309 M POLE AHA-DOB-A0261 #VALUE!
    23 10175288C AHA-DOB-A0253 6.118525986 CROSSARM AHA-DOB-A0262 #VALUE!
    24 10175291M AHA-DOB-A0254 5.5 M POLE AHA-DOB-A0263 #VALUE!
    25 10175294M AHA-DOB-A0255 1.023057647 M POLE AHA-DOB-A0264 #VALUE!
    26 10175294C AHA-DOB-A0255 3.09334561 CROSSARM AHA-DOB-A0265 #VALUE!
    27 10316356C AHA-DOB-A0256 4.541385053 CROSSARM AHA-DOB-A0266 #VALUE!
    28 10316356M AHA-DOB-A0256 1.282592096 M POLE AHA-DOB-A0267 #VALUE!
    29 10319129C AHA-DOB-A0257 5.076351457 CROSSARM AHA-DOB-A0268 #VALUE!
    30 10319129M AHA-DOB-A0257 1.522207127 M POLE AHA-DOB-A0269 #VALUE!
    1509

    Array Formulas
    Cell Formula
    G2 {=TEXTJOIN(", ",1,IF((($C$2:$C$31884)*($B$2:$B$31884=F2))=MAXIFS($C$2:$C$31884,$B$2:$B$31884,F2),$D$2:$D$31884,""))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MAXIF and return another column

    Hi, so I got your problem, it is happening because of NULL text in some of the columns. Trying to find a way around it
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MAXIF and return another column

    Can you replace NULL with 1 or any big number like 10000?
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: MAXIF and return another column

    maybe like this?

    Asset ID Max Pole and Cross Arm Identifier
    AHA-DOB-A0243
    7.401968309
    M POLE, CROSSARM
    AHA-DOB-A0253
    7.401968309
    M POLE, CROSSARM
    AHA-DOB-A0246
    7.401968309
    M POLE, CROSSARM
    AHA-DOB-A0254
    5.5
    M POLE
    AHA-DOB-A0257
    5.076351457
    CROSSARM, M POLE
    AHA-DOB-A0256
    4.541385053
    CROSSARM, M POLE
    AHA-DOB-A0255
    3.09334561
    M POLE, CROSSARM
    AHA-DOB-A0250
    3.09334561
    CROSSARM, M POLE
    AHA-DOB-A0245
    3.09334561
    CROSSARM, M POLE
    AHA-DOB-A0247
    3.09334561
    M POLE, CROSSARM
    AHA-DOB-A0248
    1.744746795
    M POLE
    AHA-DOB-A0242
    1.580660109
    CROSSARM, M POLE
    AHA-DOB-A0241A
    1.362298087
    M POLE
    AHA-DOB-A0244
    0.941667698
    M POLE
    AHA-DOB-A0249
    0.80383286
    M POLE
    AHA-DOB-A0252
    0
    L POLE, R POLE
    AHA-DOB-A0251
    0
    CROSSARM, M POLE
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAXIF and return another column

    Quote Originally Posted by Aryatect View Post
    Can you replace NULL with 1 or any big number like 10000?
    Sorry I usually work with SQL and forgot that NULLs are texts in Excel. I couldn't replace them with any other number as that will affect the MAXIFS calculation so I have removed them and the NULL cells are just blank.

    I think we're nearly there as the results are mostly coming through but would need to tweak the formula as the ones with just blank cells for certain AssetIDs are coming up as #VALUE ! error. How do I then get the CrossArm and Pole if the AssetIDs have no values in HI column (C)?

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAXIF and return another column

    Quote Originally Posted by sandy666 View Post
    maybe like this?

    [COLOR=#FFFFFF ]Asset ID[/COLOR] [COLOR=#FFFFFF ]Max[/COLOR] [COLOR=#FFFFFF ]Pole and Cross Arm Identifier[/COLOR]
    AHA-DOB-A0243
    7.401968309
    M POLE, CROSSARM
    AHA-DOB-A0253
    7.401968309
    M POLE, CROSSARM
    AHA-DOB-A0246
    7.401968309
    M POLE, CROSSARM
    AHA-DOB-A0254
    5.5
    M POLE
    AHA-DOB-A0257
    5.076351457
    CROSSARM, M POLE
    AHA-DOB-A0256
    4.541385053
    CROSSARM, M POLE
    AHA-DOB-A0255
    3.09334561
    M POLE, CROSSARM
    AHA-DOB-A0250
    3.09334561
    CROSSARM, M POLE
    AHA-DOB-A0245
    3.09334561
    CROSSARM, M POLE
    AHA-DOB-A0247
    3.09334561
    M POLE, CROSSARM
    AHA-DOB-A0248
    1.744746795
    M POLE
    AHA-DOB-A0242
    1.580660109
    CROSSARM, M POLE
    AHA-DOB-A0241A
    1.362298087
    M POLE
    AHA-DOB-A0244
    0.941667698
    M POLE
    AHA-DOB-A0249
    0.80383286
    M POLE
    AHA-DOB-A0252
    0
    L POLE, R POLE
    AHA-DOB-A0251
    0
    CROSSARM, M POLE
    Hi - you seem to have it right as you're getting results for ones with blank HIs like for
    AHA-DOB-A0252 and
    AHA-DOB-A0251
    . What is the formula please?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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