Excel/Access Question

[NakaNaka]

Hey guys. At work, I'm putting together a database of information, and it looks something like this (though much more extensive)

001 A B C D
002 B C D E F G
003 G H A
004 A D C B E F Q Z
005 C B D A
006 D

What I'm trying to say is that each item gets multiple categories. There are a different number of categories per item and items can be in different columns. A will not always be in column one. There are thousands of these. There are hundreds upon hundreds of categoris. What I want to do is generate a master list of every category and its frequency. I want to see

2 A
4 B
3 C
1 D

etc etc. You get the point. I'm willing to work in Excel or Access, whatever works. Is this possible? I hope so.

Thanks
-Phil

 

[sonoma1993]

I would start by trying with access since it deals more with database.

 

[theknight571]

What do you have the data in now? and how do you have it setup?

i.e. Excel with each item in a different column; in a database with 001 in a field and the a,b,c in another field; text file, comma delimited (sp?)

 

[NeoV]

This is the perfect setup for a Pivot Table in Excel.

 

[NakaNaka]

Right now it's in excel, with columns. First column is the item number, the rest of the columns are the categories. Each column has one category in it (not multiple categories in a column)

NeoV - I googled pivot tables but it looks kind of complicated. Can you explain how I get started?

 

[NakaNaka]

NeoV - Okay. I did a Pivot table. Here's the problem. Because the same category might be in different columns, it only shows up per category. So it tells me how many times A in in column 1, but then I have to do it again to see how many times its in column 2. Unless I'm doing something wrong which is very very possible.

 

[NeoV]

ok, this still works, you just need to do a little pivot table trick.

let's call your column A "category", and then columns B thru E will be called Cb, Cc, Cd, and Ce

When you set up the pivot table, in the "row" section, you are going to drag the "category" button into that area, and in the "data" section, you are going to drag Cb, Cc, Cd, and Ce there, and make sure it is on "count of".

Hit finish, and you get your pivot table, but it's not in a great format for what you are looking for - here's the trick.

Your pivot table should look like this now - first column says "count" and it has the first category item, in your case "001", and then it has "count of Cb, then the next row "count of Cc", etc, etc. At the top of that is the "data" button - left click on that data button, and drag it where it says "total", and let it go - it will transform your pivot table to a summarize the items you want it to count, with each "letter" in this case getting their own column.

I can email you a sample file if you can't get it to work, let me know.

 

[cleverhandle]

How about perl?


#!/usr/bin/perl

while(<>){
chomp();
@line = split / /, $_;
for ($i=1; $i <= $#line; $i++) {
$totals{$line[$i]}++;
}
}

foreach $key (sort keys %totals) {
print("$key $totals{$key}\n");
}


I'm sure it could be made shorter or more obfuscated, if that's your kind of thing. Even on Windows, I'd rather do this kind of text processing with a perl script than muck around in Excel.

 

[NakaNaka]

NeoV - I'm gonna run this by one of the tech guys at work tomorrow. If he doesn't know Excel very well, I might ask you to do a sample file with like 10 rows of my massive data set. I tried what you said and the table started looking really odd. But thank you so much for your help.

 

[theknight571]

Ok...If the pivot table or Perl script doesn't work for you...

I have a solution that involves saving the file to a CSV text file, and then using Access and VBA to Create a table with the category counts in it.