asked on

Excel 2013

I want to create formula to check that in each row if the data is all the same in each cell then it should say "all Ok" if not then should say need attention. I want to check cell from column D through N in each row. Example

2 2 2 2 2 2 2 2 2 All ok
S I S S S S S S S Need attention

How I can do that. thanks

Rgonzo1971

Hi,

pls try

=IF((SUMPRODUCT(--(FREQUENCY(MATCH(D2:N2,D2:N2,0),COLUMN(D2:N2)-COLUMN(D2)+1)>0)))=1,"OK","Need attention")

Open in new window

Regards
EE20170131.xlsx

snhandle

ASKER

When there is blank cell in between cells it gives me error #N/A. If there is any blank cell it should still say "need attention". But if all the cells are blank then it should say "Ok".

thanks

Shums Faruk

Just add Iferror

=IFERROR(IF((SUMPRODUCT(--(FREQUENCY(MATCH(D2:N2,D2:N2,0),COLUMN(D2:N2)-COLUMN(D2)+1)>0)))=1,"OK","Need attention"),"")

Open in new window

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

=IF((SUM(--(FREQUENCY(IF(D1:N1<>"",MATCH(D1:N1,D1:N1,0)),COLUMN(D1:N1)-COLUMN(D1)+1)>0)))<=1,"OK","to be examined")

Open in new window

Confirm with Ctrl+Shift+Enter.
EE20170131.xlsx

Subodh Tiwari (Neeraj)

Disregard my solution, I didn't read your later post.

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Surely simple as:

=IF(AND(D1=E1,E1=F1,F1=G1,G1=H1,H1=I1,J1=K1,K1=L1,L1=M1,M1=N1),"OK","Needs attention")

Thanks
Rob

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

This seems to work. It's an array formula so use CTL-SHIFT-ENTER after entering.

=IF(AND(D1:M1=E1:N1),"All OK","Needs Attention")

Open in new window

~bp

Rob Henson

To allow for case sensitivity:

=IF(AND(EXACT(D2,E2),EXACT(E2,F2),EXACT(F2,G2),EXACT(G2,H2),EXACT(H2,I2),EXACT(I2,J2),EXACT(J2,K2),EXACT(K2,L2),EXACT(L2,M2),EXACT(M2,N2)),"OK","Needs attention")

psteff

Here's another solution:

=IF(EXACT(CONCATENATE(D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1),CONCATENATE(D1,D1,D1,D1,D1,D1,D1,D1,D1,D1,D1)),"All ok","Need Attention")

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial