Video

Great perspective, thanks for the feedback. If you look carefully, you can see the formula at the top of the screen, but it's a fair point because we didn't spent too much time on the formula itself. The formula used is MATCH, and it has 3 parameters - what value would you like to match (the lookup value), where would you like to look for a match (the lookup array) and lastly, what kind of match do you want (less than, exact match, or greater than). In the video, we used exact match. As an example, if you had values in column A and you wanted to look for those values in column B, it'd look something like this: =MATCH(A1,$B$1:$B$20,0), where the last parameter of 0 is using the exact match, which is what we used in the video (1 would be less than the -1 would be greater than). If the value is found, it will return the row number (or column number if you're looking through columns) of where it was found. So in the example above, if the value in A1 was found in B3, then it would return the number 3. If the value is not found, it returns N/A. Hopefully this is enough to get you started. Best of luck!

ha! That made me giggle. Glad it helped out! That's why I put it out here. Cheers! :-)

Sounds like it was helpful! Great to hear! Cheers!

The most effective way I've seen that done is a "lookup table"/ "reference table" that contains a list of all of the unique phrases (this is typically used with localities such as "New York", "San Francisco", etc.) that you're interested in, and then some type COUNTIF function that looks through the array and counts the number of times each phrase is found. Hope that helps!

@@adaptalytics Thanks. That seems like a good option. I took the list of words in my article and made a big column, like you did. The rows where all sequential so I grabbed the word and I anded (&&) " " and then && the next word and it gives me a 2 word column. Then I just pivot tabled it like you did. Seems like I need to learn more on making databases from webpages. Maybe I need to study webscrapping and data structures.

Unfortunately no, but there are many sample data sets out there to download for free. Try looking on Kaggle. Best of luck, cheers!

rock and roll, glad we could help!

Ha! Best comment yet! Glad it helped out!

The formula at 4:36 was an error. :-( The intent was to create two calculations - the first (column C) is a running cumulative that sums all values in the pivot table as it moves down the page. The second (column D) divides the cumulative on each row by the absolute total at the bottom of the pivot table. This second calculation is what computes the value needed to create the Pareto principle: "X% of the dataset makes up Y% of the total" - this is what I reference at 5:44 and eventually get to by 6:32 - "there are 1,000 unique words and 117 of them constitute half (50%) of total words in the data set". This is the Pareto principle. The Pareto principle is easiest understood when analyzing tax dollars paid into any government - it's often that 5% of the taxpayers pay 80% of the total tax dollars. Hope helps clarify things!

@@adaptalytics Thanks is not enough but thank you so very much for your amazing explanation. Its help me a lot and your video just save me. Thanks a lot. 😇

The simplest way I've done this in the past is to create a secondary list of stop words and write a formula that looks at this list and if the word is found, returns a Boolean value. For example, create an attribute column named "Is Stop Word" that returns a Y if it's found in your list of stop words and an N if it's not found. To create this initial list of stop words, sort the total words high to low by volume (as I've done around the 4:00 mark) and then scroll down with your human eyeballs and type a Y by any of the words that you'd like to exclude. Once you've done this for the top 100 words or so, copy this list elsewhere in the workbook (I usually have a tab named "Lookups" for odds and ends like this), filter to ONLY the words you've flagged as Y and delete this other words - this is your initial list of stop words. Go back to your dataset and reference this list and then once you've got the new stop word column created, use it to filter OUT the stop words in your pivot table. Another way to do this is to start by looking at short words using the LEN function. It's not perfect but it's a start. Most stop words are 2 to 4 characters, whereas most other words are longer, so you can filter your initial list to only those words that are 4 characters or less and see if that yields a high volume of stop words. Hope that helps!

Glad it was useful! :-)

Great to hear! We share stuff like this in hopes of helping people so cheers to your success!

Someone actually found that in the Microsoft Word options when saving to PDF. See comment below from "FloridaImages".

You probably forgot to "paste values". Pasting "normally" would try to paste the same formula, which would error out since the data isn't there in the new workbook. Look at timestamp 2:45 - I did it quickly and used a shortcut so I'll take the blame for why you missed it. :-( Try copying, going to the new workbook, right-clicking in the cell, selecting "Paste Special" and then selecting "values". If that doesn't make sense, just do a quick internet search for "how to paste values in Excel" or something similar and you'll figure it out. 🙂 Thanks for watching!

I'm afraid I don't understand the question. In general, I recommend folks do a Google search of exactly what they're to do - I've had great success with that over the years and is how I've learned to master Excel. Something like "how to copy rows in Excel and paste to columns", etc.

Great feedback, thanks. I'll adjust accordingly next time. 🙂

Great feedback, thanks! This will only work for folks who are able to edit with Word, but still, really great stuff and even simpler! I'm going to add to the text description of the video as well in case folks don't read the comments. :-)

If you've got a million records then Excel probably isn't the best tool for you. I'd recommend something like Rapid Miner (UI-based) or Python (code-based).

It's an old-school form of concatenation. I'm just concatenating the first two rows, separated by a space (in double quotes). The more formal way to do this would be =CONCATENATE(AH5," ",AH6). The second formula though, is slightly different, as I concatenate the first formula with the next row (rather than just the prior row with the next row). Hope that helps!

@@adaptalytics Thanks that seemed to work. My intuition told me to use concatenate but -- because of the small print -- I wasn't able to read your innovation.

Happy to help! :-)

No need - pay it forward my friend. Cheers! :-)

Glad it was helpful!

Firstly, thank you for the kind words and I'm glad it was helpful. :-)

Rock and roll, glad it helped! :-)

Great to hear it worked out!

You're welcome! We had no idea that so many people had this issue. Glad it helped! :-)

Right on!

Glad it helped!

Great to hear!

Glad it helped you, cheers!

Thanks for the comment and for sharing info on Excel's cell character cap. This tutorial was targeted to the average user that likely isn't dealing with a ton of data. Excel definitely has its limitations on data volume and processing constraints.

Glad you enjoyed it and found it helpful! That was the whole point of sharing. Cheers!

Please make sure to read the video description - we warned of this over a year ago.

We're sorry to hear your opinion on that. Hopefully you're able to find other free content on the internet that will meet your needs. Cheers!