Need A Formula

[CARBOB]

Is there a way to have the 3 values in Col's B:D in one cell, Col E, where the value in Col E will be treated as a value. When I use this method, =(B28&C28&D28) my formula will not treat them as values, get a #NUM.

Excel Workbook
	B	C	D	E
28	4	9	4	494
29	5	8	1	581
30	1	4	7	147
31	0	1	3	013
DIGIT REPEATS

Excel 2007

Cell Formulas
Range	Formula
E28	=(B28&C28&D28)
E29	=(B29&C29&D29)
E30	=(B30&C30&D30)
E31	=(B31&C31&D31)

 

[VoG]

Try

=(B28&C28&D28)+0

 

[CARBOB]

Try

=(B28&C28&D28)+0

I tried this, then formatted the column to 000, I still get error.

Excel Workbook
	B	C	D	E	F
28	4	9	4	494	0D
29	5	8	1	581	0S
30	1	4	7	147	0S
31	0	1	3	013	##
DIGIT REPEATS

Excel 2007

Cell Formulas
Range	Formula
E28	=(B28&C28&D28)+0
E29	=(B29&C29&D29)+0
E30	=(B30&C30&D30)+0
E31	=(B31&C31&D31)+0
F28	=SUMPRODUCT(--(B29:D29=B28:D28))&IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},MID($E28,{1,2,3},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($E28,{1,2,3},1)+0)+{0,1,2,3},MID($E28,{1,2,3},1)+0,0)))),"C",IF(SUM(IF(FREQUENCY(MID($E28,{1,2,3},1)+0,MID($E28,{1,2,3},1)+0)=2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($E28,{1,2,3},1)+0,MID($E28,{1,2,3},1)+0)))))
F29	=SUMPRODUCT(--(B30:D30=B29:D29))&IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},MID($E29,{1,2,3},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($E29,{1,2,3},1)+0)+{0,1,2,3},MID($E29,{1,2,3},1)+0,0)))),"C",IF(SUM(IF(FREQUENCY(MID($E29,{1,2,3},1)+0,MID($E29,{1,2,3},1)+0)=2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($E29,{1,2,3},1)+0,MID($E29,{1,2,3},1)+0)))))
F30	=SUMPRODUCT(--(B31:D31=B30:D30))&IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},MID($E30,{1,2,3},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($E30,{1,2,3},1)+0)+{0,1,2,3},MID($E30,{1,2,3},1)+0,0)))),"C",IF(SUM(IF(FREQUENCY(MID($E30,{1,2,3},1)+0,MID($E30,{1,2,3},1)+0)=2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($E30,{1,2,3},1)+0,MID($E30,{1,2,3},1)+0)))))
F31	=SUMPRODUCT(--(B32:D32=B31:D31))&IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},MID($E31,{1,2,3},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($E31,{1,2,3},1)+0)+{0,1,2,3},MID($E31,{1,2,3},1)+0,0)))),"C",IF(SUM(IF(FREQUENCY(MID($E31,{1,2,3},1)+0,MID($E31,{1,2,3},1)+0)=2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($E31,{1,2,3},1)+0,MID($E31,{1,2,3},1)+0)))))

 

[VoG]

I don't pretend to understand what the SUMPRODUCT formulas are meant to do but the concatenation +0 results in numeric values

Excel Workbook
	B	C	D	E	F
28	4	9	4	494	TRUE
29	0	1	3	013	TRUE
Sheet6

 

[CARBOB]

I don't pretend to understand what the SUMPRODUCT formulas are meant to do but the concatenation +0 results in numeric values

Excel Workbook
	B	C	D	E	F
28	4	9	4	494	TRUE
29	0	1	3	013	TRUE
Sheet6

This part counts the number of digits that repeat from the previous row.
=SUMPRODUCT(--(B29:D29=B28:D28))



This part identifies the 3-digit value Col E as S(Single, (236); D(Double,022);C(Consecutive with 0 and 9 considered consecutive; T( Triple digits,222). The formula also used in a 4-digit game, where the other alpha characters come into play. DD( double digits, 2244), Q( Quads, 1111,2222)

IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},MID($E28,{1,2,3},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($E28,{1,2,3},1)+0)+{0,1,2,3},MID($E28,{1,2,3},1)+0,0)))),"C",IF(SUM(IF(FREQUENCY(MID($E28,{1,2,3},1)+0,MID($E28,{1,2,3},1)+0)=2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($E28,{1,2,3},1)+0,MID($E28,{1,2,3},1)+0)))))

 

[VoG]

The SUMPRODUCT works for me with your original concatenation formula

Excel Workbook
	B	C	D	E	F
28	4	9	4	494	0D
29	5	8	1	581	0S
30	1	4	7	147	0S
31	0	1	3	013	1S
Sheet6

 

[CARBOB]

Have no idea what happen, but everything appears to work, now. Thanks for the help!!!