Forum Discussion
Help counting duplicate values/words
Hello, I have a worksheet with about 80 responses that are in a sentence format that I am trying to figure out how to count and sort keywords that are used multiple times. These are verbatim respons...
Hi Amber,
Perhaps better to do with Power Query, but here is the manual solution which could work for one-time job, for repeated one better to find more elegant solution.
1) Select your rows with text and replace dots, commas, etc on nothing
2) From Ribbon->Data->Text to Columns split your text on separate words (one in column) using space as separator
3) If your first column is A, copy/paste next ones sequentially at the bottom of A (better to do with Table, but let keep simple cells)
4) If words starts in A1 enter in B1 formula
=COUNTIF(A:A, A1)
and copy it down to the end of your range with words
5) Filter here (or on previous steps) non-calculated words ("to", "and", etc.) and empty cells
6) Sort columns A and B in descending order by column B
In general you shall receive the result. Above is just and idea, I'm in shortage of time to play with this right now, and especially with Step 3.
Thank you, Sergei! I'm not familiar with Power Query and not really experienced in Excel. I've tried following your instruction with no success. I've attached an example in my original post. I'm trying to extract the top 20 words/terms used from the worksheet. I've been trying to look on YouTube because I'm visual but, still no luck.
Hi Amber,
When things bit more complex, but since that's practically ready-to-use solution you may use it as black box so far.
First, you have to have Power Query with your Excel. If you are on version Excel 2016 it's built-in (and called Get & Transform now). Nothing to do here.
If you are on earlier version
you need to download and install Microsoft Power Query for Excel add-in. Installation is straight forward and usually no problems are here.
If you are on even earlier version of Excel - no luck with this solution.
I'll check your file when understand how to download it - TechComm changed the interface and so far i see no way to do that.
