Excel Help - Also I suppose a general puzzle problem

[Murray]

Say I have a list of 2000 words

I have another list of 1600 words, all taken from the first list of 2000

How do I find out what 400 words are missing to complete the second list back to what it should be?

I can put them all together and remove duplicates, but that doesn't tell me what 400 were missing and that is what I need to know

Thanks

 

[Retired_Member42]

Can you not just combine all 3600 and filter for unique values?

 

[cm1975]

Two ways of doing it - VLOOKUP or COUNTIF

The second way is perhaps easier to explain.

If you have the 1600 list in column A of Sheet1 and 2000 list in column A of Sheet2, then in the cell B1 of Sheet2 put

=COUNTIF(Sheet1!A:A,Sheet2!A1)

If it returns a 1 then it exists. In the 1600 list.

Now highlight cells B1 to B2000 and press Ctrl + D to copy it down.

Filter out anything that is not 1

 

[Murray]

Can you not just combine all 3600 and filter for unique values?

Not sure how to do that

Two ways of doing it - VLOOKUP or COUNTIF

The second way is perhaps easier to explain.

If you have the 1600 list in column A of Sheet1 and 2000 list in column A of Sheet2, then in the cell B1 of Sheet2 put

=COUNTIF(Sheet1!A:A,Sheet2!A1)

If it returns a 1 then it exists. In the 1600 list.

Now highlight cells B1 to B2000 and press Ctrl + D to copy it down.

Filter out anything that is not 1

Thanks, I just tried that but it just says 1 on the first line and that's it and ctrl+d just copies the same name all the way down

 

[cm1975]

Not sure how to do that



Thanks, I just tried that but it just says 1 on the first line and that's it and ctrl+d just copies the same name all the way down

What name? Column B was the column with the formula in it in my example, so if the names are in another column, then alter the formula accordingly.

 

[Retired_Member42]

Not sure how to do that

http://lmgtfy.com/?q=excel+filter+unique+values

 

[cc976a]

If you haven't yet solved i'd suggest a VLOOKUP

Take the 2000 list, and look for matches in the 1,600 list. Any entry from the 2000 that doesn't show a match will be your missing 400 .... which can be shown using a simple filter on non matching entries

 

[philiporchard]

I probably use Vlookup formula more than any other, and you'll be able to use it to solve your challenge!

Good explanation here:

http://www.techonthenet.com/excel/formulas/vlookup.php

 

[Darren]

http://www.cyberciti.biz/faq/unix-linux-shell-removing-duplicate-lines/

 

[tk-hassan]

you can do very simply

• go to a new sheet
• add both lists in one column
• select all > now click on conditional formatting > highlight rules > duplicate values > ok
• now go to sort > custom sort > sort on > cell color
• here all without color cells are your required ones