Cross Comparing Excel Files

[CoraHarrison]

I have two excel files.

In the first file there is a list of words.

In the second file there is a list of sentences.

I would like to find out how many times the word comes up in the list of sentences.

I was going to use a vlookup but that has to be exact word to word.

Does anyone know a way around this?

Thanks,

[Mosaic]

Might be simpler to copy the sentences into a word doc then simply do 'find' or ctrl+f

[CoraHarrison]

Im doing a rather large amount of searches though...

[boxtops]

Click on find, find all, they'll all be highlighted

[boxtops]

^PS - put the word(s) you are looking for into the Find box, not the sentences.

[CoraHarrison]

Is there no more automatic way of doing this? :/

[boxtops]

If there is, I don't know it - I use Excel in a fairly basic fashion, but the way I suggest will tell you straight away how many times each word is used in the sentences.

[CoraHarrison]

I'm only asking due to the scale and the nature of the files. Hmm..

[bazwillrun]

pivot tables I use them all the time
youtube or google how to do them

[boxtops]

Ah - I knew there was a method, I don't know how to use them, I've never needed to!

[Huderon]

You might have to work on it a bit, but how about something along the lines of one of the methods outlined in http://office.microsoft.com/en-gb/excel-help/count-how-often-a-value-occurs-HP001127779.aspx ?

Given a copy of Access, I'd probably use that to do the job by loading both files into a database and then (sorry my SQL is very rusty) do something like select count() where B includes A - B being the table with the sentences and A being the one with the individual words. I'd use a small dataset for both A and B so that I could be sure I was getting the right numbers first though !

[Huderon]

On second thoughts, I'm not sure it can be done in Excel unless each word of your sentences is in a separate cell of a spreadsheet i.e. each sentence is on a single row, with each word in a single cell on that row and no punctuation.

I'm not even sure that you could do it the way I outlined in Access either, come to that.

There is a way to do it using Excel and the word counter and text analyser at http://sporkforge.com/text/word_count.php

If you go to that page, scroll down and click on the browse button under the text entry box and select the file with your sentences in, then click on the Upload File & Analyze Text button, it will, among other things, return a list of the words in your file with a word count for each of them. You can copy that list and paste it into Notepad, then load that file into Excel along with the your individual words list, and then use Vlookup.

OK, so it's not quite as simple as that and you will, perhaps, have to do a bit of tweaking of the Notepad list, but it should work and be a lot faster than trying to find ways to do it exclusively in Excel. The only limit with the Sporkforge link is that your file containing the sentences has to be smaller than 5Mb.

[Hammer]

Assume your word is in A1 and your sentence in A2.
The formula =COUNTIF (A2,"*"&A1&"*") will return 1 if the sentence contains the word, so you could use this as a starting point.

Only problem is that it will only return 1 or 0, so even if the word appears twice you will only get 1. And it looks for strings, so if your word is "and" and the sentence contains "android" it will still count as 1.

[Hammer]

You can also apply that to a whole range, so =COUNTIF (B:Z,"*"&A1&"*") will tell you how many cells your word appears in from columns B to Z.

Again, it only counts one occurance in each cell and "android" would still count if you were looking for "and".

[boxtops]

This sounds complicated to a non-techie - I'd still use my Find function, and count the number of incidents!

[Hammer]

But if there are 100 words to check that will take forever. And if the list of sentences or words ever changes you have to start again.

With a formula, you could check 100 words in a second. And if anything changes then it updates instantly.

And your Find All solution has the same downsides as mine regarding duplicates and words within words :-)

[Aquariel]

You could try the method on this page, which I have tried and it does work:

http://support.microsoft.com/kb/187667