List disctinct values

[Guest]

I am looking for a way to take a list of values from one column, and in
another column display a list of distinct items in this list.

So if in column A I have the following values:
Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In column B (or another worksheet or whatever) I am expecting a list like
this:
Alan
Bob
Charlie
Dave
Edgar

Is there a way to do something like this?

Thanks,

Cindi

 

[Guest]

If you want to effectively paste a list of the unique values
try this:

Your list will need a column heading. (eg Names)
I'll assume A1:A14 contains your list, with A1: Names

B1: Names

Select your list of values A1:A14

From the Excel main menu:
<data><filter><advanced filter>
Check: Copy to another location
Copy to: B1
Check: Unique records only
Click the [OK] button

That will create a list of the unique names under cell B1

Is that something you can work with?

Note: There are also ways to have the list built on a different worksheet in
the workbook and to use formulas, instead of Advanced Filter.

Post back if you have more questions.
***********
Regards,
Ron

XL2002, WinXP

 

[PCLIVE]

You could use an Advanced Filter.

Highlight your list of names. Then Data-Filter-Advanced Filter.
List range should show the range that you highlighted. Select the radio
button for ' Copy to another location'. Check the box for 'Unique records
only'.
In the 'Copy to' section, select a cell that you want your new data to be
placed.

HTH,
Paul

 

[T. Valko]

The easiest way to do this is to use the advanced filter.

Select the data in question
Goto the menu Data>Filter>Advanced Filter
Copy to another location
Copy to: Select a location
Unique records only
OK

Note that if you want to copy to another sheet you must start the procedure
from that other sheet.

Biff

 

[Guest]

Here is what I am trying to do. We are migrating some databases from Town A
to Town B. I am trying to map these tables into different locations. I have
done this mapping saying

owner table_name file_name mb
camposad dw_participant clrptng_data02 47
camposad dw_consult clrptng_data02 34
camposad dw_prsn_addr clrptng_data04 27
camposad dw_care_sprt_kit_info clrptng_data03 24
camposad savings_d_t2_orig_calced clrptng_data03 22
camposad savings_d_t2_uncalced clrptng_data04 21


I also have some other info like the current size of the tables. I am
trying to get a distinct list of file names out of my spreadsheet. Once I
get this list, I would then like to sum up all of the table sizes in each
file, so we can know how big each file will be. I've been looking at trying
to use some sort of formulas, but not having a lot of luck getting the
distinct list together. I figure that once I get this list together, maybe
in another worksheet within my workbook, doing this sum should be relatively
straight forward. I would like to use something like a formula, because
after looking at the file sizes, we may move some tables around, and I would
like these numbers to be modified automatically. Im probably pushing the
limits of what I can do w/ excel.

If you want to effectively paste a list of the unique values
try this:

Your list will need a column heading. (eg Names)
I'll assume A1:A14 contains your list, with A1: Names

B1: Names

Select your list of values A1:A14

From the Excel main menu:
<data><filter><advanced filter>
Check: Copy to another location
Copy to: B1
Check: Unique records only
Click the [OK] button

That will create a list of the unique names under cell B1

Is that something you can work with?

Note: There are also ways to have the list built on a different worksheet in
the workbook and to use formulas, instead of Advanced Filter.

Post back if you have more questions.
***********
Regards,
Ron

XL2002, WinXP

I am looking for a way to take a list of values from one column, and in
another column display a list of distinct items in this list.

So if in column A I have the following values:
Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In column B (or another worksheet or whatever) I am expecting a list like
this:
Alan
Bob
Charlie
Dave
Edgar

Is there a way to do something like this?

Thanks,

Cindi

 

[Guest]

If you want "automated" method then try this:

"Name" is a define name in column A, ofcourse no quotes

