Require changed of formulas as per new layout

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

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

Current Formulas</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4																			Count	Count	Count
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	Index		1	X	2
6			1	1	1	1	1	1	1	X	X	X	X	X	X	X	1.094		7	7	0
7			1	1	1	1	1	1	1	X	X	X	X	X	X	2	1.095		7	6	1
8			1	1	X	2	1	X	1	1	1	X	1	X	2	X	301.904		7	5	2
9			1	1	X	2	1	X	1	1	1	X	1	X	2	2	301.905		7	4	3
10			X	1	X	2	1	X	2	1	X	1	1	X	1	1	1.900.756		7	5	2
11			X	1	X	2	1	X	2	1	X	1	1	2	1	1	1.900.765		7	4	3
12			X	2	2	X	1	1	1	X	1	X	X	1	1	1	3.071.386		7	5	2
13			X	2	2	X	1	1	1	X	1	X	2	1	1	1	3.071.413		7	4	3
14			2	2	2	2	2	2	X	1	1	1	1	1	1	1	4.778.596		7	1	6
15			2	2	2	2	2	2	2	1	1	1	1	1	1	1	4.780.783		7	0	7
16			1	2	1	X	2	X	X	X	1	1	1	1	1	2	1.170.777		7	4	3
17			1	2	1	X	2	X	X	X	1	1	1	1	X	1	1.170.778		7	5	2
18
19
20
21
Sheet1

Cell Formulas
Range	Formula
S6	=COUNTIF(C6:P6,1)
T6	=COUNTIF(C6:P6,"X")
U6	=COUNTIF(C6:P6,2)
Q6	{=SeriesSum(LEN("1X2"), COLUMNS(C6:P6)-1, -1, SEARCH(C6:P6, "1X2") - 1)+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

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

Require formula change as per new format...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4						Count	Count	Count
5			Data With No Space	Index		1	X	2
6			1111111XXXXXXX	1.094		7	7	0
7			1111111XXXXXX2	1.095		7	6	1
8			11X21X111X1X2X	301.904		7	5	2
9			11X21X111X1X22	301.905		7	4	3
10			X1X21X21X11X11	1.900.756		7	5	2
11			X1X21X21X11211	1.900.765		7	4	3
12			X22X111X1XX111	3.071.386		7	5	2
13			X22X111X1X2111	3.071.413		7	4	3
14			222222X1111111	4.778.596		7	1	6
15			22222221111111	4.780.783		7	0	7
16			121X2XXX111112	1.170.777		7	4	3
17			121X2XXX1111X1	1.170.778		7	5	2
18
19
20
21
22
Sheet2

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[theBardd]

Try this

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

 

[Kishan]

Try this

=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</SPAN></SPAN>

Now only require Index formula in column D </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[theBardd]

This should do that

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

 

[theBardd]

A bit better

=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

 

[Kishan]

A bit better

=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</SPAN></SPAN>

I appreciate your kind help </SPAN></SPAN>
Have a nice day </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[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

=IFERROR(--MID(C6,COLUMN(A1),1),[COLOR=#222222][FONT=Verdana]MID(C6,COLUMN(A1),1))[/FONT][/COLOR]

The IFERROR is to take care of the fact that some are numbers, some text.

 

[Kishan]

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

=IFERROR(--MID(C6,COLUMN(A1),1),[COLOR=#222222][FONT=Verdana]MID(C6,COLUMN(A1),1))[/FONT][/COLOR]

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 </SPAN></SPAN>

Thank you for thinking about me.</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[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

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

 

[Kishan]

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

=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 </SPAN></SPAN>

Thanks a lot have a good day.</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>