| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List disctinct values
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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 "cindi" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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 "cindi" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
The easiest way to do this is to use the advanced filter.
Select the data in question Goto the menu DataFilterAdvanced 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 "cindi" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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. "Ron Coderre" wrote: 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 "cindi" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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),MA TCH(Name,Name,0))0,ROW(INDIRECT("1:"&ROWS(Name))) ),ROWS($1:1))),"",INDEX(Name,SMALL(IF(FREQUENCY(MA TCH(Name,Name,0),MATCH(Name,Name,0))0,ROW(INDIREC T("1:"&ROWS(Name)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Drag the Fill Handle to copy as far as needed "cindi" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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 "cindi" wrote in message ... 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. "Ron Coderre" wrote: 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 "cindi" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
Why not do it in Access then?
MH "cindi" wrote in message ... 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. "Ron Coderre" wrote: 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 "cindi" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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,Shee t1!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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cindi" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
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" wrote: 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,Shee t1!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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cindi" wrote: 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
List disctinct values
Larry, you're welcome. Great to hear that.
The feedback is appreciated. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "LarryLL" wrote in message ... Max, Thank you. This is a very easy solution to this problem. It is easily expandable automatically 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if specific value from list A equals one of the values from list b... | Excel Worksheet Functions | |||
| big list of values to averaged smaller list? | Excel Worksheet Functions | |||
| create a list of single values from multiple values | Excel Worksheet Functions | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
Linear Mode
