Hello,

I have a spreadsheet that contains member IDs in one column along with up to four potential Certifications per member ID. At the moment, each member ID can have up to four entries, depending on how many certifications a member has purchased.

I need to get a count of how many of each possible certification combination there are.

Here is an example of the data:

Member A Certification 1
Member A Certification 2
Member A Certification 3
Member B Certification 1
Member B Certification 3
Member C Certification 2
Member C Certification 3
Member C Certification 4

So, in the above example, member A bought certification combination (1,2,3), member b bought combination (1,3) and member C bought combination (2,3,4). Thus, Iif I were counting the different combinations, I'd have one for (1,2,3), 1 for (1,3) and 1 for (2,3,4) - basically I need a total of each of the combinations purchased.

Is there an easy way of getting the combinations from being one per row to being in columns across from the member ID so that Member A would read like this:

Member A Certification 1 Certification 2 Certification 3

Alternately, is there an easy way to sum each of the unique combinations and count them?

I have over 300,000 rows in the spreadsheet, so I need an easier way than counting manually!

Hi,

Am Thu, 10 May 2012 14:48:38 +0000 schrieb bryang:

Member A Certification 1
Member A Certification 2
Member A Certification 3
Member B Certification 1
Member B Certification 3
Member C Certification 2
Member C Certification 3
Member C Certification 4

So, in the above example, member A bought certification combination
(1,2,3), member b bought combination (1,3) and member C bought
combination (2,3,4). Thus, Iif I were counting the different
combinations, I'd have one for (1,2,3), 1 for (1,3) and 1 for (2,3,4) -
basically I need a total of each of the combinations purchased.

try it with pivottable


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2