Issue with a formula
Results 1 to 9 of 9

Thread: Issue with a formula
Thanks Thanks: 0 Likes Likes: 0

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

    Default Issue with a formula

    Hi guys,

    I have an Issue with a formula.

    I have to put the data in order. The formula in cell F2 is LARGE(C2:C8;1), from cells F3 to F7 is the array in the picture. The array is ok, the issue is that is not working exactly as I want. The issue is underline in the picture, I want to repeat 200 because we are in another continent. Guys, Keep in mind that these are a very simplify data.
    I need a general formula, not stuffs like if<>Europe or stuffs like that. I need and Array like this, that repeat data only based on the difference in value from the column on the right. I hope this is clear

    Thank you very much guys.
    Last edited by richard1234567891011; Aug 5th, 2019 at 07:30 PM.

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,248
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Issue with a formula

    Hi,

    Maybe i'm over simplifying this but this looks like an ordinary rundown of values in column C to me.
    If that's case why not use tyhe following: =LARGE($C$2:$C$9;ROW()-1)
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

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

    Default Re: Issue with a formula

    Quote Originally Posted by jorismoerings View Post
    Hi,

    Maybe i'm over simplifying this but this looks like an ordinary rundown of values in column C to me.
    If that's case why not use tyhe following: =LARGE($C$2:$C$9;ROW()-1)

    Dear jorismoerings,

    Thank you for your answer. But no it is not the same. The issue with =LARGE($C$2:$C$9;ROW()-1) is that you will repeat ALL the value that are equal. I don't want to repeat all equal value, I want to repeat specific equal value, in this simplified example, the only values with the same continent.

    Thank you very much.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Issue with a formula

    Is this what you mean?
    Note that I have added two more rows to the sample data.

    Order

    ABCDEF
    1
    2Europe 300 450
    3Europe 50 350
    4Europe 450 300
    5Europe 350 200
    6Europe 200 200
    7Cina 200 100
    8Europe 100 50
    9Cina 50 50
    10Cina 200
    11Europe 350
    12

    Spreadsheet Formulas
    CellFormula
    F2=IFERROR(AGGREGATE(14,6,C$2:C$11/(MATCH(A$2:A$11&C$2:C$11,A$2:A$11&C$2:C$11,0)=(ROW(C$2:C$11)-ROW(C$2)+1)),ROWS(F$2:F2)),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Nov 2013
    Posts
    623
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with a formula

    May be this.
    In F2 the cop down.

    =IFERROR(AGGREGATE(14,6,$C$2:$C$9/($A$2:$A$9="Europe"),ROWS($F$2:$F2)),"")

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Issue with a formula

    Quote Originally Posted by kvsrinivasamurthy View Post
    May be this.
    In F2 the cop down.

    =IFERROR(AGGREGATE(14,6,$C$2:$C$9/($A$2:$A$9="Europe"),ROWS($F$2:$F2)),"")
    That doesn't seem to achieve this

    Quote Originally Posted by richard1234567891011 View Post
    ... I want to repeat 200 because we are in another continent.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with a formula

    Peter thats cool.
    Sam_D_Ben

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with a formula

    Quote Originally Posted by Peter_SSs View Post
    Is this what you mean?
    Note that I have added two more rows to the sample data.

    Order

    A B C D E F
    1
    2 Europe 300 450
    3 Europe 50 350
    4 Europe 450 300
    5 Europe 350 200
    6 Europe 200 200
    7 Cina 200 100
    8 Europe 100 50
    9 Cina 50 50
    10 Cina 200
    11 Europe 350
    12

    Spreadsheet Formulas
    Cell Formula
    F2 =IFERROR(AGGREGATE(14,6,C$2:C$11/(MATCH(A$2:A$11&C$2:C$11,A$2:A$11&C$2:C$11,0)=(ROW(C$2:C$11)-ROW(C$2)+1)),ROWS(F$2:F2)),"")


    Excel tables to the web >> Excel Jeanie HTML 4

    Your formula is working!! Thank you very much!!!!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Issue with a formula

    Quote Originally Posted by richard1234567891011 View Post
    Your formula is working!! Thank you very much!!!!
    You're welcome.

    BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •