Return ID Numbers which have only 1 type of comment

[ShantanuDagar]

Hello,

I have a dataset with IDs in 1 column, next column has attributes, next has value of that attribute, the next has comment.

There are 2 types of comment: Match, Manual

As 1 row can have only 1 type of attribute, for multiple attributes, 1 ID will be in multiple rows with different attributes and corresponding values.

ID   Attribute             Value      Comments

1        Name              SD            Match

1        City                 Paris          Match

1        Job Title           CMO         Manual

I want to get the list of IDs which have only "Match" type of comment. Even if there is 1 attribute manual, then that ID shouldn't be there in the list.

If ID 2 has 20 rows (20 attributes) and all 20 comments are Match then get that ID in final result. If even 1 comment out of 20 is Manual, then don't bring that ID in final result.

There are 1300+ unique IDs and 5500+ rows.

[ShankerV]

Hi @ShantanuDagar 

One way of doing this.

 

Many thanks

Shanker V

[Christina_H]

I would do something like this, getting a distinct list of comments for each ID then concatenate them.

[ShankerV · Accepted solution]

Hi @ShantanuDagar 

Step 1: Input

Step 2:

Select the check box near ID to do the Unique based on the column ID.

Step 3: Filter tool

Filter condition, 

Comments = Manual

Step 4: Unique tool

Select the check box near ID to do the Unique based on the column ID.

Step 5: Join tool

Join ID = ID

Select 6: Select tool.

Remove the unwanted columns, deselect all the columns except the first column ID.

Many thanks

Shanker V

[ShantanuDagar]

Thanks, works as expected.

[ShantanuDagar]

What will be the way to populate all the attributes for those IDs now which were present in input.

Edit: I think i got it. A join with input and the Right_Prefixed ones will be the result intended while ignoring the left side as it will be duplicated.