×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Check multiple conditions
3

Check multiple conditions

Check multiple conditions

(OP)
Greetings,

I've a list of over 100 thousand records with ID, name, code etc.

I'm trying to identify individuals with H0004 code along with any other codes for the same ID# as we do not allow the H0004 code to exist with any other codes for the same person.

In the attached sample, sheet1 is the source data. My goal is to apply formula so that I get the result in sheet2.

TIA

Regards,

OCM

RE: Check multiple conditions

>In the attached sample
No sample attached... sad


---- Andy

There is a great need for a sarcasm font.

RE: Check multiple conditions

(OP)
Thanks Andy.
I attempted to attach a sample using the engineering.com window I choose my file and clicked upload file, it didn't work. The file I tried to attach is a 13.5 KB excel file. What am I doing wrong?

OCM

RE: Check multiple conditions

You can do just show here a sample of your data, like:

ID   Name   Code
1    Andy   H0004
2    Andy   H0005
3    OCN    H0004
4    OCN    H0007 

and based on this data, show the output you desire...


---- Andy

There is a great need for a sarcasm font.

RE: Check multiple conditions

So you need the Name value for each H004 Code, and then with that Name subset, list all the Codes for each Name?

Could easily be accomplished using MS Query, most likely.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

(OP)
Thanks,
In the example below, Andy is is good (has only H0004). Patti is not allowed as she has H00004 along with another code, Dave is allowed as he only has non H00004 codes and Stacy is not allowed because she has H00004 code along with other codes. Simply put, H0004 code is not allowed to appear with any other codes for the same ID/person.

ID Name Code
1 Andy H0004
1 Andy H0004
1 Andy H0004
2 Patti H0004
2 Patti 83833
2 Patti H0004
3 Dave 83833
3 Dave 87666
4 Stacy H0004
4 Stacy 88852
4 Stacy 87852
4 Stacy 88853

Here is what I expect to get:

ID Name Code
2 Patti H0004
2 Patti 83833
2 Patti H0004
4 Stacy H0004
4 Stacy 88852
4 Stacy 87852
4 Stacy 88853

TIA

Regards,

OCM

RE: Check multiple conditions

Not perfect, but this little VBA:

CODE

Option Explicit

Sub OCM()
Dim r As Integer
Dim ID As Integer

Dim blnHasH0004 As Boolean
Dim blnHasOther As Boolean

r = 2
ID = Range("A" & r).Value

Do Until Range("A" & r).Value = ""
    If ID <> Range("A" & r).Value Then
        ID = Range("A" & r).Value
        blnHasH0004 = False
        blnHasOther = False
    End If

    If Range("C" & r).Value = "H0004" Then
        blnHasH0004 = True
    End If
    If Range("C" & r).Value <> "H0004" Then
        blnHasOther = True
    End If

    If blnHasH0004 And blnHasOther Then
        Debug.Print Range("A" & r - 1).Value & "," & _
            Range("B" & r - 1).Value & "," & _
            Range("C" & r - 1).Value
    End If
    r = r + 1
Loop

If blnHasH0004 And blnHasOther Then
    Debug.Print Range("A" & r - 1).Value & "," & _
        Range("B" & r - 1).Value & "," & _
        Range("C" & r - 1).Value
End If

End Sub 

will give you the outcome of:

2,Patti,H0004
2,Patti,83833
4,Stacy,H0004
4,Stacy,88852
4,Stacy,87852
4,Stacy,88853
 


---- Andy

There is a great need for a sarcasm font.

RE: Check multiple conditions

(OP)
Thanks Andy,

My columns headings (ID, name, code) etc. range from A - Q.
1. Do I need to change anything in your code to match my column range?
2. I only used formulas with excel, but never vba and/or macro. Can you please lead me to the right direction as to how to apply your solution?

TIA

Regards

OCM

RE: Check multiple conditions

My Query will return ALL your columns, if you make the first line as posted here

CODE

Select d5.*
From
(
Select Distinct d2.Name
From 
(
SELECT DISTINCT d1.Name
FROM `data$` d1
WHERE d1.Code='H0004'
) d2
, `data$` d3
Where d2.Name=d3.Name
  and d3.Code<>'H0004'
) d4
, `data$` d5
Where d4.Name=d5.Name 

My results
ID	Name	Code
2	Patti	H0004
2	Patti	83833
2	Patti	H0004
4	Stacy	88853
4	Stacy	87852
4	Stacy	88852
4	Stacy	H0004

 

Workbook Attached.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

(OP)
Thanks Skip,

I opened the attached workbook where the data tab shows the data source. But, the result tab is blank.
Did you apply your syntax in the attached workbook?

TIA

Regards,

OCM

RE: Check multiple conditions

Notice that it's a macro workbook. You must run the main procedure in order to connect the query to the location of your workbook.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

(OP)
Skip,

The main procedure? Are you referring to the syntax posted by Andy?
If so, do I need to create a new module and paste the syntax and create a macro to use your solution?

TIA

Regards,

OCM

RE: Check multiple conditions

(OP)

Skip,
The main procedure you are referring to, is it the post from Andy?
If so, do I need to create a new module and paste the code and create a new macro to use your solution?

TIA

Regards,

OCM

RE: Check multiple conditions

Okay, just COPY the Results sheet to your workbook. Name your source sheet Data.

The code is in the Results sheet object.

Is this a one time thing or something you'll run periodically?





Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

(OP)
Thanks Skip,
I play around with the sample workbook you provided and it worked beautifully. In my workbook I name the source data and created a new worksheet named it Results,copy the macro and ran it. I received 'Subscript out of range' error.

Quote (Is this a one time thing or something you'll run periodically?)

I used to get similar assignment at least 4 times a year, but now I seem to get it more often.

TIA

Regards,

OCM

RE: Check multiple conditions

"created a new worksheet named it Results"

TILT!!!

My worksheet named Results has an embedded Query Table. That is a key. You could insert a Query Table and it would work. The key is that this sheet must contain a QT, as long as the vba main procedure connects the QT to the Data sheet in your workbook in your path and with your name.

All that was in my Results sheet. You really ought not create your own.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

(OP)
Skip, thanks.

I'm not familiar about inserting a Query Table in excel. Can you please lead me to the right direction as to how use your embedded QT in my workbook?

TIA

Regards,

OCM

RE: Check multiple conditions

Either COPY my Results sheet to your workbook or...
I think you could COPY the table in my Results sheet and PASTE it into your Results sheet IF both workbooks are in the SAME INSTANCE OF EXCEL.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

If I were you, I would 'stick' with Skip's approach to solve your issue, but if you want ti dive into what I gave you, Range("A" points to column A where ID is, Range("B" points to column B where Name is, and Range("C" points to column C where Code is. You may just replace A, B, and C with whatever columns have the ID, Name and Code in your Sheet.


---- Andy

There is a great need for a sarcasm font.

RE: Check multiple conditions

(OP)
Skip/Andy,

Thank you both for your solution for my post. I used Skip's approach as a solution to my problem I'm also trying And's approach for future references.

I do appreciate all your help.

Regards,

OCM

RE: Check multiple conditions

Yup, there's more than one way to skin a cat.

It comes down to, the tools that are in your toolbox. You must know enough about each tool, its capability, as well as your own capability to wield each tool, including an understanding of your data in its entirety and the task objective.

I've started out thinking or trying one approach and finally ended up with a solution that used a totally different method.

I've gone with one method, but tested other methods just because I wanted to learn something or just try a different approach to enhance my capabilies.

Glad you found a solution.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Check multiple conditions

Doing a quick look and copying the example data so the name is in column B, code in Column C, an list of individuals in COLUMN E for a quick sanity check I did the following formula:

=IF(SUMPRODUCT(($B$1:$B$12=E2)*($C$1:$C$12="H0004"))>0,IF(SUMPRODUCT(($B$1:$B$12=E2)*($C$1:$C$12="H0004"))=COUNTIF($B$1:$B$12,E2),"GOOD","BAD"),"GOOD")

It first looks to see if H0004 is under a name, if so, it counts the number of H0004 compared to the total under that name.

Blue Dragon

If I wasn't Blue, I would just be a Dragon...

RE: Check multiple conditions

(OP)
Skip, I completely agree with you, I always try implementing different methods to solve a given problem. I tried bluedragon2’s excel solution as well, and it worked beautifully.

bluedragon2, is it possible to modify your solution so that the H0004 code can NOT be combined with any other codes within a week time frame (7 days).

Here is a sample:

ID Name Code Date Comment
0001 Andy H0004 1/5/18 Allowed: Andy has only H0004 code and this is allowed any dates by itself
0001 Andy H0004 1/6/18

0002 Patti H0004 4/5/19
0002 Patti 83833 4/10/19 Not allowed: 5 days after H0004 codes. No other codes are allowed within one-week (7 days) time frame
0002 Patti 87666 4/11/19 Not allowed: 6 days after H0004 codes. No other codes are allowed within one-week (7 days) time frame

0003 Dave 88853 6/6/19 Allowed: Dave doesn't have H0004 code. Non H0004 codes are allowed at any dates
0003 Dave 87666 6/7/19

0004 Stacy H0004 8/1/19
0004 Stacy 88852 8/5/19 Not allowed: Stacy has 8852 code within 4 days after she has H0004 code
0004 Stacy 69655 8/7/19 Not allowed: Stacy has 8852 code within 6 days after she has H0004 code
0004 Stacy 88853 8/16/19 Allowed: over 7 days. Stacy has 88853 code 15 days after she had H0004 code

0005 Amy 69665 9/1/19 Allowed: Amy has those codes in the past (prior to having H0004)
0005 Amy 77400 9/3/19 Allowed: Amy has those codes in the past (prior to having H0004)
0005 Amy H0004 9/9/19

TIA,

OCM

RE: Check multiple conditions

With your example data, I quickly came up with this and little testing. COL A - ID, COL B - Name, COL C - Code, COL D - Date:

=IF(C2<>"H0004",IF(SUMPRODUCT(($B$1:$B$14=B2)*($C$1:$C$14="H0004")*($D$1:$D$14>D2-8)*($D$1:$D$14<D2))>0,"BAD","GOOD"),"GOOD")

Blue Dragon

If I wasn't Blue, I would just be a Dragon...

RE: Check multiple conditions

(OP)
Bluedragon2, thanks.

I used the following for the attached sample and got wrong result.

CODE --> =IF(C2<>"H0004",IF(SUMPRODUCT(($B$2:$B$31=B2)*($C$2:$C$31="H0004")*($D$2:$D$31>D2-8)*($D$2:$D$310,"BAD","GOOD"),"GOOD")

 

I also attached the expected result. Please let me know if this helps.

TIA

Regards,

OCM

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close