Unique Word List

Hi. I am using Excel 2003, and I am very new to the program, so if you know
the answer to my question, please explain everything in very simple English.
I have about 25 sheets that contains various columns. I want to make a word
list (one row for each word) for the words that appear in column B in all the
sheets, then copy and paste this list into another sheet. Does anyone know
how this can be done? Do I have to do each sheet separately, and then
alphabetize the resulting lists?

Rebecca
The four columns shown here were originally on separate sheets (1 to 4) in column B, (the names marked with a 1 where already in column A of sheet 1).

Albert 1 Albert 2 Albert 3 Albert 4
Barry 1 Barry 2 Barry 3 Barry 4
Colin 1 Colin 2 Colin 3 Colin 4
Dennis 1 Dennis 2 Dennis 3 Dennis 4

What I did (excel97) was to click in B1 of sheet 1 and type an equals sign =. Then I clicked on the tab for sheet 2, highlighted the data in column B of sheet 2, and pressed enter. (=Sheet2!B1:B4 showed in the formula slot). Sheet 1 appeared with Albert 2 showing in B1 of sheet 1. Moving the cursor into the bottom right-hand corner of B1 of sheet1, until it turned into a small black cross, I left-button clicked the mouse and dragged the box down to B4 of sheet 1. The four names shown in column 2 above (because the cells B1 to B4 of sheet 1 and sheet 2 are now linked), appeared. The same procedure was followed for the other columns. If there are a long column of data, rather than just hightlight the cells containing data, highlight the whole column by clicking on the column's letter - so =Sheet2!B:B should show in the formula slot (it's quicker). Also, by highlighting columns rather than just the cells containing data, anything added to the worksheet's column B in the future will also automatically appear on sheet 1 in their respective columns.

Rebecca

To get the words from column B on each sheet into one master sheet will
require manual or coded copy/paste from each sheet into one column in the
master.

Then you can use an Advanced Filter to select the unique words in that column
and copy to another sheet.

See Debra Dalgleish's site for how to copy uniques to a new sheet.

http://www.contextures.on.ca/xladvfi...html#ExtractWs

Sort this list alphabetically using Data>Sort>Select a column>Ascending.

The copy/paste part could be done much more easily using a VBA Macro(code) to
move all the words to the master sheet.

If you want to take a crack at that, post back.


Gord Dibben Excel MVP


On Fri, 4 Mar 2005 21:57:02 -0800, "Rebecca"
<Rebecca@discussions.microsoft.com> wrote:

>Hi. I am using Excel 2003, and I am very new to the program, so if you know
>the answer to my question, please explain everything in very simple English.
>I have about 25 sheets that contains various columns. I want to make a word
>list (one row for each word) for the words that appear in column B in all the
>sheets, then copy and paste this list into another sheet. Does anyone know
>how this can be done? Do I have to do each sheet separately, and then
>alphabetize the resulting lists?