number seperation

  • Thread starter Thread starter jzamilpa3
  • Start date Start date
J

jzamilpa3

i have this formula so i can seperate a set of number when needed. for
example a selected amount of diffrent numbers will equal a specific
number. i need to add more to this formula so i can have it
completed.

=IF(OR(--MID(B1,3,3)={31,32,33,34,85,86,139,140,141}),LOOKUP(--
MID(B1,3,1),­{1,3,8},{15,12,14}),IF(AND(--MID(B1,3,3)>213,--
MID(B1,3,3)<227),10,IF(OR(--­
MID(B1,3,3)={19,21,22,24,25,26,27,28,29,30,35}),13,IF(OR(AND(--
MID(B1,3,3)>­145,--MID(B1,3,3)<160),AND(--MID(B1,3,3)>200,--
MID(B1,3,3)<208)),6,IF(OR(AN­D(--MID(B1,3,3)>62,--
MID(B1,3,3)<79),AND(--MID(B1,3,3)>86,--MID(B1,3,3)<92)­,AND(--
MID(B1,3,3)>96,--MID(B1,3,3)<136)),11,1+INT((--MID(B1,3,3)-1)/
10))))­))

this the set of numbers. the ones on the right have to equal the ones
on the left.
1= 36,44,45,46
3= 37,38,39,54,55
5= 40-43
6= 47,48,49, 146-159, 201-210
7= 23, 83,84, 211
10= 212,214, 227
11= 57-82, 87-136, 163,167, 199, 213
12= 31-34
13= 21,22, 24-30, 160-197
14= 85,86
15= 139-141
16= 1,2,13,14,17,18,53
17= 3,4,15,16,20
19= 50,51,52
20= 19
22= 142-145

thanks for any help provided.
 
This would be a whole lot easier to do if you did it via VBA in code instead
of a formula. You'd have to have the code determine if the cell that was
last changed was one of the "number" cells. Then you'd do your analysis:

if Thatcell=36 then
Thatcell.column+1=1
elseif Thatcell=44 then
Thatcell.column+1=1
etc.....

You should be able to find examples for doing this in other postings. If
not, let me know and I can set you up...
 
no luck finding what i need. sorry.
i have a database on sheet1 that data is transfered into a diffrent
database on sheet2.
using vba coding.
on a column in sheet2 is where i need the formula or vba to make the
matchup
 
This is quite easy then. In the code when you put the data in sheet2, just
put this code in column x, where x is the column your number goes, assuming
the other number is to the left:

So you have your calculation in your code:

let x=1 'Or start with the new row # only
let Y=the column where the number is
let Z=the column where you want your calculation to go

do while true
if cells(x,y).value=empty then exit do
if cells(x,Y).value=36 or cells(x,y).value=44 or cells(x,y).value=45 or
cells(x,y).value=46 then
let cells(x,z).value=1
elseif cells(x,y).value=37 then '(and the other values for 3)
let cells(x,z).value=3
etc.etc.etc....
end if
x=x+1
Loop
 
i forgot to mention that there are words in tied with the numbers. the
2 letters in front the the number then several words after
 
No idea what you mean. Please give explicit example so I can follow which
number you're talking about.
 
ok well i have a set of numbers from 1 through 226 and i have broken
them down to a set where they have to equal a set between 1 and 22.

the numbers between 1 and 226 have a pp in front of them always.

pp124
pp23
pp78
pp90
 
So in the code I provided before the x=x+1 in the loop, just add this:
if cells(x,y).value>0 and cells(x,y).value<226 then
let cells(x,y).value="pp" & str(cells(x,y).value)
end if
 
I'd be interested in knowing what sort of thing this is your making.
What's it for?

Why can't you use Select Case somehow?

Case pp36,p44,pp45,pp46 : Cells(whatever) = 1
Case pp37,pp38,pp39,pp54,pp55 : Cells(whatever) = 3
and so on

JOhn
 
its a type of defect of a specific product or products.
there are so many types of defects that one can find (1-226) and from
that it gets narrowed down even more (1-22)
 
You must log in or register to reply here.
Back
Top