Need A Formula.

[CARBOB]

I need a formula for Cell G2 that will read the digits in Col's B2:F2 and tell what group they came from. It's possible to have all 5 digits come from one group. I hope you will be able to understand what I'm trying to accomplish. I entered some examples that the results should like, I have no idea where to start with this. Thanks for all suggestions.
Carbob
PS: I just caught an error G2, it should be 1EE2EO3O

Group 1
1, 2, 3,  4,  5,  6,  7,  8,  9,  10,  11,  12   Even  2, 4, 6, 8, 10, 12  ODD 1, 3, 5, 7, 9, 11

Group 2
13 ,14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24  Even  14, 16, 18, 20, 22, 24  ODD  15. 17, 19, 21, 23

Group 3
25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36 Even 28, 30, 32, 34, 36, ODD 27, 29, 31, 33, 35

F5 VTRACS V1.xls
	B	C	D	E	F	G	H
2	4	8	14	21	27	1E2EO3O
3	10	16	18	25	30	1E2EEO3E
4	3	8	17	20	30	1EO2OE3E
5	1	6	7	17	34
6	17	25	32	35	36
7	9	13	23	25	29
8	1	17	18	31	32
9	13	16	21	28	29
10	4	6	11	26	32
11	4	17	19	34	36
12	5	19	24	31	35
13	1	5	21	23	34
Sheet1

 

[babycody]

Can you break down what this represents 1E2EO3O? Are the 1, 2, and 3 the groups? O and E are Odd and Even? Wouldn't it be 1EE2EO3O?

 

[CARBOB]

yOU HAVE IT!

 

[CARBOB]

Anyone have any suggestions?

 

[SteveO59L]

Do the numbers always increase left to right ?

 

[CARBOB]

Do the numbers always increase left to right ?

Yes!

 

[SteveO59L]

Hmm,, this way the formula is going to be huge. The following only checks the first two entries.

=IF(B2<13,1,IF(B2<25,2,3))&IF(MOD(B2,2)=1,"O","E")&IF(AND(B2<13,C2<13),IF(MOD(C2,2)=1,"O","E"),IF(C2<13,1,IF(C2<25,2,3))&IF(MOD(C2,2)=1,"O","E"))

You might be better off checking each entry, then concatenating the results.

So say in h2, to check b2
=IF(B2<13,1,IF(B2<25,2,3))&IF(MOD(B2,2)=1,"O","E")

and in i2, to check c2
IF(AND(B2<13,C2<13),IF(MOD(C2,2)=1,"O","E"),IF(C2<13,1,IF(C2<25,2,3))&IF(MOD(C2,2)=1,"O","E"))


then in g2,

H2&I2&.....

to combine the results

 

[babycody]

I used two helper columns because of the limit on nested functions. You can hide the other two columns. HTH

Book4
	A	B	C	D	E	F	G	H
1	4	8	14	21	27	1E1E2E2O3O	!EE~EO#O	1EE2EO3O
2	10	16	18	25	30	1E2E2E3O3E	!E~EE#OE	1E2EE3OE
3	3	8	17	20	30	1O1E2O2E3E	!OE~OE#E	1OE2OE3E
4	1	6	7	17	34	1O1E1O2O3E	!OEO~O#E	1OEO2O3E
5	17	25	32	35	36	2O3O3E3O3E	~O#OEOE	2O3OEOE
6	9	13	23	25	29	1O2O2O3O3O	!O~OO#OO	1O2OO3OO
7	1	17	18	31	32	1O2O2E3O3E	!O~OE#OE	1O2OE3OE
8	13	16	21	28	29	2O2E2O3E3O	~OEO#EO	2OEO3EO
9	4	6	11	26	32	1E1E1O3E3E	!EEO#EE	1EEO3EE
10	4	17	19	34	36	1E2O2O3E3E	!E~OO#EE	1E2OO3EE
11	5	19	24	31	35	1O2O2E3O3O	!O~OE#OO	1O2OE3OO
12	1	5	21	23	34	1O1O2O2O3E	!OO~OO#E	1OO2OO3E
Sheet1

Column F

=IF(A1<13,1,IF(A1<25,2,IF(A1>24,3,"NO MATCH")))&IF(ISEVEN(A1),"E","O")&IF(B1<13,1,IF(B1<25,2,IF(B1>24,3,"NO MATCH")))&IF(ISEVEN(B1),"E","O")&IF(C1<13,1,IF(C1<25,2,IF(C1>24,3,"NO MATCH")))&IF(ISEVEN(C1),"E","O")&IF(D1<13,1,IF(D1<25,2,IF(D1>24,3,"NO MATCH")))&IF(ISEVEN(D1),"E","O")&IF(E1<13,1,IF(E1<25,2,IF(E1>24,3,"NO MATCH")))&IF(ISEVEN(E1),"E","O")

Column G

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F1,"1","!",1),"2","~",1),"3","#",1),"1",""),"2",""),"3","")

Column H

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G1,"!",1),"~","2"),"#",3)

 

[CARBOB]

It works, great, thank you!!!

 

[Peter_SSs]

G'day CARBOB, haven't seen you around here for a while.

Some pretty heavy-going formulas there. Would you consider a VBA solution? If so, try this. (I have assumed no numbers are greater than 36, though if there is any, my code would allocate them to group 4, 5 etc depending on the multiple of 12.) This code needs to be manually activated, but it could be automated by using the Worksheet_Change event.<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Groups()
    <SPAN style="color:#00007F">Dim</SPAN> Result<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    r = 2
    Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">While</SPAN> Cells(r, 2).Value<> ""
        Result = 1 + Int((Cells(r, 2).Value - 1) / 12)
        <SPAN style="color:#00007F">If</SPAN> Cells(r, 2).Value Mod 2 = 0<SPAN style="color:#00007F">Then</SPAN>
            Result = Result & "E"
        <SPAN style="color:#00007F">Else</SPAN>
            Result = Result & "O"
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">For</SPAN> c = 3<SPAN style="color:#00007F">To</SPAN> 6
            <SPAN style="color:#00007F">If</SPAN> InStr(Result, 1 + Int((Cells(r, c).Value - 1) / 12)) = 0<SPAN style="color:#00007F">Then</SPAN>
                Result = Result & 1 + Int((Cells(r, c).Value - 1) / 12)
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Cells(r, c).Value Mod 2 = 0<SPAN style="color:#00007F">Then</SPAN>
                Result = Result & "E"
            <SPAN style="color:#00007F">Else</SPAN>
                Result = Result & "O"
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
        Cells(r, 7).Value = Result
        r = r + 1
    <SPAN style="color:#00007F">Loop</SPAN>
    Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Here is my result using this code:

Mr Excel.xls
	A	B	C	D	E	F	G	H
1
2		4	8	14	21	27	1EE2EO3O
3		10	16	18	25	30	1E2EE3OE
4		3	8	17	20	30	1OE2OE3E
5		1	6	7	17	34	1OEO2O3E
6		17	25	32	35	36	2O3OEOE
7		9	13	23	25	29	1O2OO3OO
8		1	17	18	31	32	1O2OE3OE
9		13	16	21	28	29	2OEO3EO
10		4	6	11	26	32	1EEO3EE
11		4	17	19	34	36	1E2OO3EE
12		5	19	24	31	35	1O2OE3OO
13		1	5	21	23	34	1OO2OO3E
14
Groups