Results 1 to 10 of 10

Thread: Require changed of formulas as per new layout

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Require changed of formulas as per new layout

    Using Excel 2000
    Hi,

    Columns C:P, I got data, in the column Q have got a index formula, and in the columns S, T & U have count of 1's, X's and 2's as shown in the example sheet1 below...

    Current Formulas
    ABCDEFGHIJKLMNOPQRSTUVW
    1
    2
    3
    4CountCountCount
    5P1P2P3P4P5P6P7P8P9P10P11P12P13P14Index1X2
    61111111XXXXXXX1.094770
    71111111XXXXXX21.095761
    811X21X111X1X2X301.904752
    911X21X111X1X22301.905743
    10X1X21X21X11X111.900.756752
    11X1X21X21X112111.900.765743
    12X22X111X1XX1113.071.386752
    13X22X111X1X21113.071.413743
    14222222X11111114.778.596716
    15222222211111114.780.783707
    16121X2XXX1111121.170.777743
    17121X2XXX1111X11.170.778752
    18
    19
    20
    21

    Sheet1



    Worksheet Formulas
    CellFormula
    S6=COUNTIF(C6:P6,1)
    T6=COUNTIF(C6:P6,"X")
    U6=COUNTIF(C6:P6,2)

    Array Formulas
    CellFormula
    Q6{=SeriesSum(LEN("1X2"), COLUMNS(C6:P6)-1, -1, SEARCH(C6:P6, "1X2") - 1)+1}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    I got a little incontinent now receiving Column C:P data in the one column C only. So far I need help to have a new formulas for index and count of 1's, X's and 2's as per layout shown in the example sheet2 below... is it possible so not to convert column C in text to columns...

    Require formula change as per new format...
    ABCDEFGHIJ
    1
    2
    3
    4CountCountCount
    5Data With No SpaceIndex1X2
    61111111XXXXXXX1.094770
    71111111XXXXXX21.095761
    811X21X111X1X2X301.904752
    911X21X111X1X22301.905743
    10X1X21X21X11X111.900.756752
    11X1X21X21X112111.900.765743
    12X22X111X1XX1113.071.386752
    13X22X111X1X21113.071.413743
    14222222X11111114.778.596716
    15222222211111114.780.783707
    16121X2XXX1111121.170.777743
    17121X2XXX1111X11.170.778752
    18
    19
    20
    21
    22

    Sheet2





    Thank you in advance

    Regards,
    Kishan
    Last edited by Kishan; Jul 22nd, 2019 at 04:27 AM.

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Try this

    Code:
    =LEN($C6)-LEN(SUBSTITUTE($C6,F$5,""))

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Quote Originally Posted by theBardd View Post
    Try this

    Code:
    =LEN($C6)-LEN(SUBSTITUTE($C6,F$5,""))
    theBardd, yes the formula results are perfect for count of 1's, X's & 2's in the columns L:N, thank you so much for your help

    Now only require Index formula in column D


    Kind Regards,

    Kishan


  4. #4
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    This should do that

    Code:
    =SERIESSUM(LEN("1X2"), LEN(C6)-1,-1,--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6,1,0),"X",1),2,2),COLUMN(A1:N1),1))+1

  5. #5
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    A bit better

    Code:
    =SERIESSUM(LEN("1X2"), LEN(C6)-1,-1,--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6,1,0),"X",1),2,2),ROW(INDIRECT("1:"&LEN(C6))),1))+1

  6. #6
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Quote Originally Posted by theBardd View Post
    A bit better

    Code:
    =SERIESSUM(LEN("1X2"), LEN(C6)-1,-1,--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6,1,0),"X",1),2,2),ROW(INDIRECT("1:"&LEN(C6))),1))+1
    theBardd, absolutely amazing, formula work like a charm

    I appreciate your kind help
    Have a nice day


    Kind Regards,

    Kishan

  7. #7
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Thinking about it, an easier way may just to have been to split C6 into 14 separate cells, and use the original formulae.

    You can split by putting in

    Code:
    =IFERROR(--MID(C6,COLUMN(A1),1),MID(C6,COLUMN(A1),1))
    The IFERROR is to take care of the fact that some are numbers, some text.
    Last edited by theBardd; Jul 22nd, 2019 at 06:25 PM.

  8. #8
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Quote Originally Posted by theBardd View Post
    Thinking about it, an easier way may just to have been to split C6 into 14 separate cells, and use the original formulae.

    You can split by putting in

    Code:
    =IFERROR(--MID(C6,COLUMN(A1),1),MID(C6,COLUMN(A1),1))
    The IFERROR is to take care of the fact that some are numbers, some text.
    theBardd, I understand it can be the way easier, but I guess "IFERROR" function does not applied in excel 2000 so far the formula is not working

    Thank you for thinking about me.


    Kind Regards,

    Kishan


  9. #9
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Oh yes, sorry, I forgot you were in Excel 2000 (I have to say that if that is a company policy, that is immensely short-sighted, they are missing out on some great stuff).

    It can be done in Excel 2000, just more tedious

    Code:
    =IF(ISERROR(--MID($C6,COLUMN(A$1),1)),MID($C6,COLUMN(A$1),1),--MID($C6,COLUMN(A$1),1))

  10. #10
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Require changed of formulas as per new layout

    Quote Originally Posted by theBardd View Post
    Oh yes, sorry, I forgot you were in Excel 2000 (I have to say that if that is a company policy, that is immensely short-sighted, they are missing out on some great stuff).

    It can be done in Excel 2000, just more tedious

    Code:
    =IF(ISERROR(--MID($C6,COLUMN(A$1),1)),MID($C6,COLUMN(A$1),1),--MID($C6,COLUMN(A$1),1))
    theBardd, this is nice formula work like "Text To Columns" great of you cheers

    Thanks a lot have a good day.


    Kind Regards,

    Kishan


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
  •