3 Variable Unique count

**maxthebear** · 05-20-2009, 06:22 PM

Greetings:

I am trying to find the number of unique 3 variable combinations there are in my rows of data. I have tried the frequency function but must be missing something.

Attached is a sample of what I am trying to achieve although there may be a better way of portraying the data.

Again, thanks to all who help me on this.

Max

**Paul** · 05-20-2009, 06:49 PM

Hi Max,

I'm using Excel 2007, but I believe this should also work in 2003. Forgive me if I don't get the menus/sub-menus 100% correct...

Select D9:F16, then click Data -> Filter -> Advanced Filter
Select 'Copy to another location'
'List Range' should already be filled in ($D$9:$F$16)
Leave 'Criteria Range' blank
Change 'Copy To' to another cell (e.g. H9)
Check the box for 'Unique Records Only'
Click OK

That should do it!

**maxthebear** · 05-20-2009, 06:59 PM

thanks for the reply.

However I realized that my sample does not explain what I was trying to accomplish. i want to put a 1 next in the cell next to a row when it is a new combination. Attached is a better example. the old one was not even close. Sorry..........

Max

**Paul** · 05-20-2009, 09:22 PM

The easiest way to do this would be to create a helper column. Based on your example, with data in D10:F16...

In G10 put the formula: =D10&E10&F10 and fill it down to G16. This will concatenate the values of the columns to match. (Making the "unique" formula much simpler)

In H10 put the formula: =IF(COUNTIF($G$10:G10,G10)>1,"",1) and fill it down to H16. There's your 1's and blanks. You can hide column G if you don't want to see the concatenated data.