Look/Match specific text based on similar column criteria

[JimS]

In the attachment is an example of what I need.

If there is any "N" in Column-D, when all the Values in Column-A are identical then Column-E equals a "N" for all rows with the same value in Column-A.

This is probably easy but I cannot figure it out.

Thanks for any help.

JimS

[westconn1]

i am unable to open .xlsx files, but based on your description this should work

Dim cn As Connection, rs As Recordset, rs2 As Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Workbooks("database.xls").FullName & _
";Extended Properties=""Excel 8.0;HDR=NO"""
.Open
End With
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Sql = "select DISTINCT [f1] from [sheet2$]"
rs.Open Sql, cn, adOpenStatic, adLockReadOnly
Do Until rs.EOF
Sql = "select count(*) from [sheet2$] where [F1] = '" & rs(0) & "' and [F4] = 'n'"
rs2.Open Sql, cn, adOpenStatic, adLockReadOnly
If rs2(0) > 0 Then
Sql = "update [sheet2$] set [f5] = 'n' where [F1] = '" & rs(0) & "' and [F5] is null"
Set rs2 = cn.Execute(Sql, r)
Else: rs2.Close
End If
rs.MoveNext
Loop
rs.Close
cn.Closeadd a reference to ADO, change workbook and sheet names to suit

[JimS]

I must not be using this code correctly. I add a refernce to MS ADO Ext. 6.0 for DDL and Security.

It fails with a Compile error: User-defined type not defined on the "cn As Connection" dim.

Has anyone else had trouble opening the Attachment, its just a straight workbook with no VBA?

[mancubus]

with a formula:

=IF(SUMPRODUCT(($A$2:$A$1000=A2)*($D$2:$D$1000="N"))>0,"N","Y")


PS: for ADO put a reference to: Object X.X Library.
for Office 2007+ use: Provider=Microsoft.ACE.OLEDB.12.0

[westconn1]

Has anyone else had trouble opening the Attachment, its just a straight workbook with no VBA?i only have old excel, can only open .xls

[mancubus]

hey westconn1. pls google "open xlsx files in office 2003". there must be some converters.

[JimS]

mancubus,

Thanks, just what I was looking for.

JimS

[mancubus]

you're welcome. im glad it helped. pls mark the thread as "solved" from thread tools dropdown which is just above the first post.