# Need A Formula.

#### CARBOB

##### Well-known Member
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
BCDEFGH
2481421271E2EO3O
310161825301E2EEO3E
4381720301EO2OE3E
51671734
61725323536
7913232529
8117183132
91316212829
1046112632
11417193436
12519243135
1315212334
Sheet1

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### babycody

##### Well-known Member
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?

yOU HAVE IT!

#### CARBOB

##### Well-known Member
Anyone have any suggestions?

#### SteveO59L

##### Well-known Member

Do the numbers always increase left to right ?

#### SteveO59L

##### Well-known Member

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

##### Well-known Member
I used two helper columns because of the limit on nested functions. You can hide the other two columns. HTH
Column F
Code:
``=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
Code:
``=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F1,"1","!",1),"2","~",1),"3","#",1),"1",""),"2",""),"3","")``
Column H
Code:
``=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G1,"!",1),"~","2"),"#",3)``

#### CARBOB

##### Well-known Member
It works, great, thank you!!!

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFGH
1
2481421271EE2EO3O
310161825301E2EE3OE
4381720301OE2OE3E
516717341OEO2O3E
617253235362O3OEOE
79132325291O2OO3OO
81171831321O2OE3OE
913162128292OEO3EO
10461126321EEO3EE
114171934361E2OO3EE
125192431351O2OE3OO
13152123341OO2OO3E
14
Groups

Replies
8
Views
63
Replies
7
Views
131
Replies
14
Views
106
Replies
3
Views
69
Replies
4
Views
101