Forum Discussion
track frequencies of keywords in Excel sheet
Hi everyone!
I have two columns filled with data. In the first column are publishing years and in the second are keywords (array of words divided with semicolon). I need to extract each keyword and determine it's frequency by year it emerged. I didn't come across any function that can help me.
I also tried with dividing each keyword in a different cell, but didn't find the function that would work in that scenario.
Please help!! Thank you! 🙂
AnaStudent If you are open to a Power Query (PQ) solution, please find a small example attached. The key is to split the Keywords column (B) from the data table into separate keywords, then unpivot the table. The result is shown in columns D and E. This could then be the source for a regular pivot table, if you so prefer, or you perform that step within PQ. See output in G1:M7.
5 Replies
AnaStudent If you are open to a Power Query (PQ) solution, please find a small example attached. The key is to split the Keywords column (B) from the data table into separate keywords, then unpivot the table. The result is shown in columns D and E. This could then be the source for a regular pivot table, if you so prefer, or you perform that step within PQ. See output in G1:M7.
- Thank you for your answer.
Can you tell me how did you get output G1:M7?
More specifically, how do I aggregate columns by year and get frequencies?
I used Pivot Column and when I choose "Don't Aggregate" in the advanced options I get table filled with Error in every cell. Or I get only two rows without sorting columns by year.
Thank you very much!AnaStudent If you go to the DATA ribbon and choose "Queries & Connections". Double click the "Keywords" query. It has only three "applied steps". The source is the output from the "UnpivotTrimmed" query. Then I added a column called "Custom" filled with numbers 1, and then I pivoted the "Year" column, summing the values from the "Custom" column. Then, I load the result back to Excel. Hope this helps.
Hello how are you doing?
I hope I can help you.
Considering your Column A as you YEAR Column and Column B your keyword columns.
Lets say you put your Keywords criteria on G1 and the Year criteria on F2.
Use the following formula =COUNTIFS(A2:A3,F2,B2:B3,"*"&G1&"*")
As this formula is checking using wildcard, doesn't matter the position of the keyword, it will be find and the formula will calculate the number of occurrences for it.
- I am doing well. How about you?
Thank you for your answer.
The thing is I can't use COUNTIFS, because I have too many words and I can't fill in conditions manually.
