Require changed of formulas as per new layout

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
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
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
Cell Formulas
RangeFormula
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
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</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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</SPAN></SPAN>

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

Kind Regards,
</SPAN></SPAN>
Kishan :)
</SPAN></SPAN>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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</SPAN></SPAN>

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

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>:biggrin:
 
Upvote 0
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),[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.
 
Last edited:
Upvote 0
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),[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>
 
Upvote 0
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))
 
Upvote 0
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 </SPAN></SPAN>

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

Kind Regards,
</SPAN></SPAN>
Kishan :)
</SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top