if statement - text / blank cell

  • Thread starter Thread starter jester
  • Start date Start date
J

jester

I have 3 columns a,b,c. col. A is the result columm.
column b and c will have text in one column and the other
is blank. I am trying to get an if statement to put a
number referring to the cell in the result column. The
text in columns b and c CHANGE week to week.

a(result b c
smith(1)
jones(4)

In the example I am trying to get result column to return
a "1" because smith is in the first cell position. The
jones cell is in the fourth cell position, so I want "4"
to show in the result (a) column. Likewise if jones was in
the "b" column i would want it to return a 3 in the "a"
column.

Is it possible to combine an if statement for b and c rows
to get a result in col. a.? Is there a wild card that
can be used if a cell has text in it?
EX: if(b1=*text*,1,"") - can the same statement for c1 be
combined with the b1 statement?

I hope this makes sense. I tried to explain as best as I
could. I haven't used any other functions/statements
before. If I have to use a macro or something please
explain where to enter it in excell.

A whole bunch of thanks in advance.
 
Copy the following code:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: http://makeashorterlink.com/?P20022174
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.

Let B1:C3 house the following sample:

{"Smith","";"","Jones";"Jon",""}

where "" stands for an empty cell. It is essential to note (as per your
spec) that each record houses one text value at most.

A formula that you can have in A1 is:

=MATCH(REPT("z",255),arrayunion($B$1:C1))

which must be confirmed with control+shift+enter instead of just with enter.
 
Reading Aladins reply, I could well be missing the point big time here, so
apologies if that is the case, but the way I read your question was that for
each of the two results 'Smith and Jones' whose Text will change regularly but
will appear in at least one of the two cells - (But how - Is it typed in, or the
result of a formula), they could appear in either col B & C. Example references
could be that you want the result in A1, and the text could be in either B2 or
C2 and B3 or C3. Are these the only cells this applies to?? These cells have a
numerical significance to you as follows:-

B2 = 1
C2 = 2
B3 = 3
C3 = 4

Depending on what cells the text appears in, you then simply want the total of
those two cells??

If this is all you require, then the two formulas below would do that
individually:-

=IF(B2="",2,1)
=IF(B3="",4,3)

and combined would be just

=IF(B2="",2,1)+IF(B3="",4,3)
 
Aladin Akyurek said:
Copy the following code:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
Click to expand...
....

Quibble: ReDim Preserve is a slow executing statement. Use as few as
possible rather than one or more per loop. One possibility would be using an
initial ReDim call to size TempUnion the same as Arg, then double the size
of TempUnion each time you bump into its upper bound, then resize to Ctr
after the outer For loop ends.
Let B1:C3 house the following sample:

{"Smith","";"","Jones";"Jon",""}

where "" stands for an empty cell. It is essential to note (as per your
spec) that each record houses one text value at most.

A formula that you can have in A1 is:

=MATCH(REPT("z",255),arrayunion($B$1:C1))
Click to expand...
....

?

Maybe =MATCH("Smith",ArrayUnion($B$1:$C$3),0)

But for a problem like the OP's, a udf isn't needed for a general solution.
If the range of names were named X, and the string sought ("smith") were
named Y, the following would return its position.

=SUMPRODUCT(--(LEFT(X,LEN(Y))=Y),(ROW(X)-CELL("Row",X))*COLUMNS(X)
+COLUMN(X)-CELL("Col",X)+1)
 
In A1 put in this formula, then drag fill down column A

=IF(OR(B1<>"",C1<>""),IF(B1<>0,(ROW()-1)*2+1,(ROW()-1)*2+2),"")
 
The info in columns b and c are typed in every week, and
do change. Also, the info could be in either column b or c.

I actually have around 10 rows under the columns b and c
I want the result column (a) to return a number based on
where the text is in the cell. I think you may have it
right. I don't have time to check it today, I will try
tomorrow. Also, I am not trying to total the results of
column a I am just trying to automate that column so I
don't have to type those numbers in each week.

here is another example:

a(result) b c
result here of 1 0r 2 (1) smith
result here of 3 or 4 (4) jones
result here of 5 or 6 (6) green
7 or 8
9 or 10 .............

This continues for around 10 to 12 rows. I assume the
formula would be entered in column (a) each row. When I
was trying to figure it before the if statement gave me a
blank if the cell had the text in as well if it was
blank. I probably didn't have the formula right.

I'll try your formula tomorrow. If, by my new example
there is something different let me know. Thanks again
for your help.



---Original Message-----
 
Back
Top