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 |
#1
|
|||
|
|||
Unique entries in a filtered list
Hi Peo,
I posted it again in case anyone missed it the first time. I got your formula to work and although it was impressive it is not applicable for the way I wish the spreadsheet to be used. There is a great deal of data in the spreadsheet and performance and storage are important factors so I'm not too keen on adding to this by concatenating columns. Also the spreadsheet is also going to be used by others and filtering/unfiltering by true when analysing the data would make the user interface too complicated for some novice users. If there is no way of doing this via a formula then I suppose it'll have to be programmed in VBA. Thanks for your help. Matt -----Original Message----- You have already posted this once, why don't you give an example how you filter.? Do you filter for one value in one column and want the unique entries in a second column? If that is so you can concatenate those 2 columns. Assume you want to filter A2:A100 for one value, let's say the string "ABC", now you have values in B2:B100 that you want to have unique values of. Add 2 help columns, concatenate the values in A and B as follows =A2&B2 copy down to row 100 (assume you do this in column G). Now in another help column put =COUNTIF($G$2:G2,G2)=1 copy down now filter on A and on the second help column TRUE -- Regards, Peo Sjoblom wrote in message ... Okay - heres a challenge for you excel gurus.... How can you count the unique entries on a filtered list. I can extract unique entries using the following array formula =SUM(1/COUNTIF(A1:A10,A1:A10)) but when you use the filter the array formula obviously just refers to these cells. How could I count only the unique entries in the visble range. Cheers, Matt . |
#2
|
|||
|
|||
Unique entries in a filtered list
Matt, try this formula adapted from Daniel Maher
=CEILING(SUM((IF(LEN(A3:A100)0,1/COUNTIF(A3:A100,A3:A100)))*(SUBTOTAL(3,OFF SET(B3,ROW(B3:B100)-MIN(ROW(B3:B100)),,1)))),1) it has to be array entered with ctrl + shift & enter it will count unique entries in A3:A100 when B3:B100 have been filtered Upon request I can email you privately a sample workbook -- Regards, Peo Sjoblom wrote in message ... Hi Peo, I posted it again in case anyone missed it the first time. I got your formula to work and although it was impressive it is not applicable for the way I wish the spreadsheet to be used. There is a great deal of data in the spreadsheet and performance and storage are important factors so I'm not too keen on adding to this by concatenating columns. Also the spreadsheet is also going to be used by others and filtering/unfiltering by true when analysing the data would make the user interface too complicated for some novice users. If there is no way of doing this via a formula then I suppose it'll have to be programmed in VBA. Thanks for your help. Matt -----Original Message----- You have already posted this once, why don't you give an example how you filter.? Do you filter for one value in one column and want the unique entries in a second column? If that is so you can concatenate those 2 columns. Assume you want to filter A2:A100 for one value, let's say the string "ABC", now you have values in B2:B100 that you want to have unique values of. Add 2 help columns, concatenate the values in A and B as follows =A2&B2 copy down to row 100 (assume you do this in column G). Now in another help column put =COUNTIF($G$2:G2,G2)=1 copy down now filter on A and on the second help column TRUE -- Regards, Peo Sjoblom wrote in message ... Okay - heres a challenge for you excel gurus.... How can you count the unique entries on a filtered list. I can extract unique entries using the following array formula =SUM(1/COUNTIF(A1:A10,A1:A10)) but when you use the filter the array formula obviously just refers to these cells. How could I count only the unique entries in the visble range. Cheers, Matt . |
Thread Tools | |
Display Modes | |
|
|