Return ID Numbers which have only 1 type of comment

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.

Help

Accepted answers

ShankerV

Hi @ShantanuDagar

Step 1: Input

Screenshot 2023-10-05 182753.png

Step 2:

unique 1.png

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

All comments

ShankerV

Hi @ShantanuDagar

One way of doing this.

Screenshot 2023-10-05 182620.png

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

Hi @ShantanuDagar

Step 1: Input

Screenshot 2023-10-05 182753.png

Step 2:

unique 1.png

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703