# 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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### 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
1
Views
93
Replies
5
Views
131
Replies
4
Views
111
Replies
1
Views
87
Replies
2
Views
126

1,141,920
Messages
5,709,349
Members
421,631
Latest member
JDamery

### 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.

### Which adblocker are you using?

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

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