Combining & counting data

[bmr]

I have thousands of records of search terms I have extracted from our web
sites. I am trying to combine all the duplicates and give a count after each
search term. Here's an example of the terms:

Search Terms
1 inch heel casual
2 inch brown pumps
2 inch brown pumps
2 inch brown pumps
2 inch brown pumps
2 inch brown pumps
2 inch brown pumps
2 inch brown pumps
2 inch heel

So I have used a pivot table that helps me do just that, except I have to
run a pivot table for each worksheet. I can't figure out how to combine all
the common search terms from all the worksheets and give me back one count
per term. I am using Excel 2003 and the file I have has 103,000 records.

Any ideas would be great,
Beth

 

[Jim Thomlinson]

So your issue is that because you have 103,000 records you can not get all of
the records inot a single sheet and for that reason you have ot have multiple
pivot tables to deal with the data??? Assuming that to be the case you can
use multiple consolidation ranges. Since pivot tables are not constrained to
65k records (they can actually handle potentially millions) you should be
able to get all of your data into a single pivot. Another option ot muliple
consolidation ranges is to laod your records into a database such as Access.
You can then hook your pivot table directly tot he Access database. As you
add records to the database you only need to refresh the pivot table to get
the most current info...

Check out this link on multiple consolidation ranges...

http://www.contextures.com/xlPivot08.html

 

[bmr]

Yes, you have it right and I actually started with a longer list. I tried the
multiple consolidation ranges, but had to add a fake "row" because it
wouldn't make a table with only one field of data. Otherwise, it worked
great. Thanks.

Originally, I loaded all the data into Access. The search term is in the
middle of a very long URL like this
"/graph?node=0&allgroups=1&groupby=id_item_parent&offset=0&nbins=16&allbins=1&attrs=Tier2+sandals|mode+matchallpartial&dym=1&irversion=460
1214463621 192.168.7.245 70540 254.74 241.29 200 29979 -3 9". (sandals is
the term) I was able to delete all the extraneous characters easier in Excel
than Access.

If I could leave it in Access and be able to pull out the search terms, then
count them with Excel, that would be great. I just didn't know how to do
that.