Dynamically return unique combinations in ranges to a list

[winspear]

I have worksheet 1 which contains three columns (shown below with some sample inputs)

Wireframe. Test Type. Sub TestType
01.01 BVR Generic Validation
01.01 BVR Generic Validation
01.01 BVR Generic Validation
01.01 BVR Specific Validation
10.02 BVR Generic Validation
10.02 Wireframe Label
10.02 Wireframe Label

The values in each list are named ranges
(eg. Wireframe = Range1, TestType = Range2, SubTestType = Range 3)


I want to automatically populate the following columns in a separate worksheet with unique values across all three columns (shown below with required output)

Wireframe. Test Type. Sub TestType. Count of occurances
01.01 BVR Generic Validation 3
01.01 BVR Specific Validation 1
10.02 BVR Generic Validation 1
10.02 Wireframe Label 2

Can anyone help me out (vba solution is fine if that is the only option)

Thanks

 

[sanrv1f]

why dont you try pivot table with all the three fields in row area and count of Wireframe in value area?

 

[winspear]

why dont you try pivot table with all the three fields in row area and count of Wireframe in value area?

Hi there

Thanks for the advice - although my vba skills are ok, I have not really used pivot tables before.

The source columns are not located next to each other - would this change anything?

Thanks

 

[sanrv1f]

that wont make any difference if all the three columns are in a single table, (ie) no blank columns in between

see these for more on pivot tables

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://www.cpearson.com/excel/pivots.htm