calculating unique entries based on specific condition

Hi, I have been struggling with this problem for days, and I can't find a solution. I would really appreciate it, if someone could give a suggestion.

By using the auto-filter option in Excel, I have the answer straight away, but automatically calculating this is a pain (or at least for me).

I have attached a spreadsheet with example data to illustrate.

The problem
Column A has a text string (3 variations to choose from)
Column B has a text string (7 different options)
[B]-> [/B]For all values in column A matching the criteria, I need to count the unique instances in the relative column B

I can calculate the number of different instances in a row, based with the following formula: {=SUM(IF(LEN(B1:B24),1/COUNTIF(B1:B24,B1:B24)))} The If(LEN) part is to ensure empty cells are not causing an error.

Now I need to built in the factor to only count the unique instances for those cells for which cell A matches the criteria.

I would really appreciate some suggestions.

Best,
Wouter.

Hi there,

As you've said that you can achieve your desired result by using the AutoFilter, you can then use one of the 11 available SUBTOTAL functions to perform whatever function you want on your filtered (visible) rows.

Have a look at the attached where cells B33, B34 and B35 count the number of filtered instances of One, Two and Three respectively.

HTH

Robert

Hi, thanks for this.

Unfortunately it doesn't give me the desired outcome. In my original spreadsheet, those numbers are the bottom are the results that it should produce (4, 3 and 5 respectively).

In your model, there is also the assumption that row A is already sorted, which is not necessarily the case.

I actually made one mistake in my original post. I meant to say that I get the desired results by using the advanced filter, and sorting on unique records.

Best,
Wouter.

Hi there,

I inserted a blank row to allow for headings for filtering purposes - it doesn't matter if the data is sorted or not.

Assuming it's OK to have an additional column (which could be hidden if you wish), have a look at this attachement which makes use of your original formula.

HTH

Robert

Hi there,

I've provided two possible solutions.

1 - The macro (Macro1) on Problem v2 Excel when executed (Alt+F8) populates cells B32, B33 and B34 with the unique records for One, Two and Three respectively (based on the 21 possible combinations you have mentioned). This is the case regardless of what order the data is in (though may take a little while for large datasets).

2 - The rptUniqueRecords report on the Problem v2 Access database will produce the required figures. This report is based on the data in the tblRawData table.

Beyond these solutions, I cannot think of any other alternative.

HTH

Robert

Thanks for your help again.

Access: unfortunately I have to restrict this problem to Excel due to the usage of the spreadsheet.

Excel: the macro only works correctly when using any of the 8 options in the data sheet. it is however not generic. In my situation I can expect any kind of data in the B column. Only column A is limited to a pre-set of say 10 options.

in the long run, I have done it as follows:
have hidden columns C, D, E, etc. in column C1:C24 use =if(A1=One,$B1). in Columns D1:D24 use =if(A1=Two,$B1), etc.
Than at bottom of each hidden column, use the function =sum(1/countif(range,range)

This works correctly, so in the long run I have had to give up finding a single formula doing the same thing.

Wouter.

One last suggestion would be to try the template at http://www.myzips.com/download/Excel-List-Compare.phtml