Count Most Frequent Pairs

[Crystalyzer]

I have a list of data "Company" and "Tag" as follows:

Company      Tag
ABC          media
ABC          showbiz
ABC          TV
ABC          entertainment
DEF          media
DEF          entertainment
DEF          radio
GHI          software
GHI          enterprise
GHI          programming

What I want to do is find the top ten most often paired tags. In the simple example above "media" and "entertainment" have been paired (with the same company) twice. Is there any way in Excel to do this?

Thanks so much for any help or direction.

Thanks and best regards,
-Lloyd

 

[bluedragon2]

Take a look at pivot tables

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Crystalyzer]

I've been using pivot tables but to no avail. There are over 400 unique tags and 140 companies. I'm not sure how I would accomplish the "pairing" using a pivot table. I was able to determine the most widely used tags (individually) however I need the most frequent pairings. Any ideas on that?

Thanks!

Thanks and best regards,
-Lloyd

 

[xlbo]

easiest way:

create a new column
if company in col A and Tag in col B, then

=A2&B2

is the formula you need

create a pivottable based on this one field and put it in the ROW area
Also do a COUNT of the field in the DATA area
when done, right click on your field heading and choose 'Field Settings'
chose 'Advanced'
tick 'Descending'
From dropdown box choose COUNT of Fieldname (whatever you have called your new column)
Ok
OK
et voila



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Crystalyzer]

Geoff,

thanks for the response. That would give me the pairings of Companies to Tags. I am looking for the pairings of tags within each company. Each company has several tags associated with it. I am looking for the most frequently paired tags by company (i.e. how many times does media get paired with entertainment across ALL companies?)





Thanks and best regards,
-Lloyd

 

[GlennUK]

So you are asking how many times does "media" and "entertainment" appear for all companies? Is it always "media" and "entertainment" as the pairs? Or do you mean that the analysis should choose any 2 tags that appear multiple times somehow?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

in which case, simply put COMPANY & TAG in the ROW field and count ofTAG in the DATA field - sort descending as per previous instructions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Crystalyzer]

Glenn,

Yes you've got it. I want to count how many times ANY combination of two tags appear within a company. I have done the pivot table with company and tag and count of tag simply gives me a count of how many times a single tag has been used. What I want is how many times a tag has been used in conjunction with ANOTHER tag within a company across ALL companies.

My data example was probably too limited to accurately express the goal.

Thanks again for all the grey matter!!


Thanks and best regards,
-Lloyd

 

[GlennUK]

Altering the PivotTable layout so that Tags are across the top, Company is down the left, and Count of Tag is in the data area should show you a layout that could be useful to you. Try it and let me know what you think.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Crystalyzer]

Glenn,

Thanks for the suggestion, I tried it but that tells me what I already know (how many tags per company and how many times a single tag has been used).

Also, I have over 700 tags and the pivot table can't show that many columns.



Thanks and best regards,
-Lloyd

 

[GlennUK]

700 tags? And you want to test for any pair occurring for companies. That's 244,650 combinations of tags. Well, you can't make a master list of all possible combinations then. I'll have a think about this over the weekend, and see what I can come up with.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Corgano]

I know you're looking for an Excel solution but this sounds like it would be a snap if you imported the table into access.

Just a thought.

 

[Crystalyzer]

Corgano,

That sounds like an interesting idea. I know how to import the data to access, but then what? Any suggestions?

Thanks and best regards,
-Lloyd

 

[Corgano]

Ugly, processor intensive, drawn out method: I'm sure a VBA or cleaner solution is available but...

Column A = Company
Column B = Tag

Carry each of these down to whatever:

Column C: =A2&" "&B2
Column D: =COUNTIF($C$2:C2,C2)
Column E: =IF(C8<>"",IF(D8=1,COUNTIF($C$28,1),0),0)
Column F: 1, 2, 3, 4 (i.e. seq numbers starting at 1 at F2)
Column G: =INDEX($C$2:$C$50000,MATCH(F2,$E$2:$E$50000,0))
Column H: =COUNTIF(C2:C50000,G2)
Column I: =LEFT(G2,FIND(" ",G2)-1)
Column J: =MID(G2,FIND(" ",G2)+1,100)
Column K: =H2

This will take a *long* time to process based on the size of your dataset but it will return the unique list of combinations with a count... some standard sorting will then get you the answer you seek.

It's really getting Excel to do something slowly that Access could chew up and spit out.

How many rows of data are you working with?

 

[Corgano]

Sorry, change H to =COUNTIF($C$2:$C$50000,G2) in order to maintain the references!

 

[Crystalyzer]

Corgano,

I am going to post this question to the MS Access Other topics forum. Would you mind suggesting a way in Access to "chew up and spit out" what I'm looking for? I tried following your Excel Example but I wasn't even close to successful. I have imported the data to access and I assume a query to the table joined on itself is part of the answer.

See you on the other forum.

Thanks for all your help so far.

Thanks and best regards,
-Lloyd