If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
REPORTING unique values
Hello Peo,
Your formula =IF(ISNA(MATCH(0,COUNTIF($C$1:C1,$B$5:$B$15),0))," ",INDEX($B$5:$B$15,MATCH(0,COUNTIF($C$1:C1,$B$5:$B $15),0))) is a great solution to a task we have in mind, but is there a way to have it sort the resulting list when working from an unsorted source? Thanks, Blue *************** "Peo Sjoblom" wrote in message ... You can't read? I told her to use advanced filter "You can use advanced filter which has a built in way, select the list, then select unique records only then copy to another location. Excel 2007 has a built in method for this called remove duplicates?" I thought perhaps it would be easier to select a range and apply filter to get the values but of course there are ways using a formula =IF(ISNA(MATCH(0,COUNTIF($C$1:C1,$B$5:$B$15),0))," ",INDEX($B$5:$B$15,MATCH(0,COUNTIF($C$1:C1,$B$5:$B $15),0))) entered with ctrl + shift & enter Where $C$1:C1 is the cell above the cell with the formula, adapt accordingly HTH -- Regards, Peo Sjoblom "Alan Beban" wrote in message ... Peo Sjoblom wrote: Use this instead =SUMPRODUCT((B5:B15"")/COUNTIF(B5:B15,B5:B15&"")) that will not return an error if all cells are empty You can use advanced filter which has a built in way, select the list, then select unique records only then copy to another location. Excel 2007 has a built in method for this called remove duplicates Can you post the syntax for using the remove duplicates method? Thanks, Alan Beban |
#22
|
|||
|
|||
REPORTING unique values
Hello Peo,
Your links are very good, but isn't there a single formula that can both sort and extract unique values at the same time? We would like to go from the original column directly to the sorted unique column without the middle sorted column of all values? *************** "Peo Sjoblom" wrote in message ... I corrected your formula that counts it, I never said it would return unique values If you test your formula and remove all values in your range it will return a DIV error To use a formula is rather complicated, here's a link that will do that http://tinyurl.com/2fwou2 you can download a sample from the same page http://nwexcelsolutions.com/Download..._records. xls it's definitely easier to use advanced filter -- Regards, Peo Sjoblom "jane" wrote in message ... HI Peo and Pete, Thank you for responding so quickly! My result using Peo's formula was 6. My formula gave me 6 also but I needed a list of the actual names without doing the auto-filter as Pete suggested. thoughts? jane "Peo Sjoblom" wrote: Use this instead =SUMPRODUCT((B5:B15"")/COUNTIF(B5:B15,B5:B15&"")) that will not return an error if all cells are empty You can use advanced filter which has a built in way, select the list, then select unique records only then copy to another location. Excel 2007 has a built in method for this called remove duplicates -- Regards, Peo Sjoblom "jane" wrote in message ... here is my data: Mary Sally Sally Mary John John David I used the following to COUNT the unique values: =SUMPRODUCT((B5:B15"")/COUNTIF(B5:B15,B5:B15)) What can I do to get a list that REPORTS the unique data - my result should look like this: Mary Sally John David thanks in advance! jane |
#23
|
|||
|
|||
REPORTING unique values
Hello,
For this I would suggest my UDF lfreq (use only the first output column = select only one column for the array formula): http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#24
|
|||
|
|||
REPORTING unique values
Thank you, Bernd. Just two questions:
FIRST, your link gives two sample functions. Is the one on the left only for numbers and the one on the right for text and numbers? What if the list has blanks? SECOND, if we define this function and use it on a spreadsheet, will it be integral to the spreadsheet? In other words, if we copy the spreadsheet to another location will it still retain all its functionality? Thanks, Richard ******************** "Bernd P" wrote in message ... Hello, For this I would suggest my UDF lfreq (use only the first output column = select only one column for the array formula): http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#25
|
|||
|
|||
REPORTING unique values
Hi Richard,
Both functions should work on both strings and numbers. If you copy the whole file (worksheets and modules included) then the UDF would be transferred, too. You have to allow individual macros, though (if the macro cannot be run this might be caused by your company's security policy on macros). Regards, Bernd |
#26
|
|||
|
|||
REPORTING unique values
"Blue Max" wrote...
.... . . . isn't there a single formula that can both sort and extract unique values at the same time? . . . .... Almost. If the possibly duplicate values were in a range named D, that range could contain numbers and text, and you want the distinct numbers to appear before the distinct text in the results, try the following array formulas. E1 [1st distinct value]: =IF(COUNT(D),MIN(D),INDEX(D,MATCH(0,COUNTIF(D,""& D)))) E2 [2nd distinct value]: =IF(SUM(--(D=E1))COUNT(D),MIN(IF(DE1,D)), INDEX(D,MATCH(IF(COUNT(E1),0,COUNTIF(D,"="&E1)), IF(ISTEXT(D),COUNTIF(D,""&D)),0))) Fill E2 down to get the subsequent distinct values. Note: for large range D, these formula will take a long time to recalculate - there are pronounced benefits to using an intermediate range for sorted values. |
#27
|
|||
|
|||
REPORTING unique values
Thanks, Harlan. I just keep thinking that there must be some way to
integrate the two formulas. The problem seems to be that Microsoft has not provided any standard functions for sorting and filtering arrays so they can continue to be used in the same formula. Thanks, Blue ************** "Harlan Grove" wrote in message ... "Blue Max" wrote... ... . . . isn't there a single formula that can both sort and extract unique values at the same time? . . . ... Almost. If the possibly duplicate values were in a range named D, that range could contain numbers and text, and you want the distinct numbers to appear before the distinct text in the results, try the following array formulas. E1 [1st distinct value]: =IF(COUNT(D),MIN(D),INDEX(D,MATCH(0,COUNTIF(D,""& D)))) E2 [2nd distinct value]: =IF(SUM(--(D=E1))COUNT(D),MIN(IF(DE1,D)), INDEX(D,MATCH(IF(COUNT(E1),0,COUNTIF(D,"="&E1)), IF(ISTEXT(D),COUNTIF(D,""&D)),0))) Fill E2 down to get the subsequent distinct values. Note: for large range D, these formula will take a long time to recalculate - there are pronounced benefits to using an intermediate range for sorted values. |
Thread Tools | |
Display Modes | |
|
|