Thread: Require changed of formulas as per new layout Thanks:  3 Post #5313200 (1)Post #5313514 (1)Post #5313039 (1) Likes:  3 Post #5313200 (1)Post #5313514 (1)Post #5313039 (1)

1. 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

Regards,
Kishan

2. Re: Require changed of formulas as per new layout

Try this

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

3. Re: Require changed of formulas as per new layout

Originally Posted by theBardd
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. 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. 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. Re: Require changed of formulas as per new layout

Originally Posted by theBardd
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

Have a nice day

Kind Regards,

Kishan

7. 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.

8. Re: Require changed of formulas as per new layout

Originally Posted by theBardd
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. 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. Re: Require changed of formulas as per new layout

Originally Posted by theBardd
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