Create Unique List

[ES1987]

Hi

I have 6 sheets of data (all the sheets have the same layouts but relate to different events). On my overview sheet I need to create a list of unique entries (names) from column B of each sheet but only those where column C is a specific value. The data will get replaced every now and then so the manual methods of generating this data isn't feasible long term.

Any suggestions appreciated.

[Kaper]

Without seeing data I'd suggest macro working this way (easy one, but not necesserily most efficient):
* add temporary sheet (or use your overview sheet for this purpose)
* copy all data from columns B and C all sheets into it
* filter only meeting C criterion
* find unique values
* if made in temp sheet move this data to overview one and delete temp.

you can call this macro either manually, or for instance in Activate event for your overview sheet.

[ES1987]

Thanks but my knowledge isn't good enough to make that answer work. I have tried creating a dummy sheet to upload not sure it helps explain what I am trying though.

I have a number of sheets (6 in real life only created 2 in the example) Each contains an amount of data, column B is a list of names C a group name. entries will duplicate between sheets but a very unlikely to duplicate on a sheet.

I need a list on the overview sheet of all the names who belong to a certain group ie where column C is a certain value, with any duplicates removed.

many thanks

[Kaper]

You have not disclosed what shall be in column C - I assumed that required value is given in C1 in summary sheet.
Try for instance such macro:

Please Login or Register  to view this content.

it works with your example, if layout of real workbook is similar enough will also work directly with it (if not - ammend to follow layout of real workbook).

[MarvinP]

Hi ES,

You can do your problem without code if you use the Pivot Table Consolidation Wizard. First add it to your QAT toolbar:
http://www.pivot-table.com/2010/07/1...in-excel-2007/
Then add the 6 sheets worth of data and make a new sheet with what I think you want.
http://www.launchexcel.com/pivot-tab...tten-crosstab/

I've done this with your 2 sheet tables in the attached.

[Pepe Le Mokko]

Unique values or distinct values ?

[ES1987]

This is really close to working thank you. Only 2 things I haven't managed to work out, firstly on my overview sheet there were a few hidden rows (which I had forgotten about) which get overwritten in the new sheet, I assumed I just needed to redirect anything aimed at B2 to B6 but that didn't seem to work. The other is the clear contents seems to wipe the rest of the sheets data rather than just the new bits generated by the macro.

Many thanks

[Kaper]

Made few changes to previous code to use temporary sheet.
As real layout your sheet is not disclosed ;-) I made assumption that there could be some data/formula in B16 in your Overviewe sheet

Please Login or Register  to view this content.

[ES1987]

Thank you Kaper. All working now.