Creating a list of unique values based on 5 variables.

[TheHighlandCal]

Hi all,

Looking for some help on this problem.

I have 5 different columns with different measurements (these can be anything) and want to create a list of all the different unique combinations and then count how many of each unique combination there is.
There can be thousands of different entries so I am looking to completely automate this. The Table will look something like this:

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Measurement 1[/TD]
[TD]Measurement 2[/TD]
[TD]Measurement 3[/TD]
[TD]Measurement 4[/TD]
[TD]Measurement 5[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]P01[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]Type A[/TD]
[/TR]
[TR]
[TD]P02[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e'[/TD]
[TD]Type B[/TD]
[/TR]
[TR]
[TD]P03[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]Type A[/TD]
[/TR]
</tbody>[/TABLE]

Once I achieve this counting the different types will be simple.
I hope this is clear and any help given will be greatly appreciated!

 

[onlyadrafter]

Hello,

You haven't said how you want this to be solved, but have a VBA solution

Sub COUNT_UNIQUE()
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("J" & MY_ROWS).Value = Range("B" & MY_ROWS).Value & Range("C" & MY_ROWS).Value _
            & Range("D" & MY_ROWS).Value & Range("E" & MY_ROWS).Value & Range("F" & MY_ROWS).Value
    Next MY_ROWS
    Range("J1").Value = "Type"
    Range("J1:J" & Range("A" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1" _
        ), Unique:=True
    Range("I1").Value = "Count"
    Range("I2").Formula = "=countif($J$2:$J$" & Range("J" & Rows.Count).End(xlUp).Row & ",H2)"
    Range("I2").Copy Range("I3:I" & Range("H" & Rows.Count).End(xlUp).Row)
End Sub

You haven't specified the exact way of displaying the results, but have assumed columns H, I and J are available.

Most of this can be achieved by formula.

How close is this to your requirements?