conditional formulas

[arvinduj]

Hi There

I have data in two Sheets

Can any one please help me to create this formula , I am struggling since last couple of months

Data

Sheet 1 column A contains unique records

A
b
c
d
e
f

Sheet 2 column A contains references
A ( Repeated 15 times)
b( Repeated 5 times)
Example :
column A Column B
B Y
B Y
B N
B Y
B Y
c( Repeated 8 times)
d ( repeated 71 times)
e( Repeated times )

and so on ......repeation of data in sheet2 is not fixed.
ColumnB in sheet 2 contains either "Y" or "N"


Requirement

I want a formula that will update column C in sheet 2 with a condition

a) Look into the Sheet1 refer 'B" check if "B" is there in sheet 2 and if column B in sheet 2 contains "N" and remaining "B" as "Y" then replace all such "Y" to "n" ( Lower case "n" only") . In case column B in Sheet 2 contains all "Y" keep the "Y" as it is

Thanks

Regards

Arvind

[starguy]

better it would be if you attach here a sample file.

[arvinduj]

Please find attachment for further details

[starguy]

Please find attachment for further details

I think you forgot to attach the file.

[arvinduj]

Hi

Please find attached the Sample data

I am eagerly waiting for the solution ........ please help!

[starguy]

Hi

Please find attached the Sample data

I am eagerly waiting for the solution ........ please help!

put formula in col C

=IF(AND(SUMPRODUCT(($A$3:$A$500="Intangible Assets")*($B$3:$B$500="N"))=1,ISERROR(MATCH(A53,Sheet1!$A$3:$A$20,0))=FALSE,D63),"n",B53="Y")

I have put this formula only in one cell, you can drag it down and see results.

hope this would return what you require, please let me know.

[arvinduj]

That's great! it is working fine ..

There is small error if this can be sorted out ...... the result is coming "True" instead of "Y" when all conditions in column B are "Y"

AND ALSO

The original "N" is changing to "n" in small case it should be capital "N" instead rest all "n" which are replacing "Y" are correct

Thanks in advance

You are really great ........ so quick to resolve

Thanks

Regards

Arvind

[starguy]

That's great! it is working fine ..

There is small error if this can be sorted out ...... the result is coming "True" instead of "Y" when all conditions in column B are "Y"

AND ALSO

The original "N" is changing to "n" in small case it should be capital "N" instead rest all "n" which are replacing "Y" are correct

Thanks in advance

You are really great ........ so quick to resolve

Thanks

Regards

Arvind

you're welcome and thank you for the feedback.

here is the formula and this time its a bit longer than previously used.

=IF(SUMPRODUCT(($A$3:$A$500="Intangible Assets")*($B$3:$B$500="N"))=0,B53,IF(AND(SUMPRODUCT(($A$3:$A$500="Intangible Assets")*($B$3:$B$500="N"))=1,ISERROR(MATCH(A53,Sheet1!$A$3:$A$20,0))=FALSE,B53="Y"),"n",B53))

and see the attached file.

[starguy]

Hi

more precise formula is here

put this in cell C53 of sheet2 and drag down

=IF(AND(SUMPRODUCT(($A$3:$A$500="Intangible Assets")*($B$3:$B$500="N"))=0,ISERROR(MATCH(A53,Sheet1!$A$3:$A$20,0))=FALSE),B53,IF(AND(SUMPRODUCT(($A$3:$A$500="Intangible Assets")*($B$3:$B$500="N"))=1,ISERROR(MATCH(A53,Sheet1!$A$3:$A$20,0))=FALSE,B53="Y"),"n",B53))

this will return the same values (Y or N) if Intangible Assets is not found in sheet1.
if found, it further checks that either all values in col B of sheet2 are Y against Intangible Assets if so, it will return Y in all cells, but if N is also found in col B, it will return n for Y and N for N.

hope this would be better solution.

[arvinduj]

Thanks a ton!

I don't have words express my feelings .......... must say you are Master of Excel

Regards


Arvind

[starguy]

Thanks a ton!

I don't have words express my feelings .......... must say you are Master of Excel

Regards


Arvind

you're welcome
and thank you for your feelings for me.
I'm not master as such.

thank you.