Another way to write this

Stephen_IV · Jan 7, 2011

Good afternoon, I was just wondering is there another way to write this? Thanks in advance Stephen!

=IF(AND(E2="Y",D2="N",C2="N"),1,"")
or
=IF((E2="Y")*(D2="N")*(C2="N"),1,"")

MrKowz · Jan 7, 2011

Three ways I thought of off the top of my head:

Excel Workbook

C

D

E

F

G

2

N

Y

1

3

1

4

1

Name

Excel 2003

Cell Formulas
Range	Formula
G2	=IF(E2&D2&C2="YNN",1,"")
G3	=--(E2&D2&C2="YNN")
G4	=(E2="Y")(D2="N")(C2="N")

Stephen_IV · Jan 7, 2011

Thanks MrKowz,
Thanks for your responses 1 I never thought of. I should have said that I had 40 columns of data.

I have a ton of columns (40 to be exact) with Y and N, I need to lookup different sequences within the 40 columns. Can you think of any other?

MrKowz · Jan 7, 2011

With that amount of data, might be a good idea to write a small UDF that handles it.

If you can describe to me what you are doing, what columns you are using, what combinations should return what, etc... I could give you some good solutions.

Stephen_IV · Jan 7, 2011

Thanks MrKowz,

This is based on survey data, the sequences I need are below. What we are trying to do is to find the sequences to the questions that best fit our companies needs and then put an X at the end of those who match the sequence. There are about 650,000. Looking for a formula or code to do this. Our range is (A:AN).

Thanks in advance

Below are a couple of sequences

Code:

n, n, y, y, n, y, y, y, n, y, n, n, y, y, y, y, y, y, n, n, y, n, y, y, y, y, y, y, y, y, n, n, n, y, y, y, n, n, y, n

Code:

y, y, y, y, n, y, y, y, n, y, n, n, y, y, y, y, y, y, n, n, y, n, y, y, y, y, y, y, y, y, n, y, n, y, y, y, n, n, y, n

MrKowz · Jan 7, 2011

Give this function a shot, I tweaked it from another UDF I had that concatenates an entire range:

Code:

Public Function SurveyResults(rng As Range, seq1 As String, seq2 As String)
   Dim rRng As Range
   Dim Concatrange As String
   Dim delimiter As String
   delimiter = ", "
   For Each rRng In rng
      If rRng.Value <> "" Then
         Concatrange = Concatrange & rRng.Value & delimiter
      End If
   Next rRng
   
   If Len(Concatrange) > 0 Then
      Concatrange = Left(Concatrange, Len(Concatrange) - Len(delimiter))
   Else
      Concatrange = ""
   End If
   
   If Concatrange = seq1 Or Concatrange = seq2 Then
      SurveyResults = "X"
   Else
      SurveyResults = ""
   End If
   
End Function

Use it like:

=SurveyResults(A1:AN1,"n, n, y, y, n, y, y, y, n, y, n, n, y, y, y, y, y, y, n, n, y, n, y, y, y, y, y, y, y, y, n, n, n, y, y, y, n, n, y, n","y, y, y, y, n, y, y, y, n, y, n, n, y, y, y, y, y, y, n, n, y, n, y, y, y, y, y, y, y, y, n, y, n, y, y, y, n, n, y, n")

If you need more possible sequences, they can easily be added in the UDF.

Jonmo1 · Jan 7, 2011

Try this

=--(SUMPRODUCT(--(C2:E2=C3:E3))=COUNTA(C3:E3))

C2:E2 is the sequence to check
C3:E3 is the desired Sequence.

Stephen_IV · Jan 10, 2011

Thanks guys,
I had a couple days to digest this stuff and both of your solutions worked. The formula below taken from jonmo1 (again this is just test data) I know that I have 40 columns, is there a way to circumvent using sumproduct twice?

=IF((SUMPRODUCT(--(C2:E2={"N","N","Y"}))=COLUMNS(C2:E2))+(SUMPRODUCT(--(C2:E2={"Y","N","Y"}))=COLUMNS(C2:E2)),"x","") this formula works

Domenic did something like this but his was Sumproduct(Isnumber(Match(Choose({1,2},A1:A9,B1:B9),{1,2,3},0)))

I tried to modify his formula into mine and did not come up with the write answer,
=IF(SUMPRODUCT(--ISNUMBER(MATCH(C2:E2,(CHOOSE({1,2},{"N","N","Y"},{"Y","N","Y"})),0))),"x","")

Book2

A

B

C

D

E

F

G

1

SPED

S504

LEP

2

N

Y

x

3

N

Y

x

4

N

Y

N

x

5

Y

6

Y

N

Y

x

Sheet1

Can anyone please give me some support. Thanks in advance!

Domenic · Jan 11, 2011

Try...

G2, copied down:

=IF(SUMPRODUCT(--(MMULT(({"N","N","Y";"Y","N","Y"}=C2:E2)+0,{1;1;1})=3)),"x","")

or

=IF(SUM(IF(MMULT(($J$2:$L$3=C2:E2)+0,TRANSPOSE(COLUMN(C2:E2)^0))=3,1)),"x","")

...where J2:L3 contains...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=64>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Y</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Y</TD></TR></TBODY></TABLE>

Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Stephen_IV · Jan 11, 2011

Domenic perfect! Thank you again. You are blessed with knowledge that you have. Amazing!

Another way to write this

Stephen_IV

Well-known Member

Excel Facts

MrKowz

Well-known Member

Stephen_IV

Well-known Member

MrKowz

Well-known Member

Stephen_IV

Well-known Member

MrKowz

Well-known Member

Jonmo1

MrExcel MVP

Stephen_IV

Well-known Member

Domenic

MrExcel MVP

Stephen_IV

Well-known Member

Similar threads

Forum statistics

Share this page

Another way to write this

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock