Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Fill a blank cell with a value using if conditions

I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;

Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times

A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4,393
Default Fill a blank cell with a value using if conditions

Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M"))+1)/2),"")
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"rjagathe" wrote in message
...
I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;

Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times

A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Fill a blank cell with a value using if conditions

On Dec 17, 10:38*pm, "Bernard Liengme"
wrote:
Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"")
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP

"rjagathe" wrote in message

...



I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " *condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
*I have given an illustration below;


Excel Sheet contains data as follows:
* * * A * *B * *C * D
*1 * S * *M * *1
*2 * B * *F * * 1
*3 * M * F * * *2
*4 * S * *M * * 1
*5 * B * *M * *1
6 * S * * M * * 1
7 * M * *F * * * 2
8 * *S * * M * * 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th *row with a value "True1" for 2 times


* * * A * *B * *C * D
*1 * S * *M * *1 * *True1
*2 * B * *F * * 1
*3 * M * F * * *2
*4 * S * M * *1 * *True1
*5 * B * *M * *1
6 * S * * M * * 1
7 * M * *F * * * 2
8 * *S * * M * * 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th *rows with a value "True2"
for 2 times as shown below:


* * A * *B * *C * *D
1 * S * *M * *1 * *True1
2 * B * *F * * 1
3 * M * *F * *2
4 * S * *M * *1 * *True1
5 * B * *M * *1
6 * S * *M * *1 * *True2
7 * M * F * * *2
8 * S * *M * *1 * *True2


Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,934
Default Fill a blank cell with a value using if conditions

The formula works for me. Make sure you didn't pick up a Line Feed character
at the end of the formula when you copied it... after you paste the formula
into the Formula Bar, it should occupy only one line... if you see the text
cursor on a different line, then you picked up a Line Feed and the will make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).

--
Rick (MVP - Excel)


"rjagathe" wrote in message
...
On Dec 17, 10:38 pm, "Bernard Liengme"
wrote:
Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M
"))+1)/2),"")
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP

"rjagathe" wrote in message

...



I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;


Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2


Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Fill a blank cell with a value using if conditions

On Dec 18, 11:17*pm, "Rick Rothstein"
wrote:
The formula works for me. Make sure you didn't pick up a Line Feed character
at the end of the formula when you copied it... after you paste the formula
into the Formula Bar, it should occupy only one line... if you see the text
cursor on a different line, then you picked up a Line Feed and the will make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).

--
Rick (MVP - Excel)

"rjagathe" wrote in message

...
On Dec 17, 10:38 pm, "Bernard Liengme"
wrote:





Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M
"))+1)/2),"")
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP


"rjagathe" wrote in message


...


I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;


Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2


Sir,
* * I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.


Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,934
Default Fill a blank cell with a value using if conditions

I'm not sure what to tell you... the formula produces True1 and True2 in the
correct locations using the sample data you posted... I can find no way for
that formula to produce True0... did you copy/paste the formula that Bernard
posted or did you try to type it in? If you tried to type it in, I'm
guessing you did so incorrectly... try copy/pasting the formula instead.

--
Rick (MVP - Excel)


"rjagathe" wrote in message
...
On Dec 18, 11:17 pm, "Rick Rothstein"
wrote:
The formula works for me. Make sure you didn't pick up a Line Feed
character
at the end of the formula when you copied it... after you paste the
formula
into the Formula Bar, it should occupy only one line... if you see the
text
cursor on a different line, then you picked up a Line Feed and the will
make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).

--
Rick (MVP - Excel)

"rjagathe" wrote in message

...
On Dec 17, 10:38 pm, "Bernard Liengme"
wrote:





Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M
"))+1)/2),"")
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP


"rjagathe" wrote in message


...


I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;


Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2


Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.


Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Fill a blank cell with a value using if conditions

On Dec 20, 1:33*pm, "Rick Rothstein"
wrote:
I'm not sure what to tell you... the formula produces True1 and True2 in the
correct locations using the sample data you posted... I can find no way for
that formula to produce True0... did you copy/paste the formula that Bernard
posted or did you try to type it in? If you tried to type it in, I'm
guessing you did so incorrectly... try copy/pasting the formula instead.

--
Rick (MVP - Excel)

"rjagathe" wrote in message

...
On Dec 18, 11:17 pm, "Rick Rothstein"





wrote:
The formula works for me. Make sure you didn't pick up a Line Feed
character
at the end of the formula when you copied it... after you paste the
formula
into the Formula Bar, it should occupy only one line... if you see the
text
cursor on a different line, then you picked up a Line Feed and the will
make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).


--
Rick (MVP - Excel)


"rjagathe" wrote in message


....
On Dec 17, 10:38 pm, "Bernard Liengme"
wrote:


Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M
"))+1)/2),"")
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP


"rjagathe" wrote in message


....


I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;


Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2


Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.


Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.


I am using office 2007.I have copy -pasted the formula.Will it work in
office 2007.May be that is the problem.
  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Fill a blank cell with a value using if conditions

On Dec 20, 4:26*pm, rjagathe wrote:
On Dec 20, 1:33*pm, "Rick Rothstein"





wrote:
I'm not sure what to tell you... the formula produces True1 and True2 in the
correct locations using the sample data you posted... I can find no way for
that formula to produce True0... did you copy/paste the formula that Bernard
posted or did you try to type it in? If you tried to type it in, I'm
guessing you did so incorrectly... try copy/pasting the formula instead..


--
Rick (MVP - Excel)


"rjagathe" wrote in message


....
On Dec 18, 11:17 pm, "Rick Rothstein"


wrote:
The formula works for me. Make sure you didn't pick up a Line Feed
character
at the end of the formula when you copied it... after you paste the
formula
into the Formula Bar, it should occupy only one line... if you see the
text
cursor on a different line, then you picked up a Line Feed and the will
make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).


--
Rick (MVP - Excel)


"rjagathe" wrote in message


....
On Dec 17, 10:38 pm, "Bernard Liengme"
wrote:


Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M
"))+1)/2),"")
best wishes
--
Bernard Liengmehttp://people.stfx.ca/bliengme
Microsoft Excel MVP


"rjagathe" wrote in message


....


I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;


Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2


Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.


Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.


I am using office 2007.I have copy -pasted the formula.Will it work in
office 2007.May be that is the problem.

I found the bug .There is an unnecessary space $B$1:B1="M ".Now it
works fine.
As an improvement to this formula ,I want "5n' number of occurrences
(not just 2) in the results showing True 1 and True 2 and so on.That
is I may want 5, "True1" , 15 "True2" and 55 "True3".Pl help.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do include a (blank) cell within a Custom Fill List? VickiMc Excel Discussion (Misc queries) 0 October 2nd 08 02:32 AM
returning blank cell when conditions not met kfarley Excel Worksheet Functions 4 March 17th 08 11:24 PM
Automatically fill blank cell by same as just above record [email protected] Excel Discussion (Misc queries) 2 March 3rd 07 06:24 PM
Fill column blanks from last non-blank cell C. BROWN Excel Discussion (Misc queries) 2 August 28th 06 08:36 PM
Fill cell that is blank Slashman Excel Worksheet Functions 7 August 28th 06 01:30 AM


All times are GMT +1. The time now is 02:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"