Results 1 to 10 of 10

Thread: 2 Formulas, rearrange, CF.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 2 Formulas, rearrange, CF.

    Hi everyone,

    Im looking for 2 formulas, I cant really make 2 posts for it because they run together.


    The first one would be to take the data from EC3:EY3 (with blanc columns in between ) which are " Span numbers " and rearrange them in numerical order starting from FA3 to FS3, its always gone by 19 numbers.


    The second formula is a CF formula that will in the respected order highlight the right numbers.


    For example if you look at row 3 you have number 1 in ED3, EH3, EN3, ES3 and EW3, but only EN3 and ES3 are highlighted, meaning the third and forth in the rearrange numerical order and so on for all the other number, they need to be highlighted according to their position in the chart.

    See image below :

    Image and video hosting by TinyPic

    Thank you.
    Serge.

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    I made the pictures bigger to see and understand better.Thank you for any help.

    Image and video hosting by TinyPic

    Image and video hosting by TinyPic

    Thank you.
    Serge.

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    Can anyone help me with this please ?

    Thank you.
    Serge.

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,575
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    To put them in numerical order, you could put =SMALL($EC3:$EY3, COLUMNS($FA3:FA3)) in FA3 and drag right.

    Formulas can't react to cell color.

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    Thank you Mike, I guess what I need is not possible, that would had really help me in my analysis.
    Serge.

  6. #6
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    Here is a small video I made quick to explain better what I need Thank you for your time.
    Serge.

    https://youtu.be/joJU_nQyNNI

  7. #7
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    I hope someone will take the time to watch it, its only 3:37 mn long, I appreciate it.
    Serge.
    Last edited by serge; Jan 27th, 2019 at 04:59 PM.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,264
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    To make things easier we should put the number of the groups in EC2:EY2, that is


    DW
    DX
    DY
    DZ
    EA
    EB
    EC
    ED
    EE
    EF
    EG
    EH
    EI
    EJ
    EK
    EL
    EM
    EN
    EO
    EP
    EQ
    ER
    ES
    ET
    EU
    EV
    EW
    EX
    EY
    1
    1
    2
    3
    4
    5
    2
    3
    4
    2
    3
    4
    5
    2
    3
    4
    5
    2
    3
    4
    5
    2
    3
    4
    5
    2
    1
    1
    1
    2
    2
    2
    2
    3
    3
    3
    3
    4
    4
    4
    4
    5
    5
    5
    5
    3
    4
    5
    4
    4
    2
    3
    1
    4
    6
    1
    5
    20
    5
    2
    1
    3
    15
    4
    1
    2
    7
    1
    2
    4
    4
    3
    2
    2
    5
    4
    4
    2
    1
    7
    2
    6
    1
    6
    3
    1
    14
    16
    5
    1
    3
    1
    2
    3
    5
    5
    4
    3
    4
    2
    3
    5
    1
    2
    8
    3
    7
    2
    1
    4
    2
    15
    17
    6
    2
    1
    2
    3
    1
    6


    Then select FA3:FS5 and in CF use this formula
    =INDEX($DW3:$EA3,SMALL(IF($EC3:$EY3=FA3,$EC$2:$EY$2),COUNTIF($FA3:FA3,FA3)))=INDEX($EC$1:$EY$1,SMALL(IF($EC3:$EY3=FA3,CO LUMN($EC3:$EY3)-COLUMN($EC3)+1),COUNTIF($FA3:FA3,FA3)))
    pick a format

    Hope this helps

    M.
    Last edited by Marcelo Branco; Jan 28th, 2019 at 03:37 AM.

  9. #9
    Board Regular
    Join Date
    Oct 2008
    Location
    Los Angeles
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    Thank you very much Marcelo, That's a very impressive formula, It works perfectly for my chart, You are incredible .

    Thank you again.
    Serge.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,264
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: 2 Formulas, rearrange, CF.

    You're welcome. Glad to help.

    M.

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
  •