Unigue codes from 1 Column on 3 different sheets

Posted by Leon on August 29, 2001 6:49 AM

Is there a way to do this with arrays, names or some other function?

I have one column on 3 separate sheets within a workbook that contain various department codes. Some of the codes within each column of each sheet will be duplicates while others will be unique. I want my array/name/some unknown function to contain all of the codes from each column of each sheet but not duplicates. Thanks in advance to those within the forum.

Posted by Eric on August 29, 2001 10:55 AM

Put the data from all 3 sheets into one column and do an advanced filter

Im not sure that this is exactly what you are looking for, but here goes.

Take all 3 columns (from sheet1 sheet2 and sheet3), and paste them into one column in another sheet (stack them). Label the column "all sheets". Then click on the label, go to "data"-->"filter"-->"advanced filter". It should automatically select the data in the column, click the "copy to another location" radio button, skip the criteria box, select a cell in the adjacent column for the "output", and click the "unique records only" checkbox. Click "OK" and you should get a list of the unique records, is that what you wanted?

Posted by Leon on August 29, 2001 11:11 AM

Re: Put the data from all 3 sheets into one column and do an advanced filter

Thanks for the help Eric. I was just hoping there was a way I could do it without cutting and pasting into another sheet.

Posted by Eric on August 29, 2001 11:43 AM

I was afraid of that...

btw you don't have to put the list on a separate sheet, and instead of cut and pasting you could set the column up to point to the data in the different sheets rather than actually pasting those values (i.e. don't copy sheet1 a2:a200 to sheet4 a2:a200, enter =sheet1!a2 in sheet4 a2 and copy down to a200; then the sheet 2 references start in sheet4 a201, etc.). That would be especially useful if the number of rows on each spreadsheet doesn't change after you set it up (of course it would be especially useless if it does!).

You could also use a pivot table to get at the unique list but you're still stuck with making a column of all the values first.

Also try reposting with your more explicit criteria,

good luck! sorry I couldn't be of more help