Get one instance of each person from a large list

P

Paul Martin

Hi guys

I have a large table of data, in which a person's name appears in
Column A (multiple times). For quality control purposes, I would like
to be able to randomly extract one instance of each person who appears
in the list. I want the whole row of data once for each person.

Thanks in advance

Paul Martin
 
Y

Yinon

I'd recommend 2 stages
1. Data -> Filter -> Advanced Filter..
There you should choose "copy to another location" and mark the "unique records only" checkbox. – This will copy a unique list in the chosen locatio
2. Now you want to get all available data for each unique record – use VlookUp.
 
D

Debra Dalgleish

You can do this with an Advanced Filter, if you add a column to the
table of data.

Insert a column in the table, with a heading such as NameCount
In the first data row, enter a formula to count the instances of the
name. For example, if the name is in column C:
=COUNTIF($C$2:C2,C2)
Copy the formula down to the last row of data

Create a criteria area, with two columns, using the same headings as in
the large table. For example:

Name NameCount
Joe 3

To obtain a random number, in another cell, enter the following formula
(the Analysis Toolpak must be installed), where column D contains the
NameCount, and K2 contains the Name criteria:
=RANDBETWEEN(1,COUNTIF(C:C,$K$2))
Type that number in the NameCount criteria cell (or you could create a
macro to do this)

To run the Advanced Filter:
Select a cell in the data table
Choose Data>Filter>Advanced Filter
Choose to Filter in Place, or Copy to another location
Select the data table as the List range
For the criteria range, select all four cells
If copying to another location, select a starting cell.
Click OK
 
P

Paul Martin

Hi Debra

I only just found your reply. Thanks for that; I'll check it out.

Regards

Paul
 
Top