CODEWITHSUNDEEP
excelexcel-formula
JCassio
JCassio

Reputation: 13

How to concatenate columns in excel to obtain the occurrence of a value?

I've posted on instagram, rules for a chance to win a gift card to spend on my website. The post generated +4000 comments and I downloaded all comments into an Excel sheet and created columns such as username, date,comment,number of people mentioned and so on.

However, I now want to know how many unique accounts has an username mentioned in our comment section. My plan was to concatenate the "username" column and the "comment" column to see if the same username has the exact same comment/mentions or if they are different.

I have noticed that some usernames spammed the comment section with the same comment/mentions to increase their chances of winning (attempting to cheat), nevertheless, one of the rules was to mention different users.

I want to have a column that indicates if the user repeated the comment or the same mentions more than once. How can I achieve that? I have been struggling for a few hours now.

I am using Excel 16.12

And I have already tried the following options:

1 - Concatenating column "Username" and "Comments" (rows) and concatenate the whole column "Username" and "Comments" to see if the value exists. ("It sounds bad. I know")

2 - Count the occurrence of the row in the same column (Worse! It just generated a match because it exists in itself)

This is the code I've tried:

1 - Concatenate both columns

=IF(IF(B:B&","&D:D)=IF(B2&","&D2,"Exists","Non-Existent"))

2 - Check if exists in same column

=IF(D2=D:D,"Exists","Non-Existent")

Upvotes: 0

Views: 161

Answers (2)

OverflowStacker
OverflowStacker

Reputation: 1338

this should work:

=IF(SUMPRODUCT((A:A&","&D:D=A2&","&D2)*1)>1,"exists","doesn't exist")

If you want the number of occurences including the current line, then just use the SUMPRODUCT part:

=SUMPRODUCT((A:A&","&D:D=A2&","&D2)*1)

If you want to use this formula, you might want to make the data ranges as long as your data ($A$1:$A$5000&","&$B$1:$B$5000 instead of A:A&","&B:B). This would be faster for excel to calculate.

Upvotes: 0

JLCH
JLCH

Reputation: 803

If I understood your problem correctly. I'd simply use a countif>1 for this

Meaning, if B is the username and D the comment, concatenate both in E using in E2: 

=B2&", "&D2

Then simply countif that countif that column in F2

=IF(COUNTIF(E:E,E2)>1,"exists","unique")

Drag down and sort

Upvotes: 1

Related Questions