I have data organized in the following copied below. I am going to have a lot
of these tables with repeat data. I need to know how I can simplify this
table so that each item in the left column only appears once and all the
clicks in the right column aggregate for those items that appeared more than
once in the first row. For example, the new table would have only one row
called (image), and the number of clicks would say 39 because that is the sum
of 20 and 19. How can I get this process to automate? Ideally, this would
also present items in order from largest to smallest once aggregation has
been completed.

Help?

Thanks,

Marc
(here's the table below)

Link In Message Clicks

online version 30
(image) 20
Find a Class 20
(image) 19
Find a Class 15
creative non-fiction 14
memoirs 12
digital photographs 11
creative non-fiction 10
stories about an organization 9
list of recommended courses 9
Dreamweaver 8
digital photographs 8
typography 7
landscaping 6
InDesign 6
Draw analytically 6
memoirs 5
Flash 5
graphics applications 5
Dreamweaver 5
Draw analytically 4
Collaborate with printers 4
Photoshop 4
Request a Catalog 4
landscaping 4
typography 3
stories about an organization 3
Collaborate with printers 3
graphics applications 3
Photoshop 3
Request a Catalog 3
list of recommended courses 3
list of recommended courses 2
online version 2
Flash 2
Flash 2
InDesign 2
Update Mailing Address 2
Our online bookstore 2
online version 1
Our online bookstore 1
list of recommended courses 1
creative non-fiction 1
memoirs 1
Draw analytically 1
landscaping 1
Dreamweaver 1
typography 1
stories about an organization 1
Collaborate with printers 1
InDesign 1
digital photographs 1
graphics applications 1
Photoshop 1
Our online bookstore 1
list of recommended courses 1
creative non-fiction 1

Marc,

this requires a few steps and assumes you original data are in columns A&B

1. Select your categories then
Data|Filter|Advanced filter
select unique items
select copy to new location and enter a location (This assumes D1)
OK

The in e1 enter the formula
=SUMPRODUCT(($A$1:$A$100=D1)*($B$1:$B$100))
and drag down

Mike


"Marc" wrote:

I have data organized in the following copied below. I am going to have a lot
of these tables with repeat data. I need to know how I can simplify this
table so that each item in the left column only appears once and all the
clicks in the right column aggregate for those items that appeared more than
once in the first row. For example, the new table would have only one row
called (image), and the number of clicks would say 39 because that is the sum
of 20 and 19. How can I get this process to automate? Ideally, this would
also present items in order from largest to smallest once aggregation has
been completed.

Help?

Thanks,

Marc
(here's the table below)

Link In Message Clicks

online version 30
(image) 20
Find a Class 20
(image) 19
Find a Class 15
creative non-fiction 14
memoirs 12
digital photographs 11
creative non-fiction 10
stories about an organization 9
list of recommended courses 9
Dreamweaver 8
digital photographs 8
typography 7
landscaping 6
InDesign 6
Draw analytically 6
memoirs 5
Flash 5
graphics applications 5
Dreamweaver 5
Draw analytically 4
Collaborate with printers 4
Photoshop 4
Request a Catalog 4
landscaping 4
typography 3
stories about an organization 3
Collaborate with printers 3
graphics applications 3
Photoshop 3
Request a Catalog 3
list of recommended courses 3
list of recommended courses 2
online version 2
Flash 2
Flash 2
InDesign 2
Update Mailing Address 2
Our online bookstore 2
online version 1
Our online bookstore 1
list of recommended courses 1
creative non-fiction 1
memoirs 1
Draw analytically 1
landscaping 1
Dreamweaver 1
typography 1
stories about an organization 1
Collaborate with printers 1
InDesign 1
digital photographs 1
graphics applications 1
Photoshop 1
Our online bookstore 1
list of recommended courses 1
creative non-fiction 1

Have you tried putting the data into a Pivot Table? Link in Message is the
Row and clicks are the Data.

HTH,

AW

"Marc" wrote:

I have data organized in the following copied below. I am going to have a lot
of these tables with repeat data. I need to know how I can simplify this
table so that each item in the left column only appears once and all the
clicks in the right column aggregate for those items that appeared more than
once in the first row. For example, the new table would have only one row
called (image), and the number of clicks would say 39 because that is the sum
of 20 and 19. How can I get this process to automate? Ideally, this would
also present items in order from largest to smallest once aggregation has
been completed.

Help?

Thanks,

Marc
(here's the table below)

Link In Message Clicks

online version 30
(image) 20
Find a Class 20
(image) 19
Find a Class 15
creative non-fiction 14
memoirs 12
digital photographs 11
creative non-fiction 10
stories about an organization 9
list of recommended courses 9
Dreamweaver 8
digital photographs 8
typography 7
landscaping 6
InDesign 6
Draw analytically 6
memoirs 5
Flash 5
graphics applications 5
Dreamweaver 5
Draw analytically 4
Collaborate with printers 4
Photoshop 4
Request a Catalog 4
landscaping 4
typography 3
stories about an organization 3
Collaborate with printers 3
graphics applications 3
Photoshop 3
Request a Catalog 3
list of recommended courses 3
list of recommended courses 2
online version 2
Flash 2
Flash 2
InDesign 2
Update Mailing Address 2
Our online bookstore 2
online version 1
Our online bookstore 1
list of recommended courses 1
creative non-fiction 1
memoirs 1
Draw analytically 1
landscaping 1
Dreamweaver 1
typography 1
stories about an organization 1
Collaborate with printers 1
InDesign 1
digital photographs 1
graphics applications 1
Photoshop 1
Our online bookstore 1
list of recommended courses 1
creative non-fiction 1