=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(Name,Name,0),MATCH(Name,Name,0))>0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(FREQUENCY(MATCH(Name,Name,0),MATCH(Name,Name,0))>0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag the Fill Handle to copy as far as needed

 

[T. Valko]

You can do this with formulas *but* the formula needed is not very efficient
especially if the table to search has 1000's of entries and there are 100's
of uniques to be extracted.

Biff

Here is what I am trying to do. We are migrating some databases from Town
A
to Town B. I am trying to map these tables into different locations. I
have
done this mapping saying

owner table_name file_name
mb
camposad dw_participant clrptng_data02 47
camposad dw_consult clrptng_data02 34
camposad dw_prsn_addr clrptng_data04 27
camposad dw_care_sprt_kit_info clrptng_data03 24
camposad savings_d_t2_orig_calced clrptng_data03 22
camposad savings_d_t2_uncalced clrptng_data04 21


I also have some other info like the current size of the tables. I am
trying to get a distinct list of file names out of my spreadsheet. Once I
get this list, I would then like to sum up all of the table sizes in each
file, so we can know how big each file will be. I've been looking at
trying
to use some sort of formulas, but not having a lot of luck getting the
distinct list together. I figure that once I get this list together,
maybe
in another worksheet within my workbook, doing this sum should be
relatively
straight forward. I would like to use something like a formula, because
after looking at the file sizes, we may move some tables around, and I
would
like these numbers to be modified automatically. Im probably pushing the
limits of what I can do w/ excel.

If you want to effectively paste a list of the unique values
try this:

Your list will need a column heading. (eg Names)
I'll assume A1:A14 contains your list, with A1: Names

B1: Names

Select your list of values A1:A14

From the Excel main menu:
<data><filter><advanced filter>
Check: Copy to another location
Copy to: B1
Check: Unique records only
Click the [OK] button

That will create a list of the unique names under cell B1

Is that something you can work with?

Note: There are also ways to have the list built on a different worksheet
in
the workbook and to use formulas, instead of Advanced Filter.

Post back if you have more questions.
***********
Regards,
Ron

XL2002, WinXP

I am looking for a way to take a list of values from one column, and in
another column display a list of distinct items in this list.

So if in column A I have the following values:
Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In column B (or another worksheet or whatever) I am expecting a list
like
this:
Alan
Bob
Charlie
Dave
Edgar

Is there a way to do something like this?

Thanks,

Cindi

 

[MH]

Why not do it in Access then?

MH

Here is what I am trying to do. We are migrating some databases from Town
A
to Town B. I am trying to map these tables into different locations. I
have
done this mapping saying

owner table_name file_name
mb
camposad dw_participant clrptng_data02 47
camposad dw_consult clrptng_data02 34
camposad dw_prsn_addr clrptng_data04 27
camposad dw_care_sprt_kit_info clrptng_data03 24
camposad savings_d_t2_orig_calced clrptng_data03 22
camposad savings_d_t2_uncalced clrptng_data04 21


I also have some other info like the current size of the tables. I am
trying to get a distinct list of file names out of my spreadsheet. Once I
get this list, I would then like to sum up all of the table sizes in each
file, so we can know how big each file will be. I've been looking at
trying
to use some sort of formulas, but not having a lot of luck getting the
distinct list together. I figure that once I get this list together,
maybe
in another worksheet within my workbook, doing this sum should be
relatively
straight forward. I would like to use something like a formula, because
after looking at the file sizes, we may move some tables around, and I
would
like these numbers to be modified automatically. Im probably pushing the
limits of what I can do w/ excel.

If you want to effectively paste a list of the unique values
try this:

Your list will need a column heading. (eg Names)
I'll assume A1:A14 contains your list, with A1: Names

B1: Names

Select your list of values A1:A14

From the Excel main menu:
<data><filter><advanced filter>
Check: Copy to another location
Copy to: B1
Check: Unique records only
Click the [OK] button

That will create a list of the unique names under cell B1

Is that something you can work with?

Note: There are also ways to have the list built on a different worksheet
in
the workbook and to use formulas, instead of Advanced Filter.

Post back if you have more questions.
***********
Regards,
Ron

XL2002, WinXP

I am looking for a way to take a list of values from one column, and in
another column display a list of distinct items in this list.

So if in column A I have the following values:
Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In column B (or another worksheet or whatever) I am expecting a list
like
this:
Alan
Bob
Charlie
Dave
Edgar

Is there a way to do something like this?

Thanks,

Cindi

 

[Guest]

Another way to extract the uniques list dynamic in another sheet
using simpler non-array formulas ..

Source data in Sheet1's col C (file_name), running in C2 down

In another sheet,

Put in A2:
=IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!$C$2:C2,Sheet1!C2)>1,"",ROW()))
Leave A1 blank

In B2:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(Sheet1!C:C,SMALL(A:A,ROW(A1))))
Select A2:B2, fill down to the max expected extent of data in Sheet1's col
C. Hide away col A. The uniques list of file_names will be extracted in col
B, all neatly bunched at the top.

 

[Guest]

Max,
Thank you. This is a very easy solution to this problem. It is easily
expandable automagically to varying size of list, (Not being an array
formula), and it works for me (something the other solutions have not done.
Thanks Again!
Larry

 

[Max]

Larry, you're welcome. Great to hear that.
The feedback is appreciated.