Need A Formula.

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860
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
 

Some videos you may like

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
Joined
Jul 8, 2003
Messages
1,395
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?
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896

ADVERTISEMENT

Do the numbers always increase left to right ?
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896

ADVERTISEMENT

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
Joined
Jul 8, 2003
Messages
1,395
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)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,759
Members
410,704
Latest member
Cobber2008
Top