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:

	Measurement 1	Measurement 2	Measurement 3	Measurement 4	Measurement 5	Type
P01	a	b	c	d	e	Type A
P02	a	b	c	d	e'	Type B
P03	a	b	c	d	e	Type A

<tbody>
</tbody>

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?