Compressing Data in Sheet

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi,
I have a sheet with several pieces of data arranged like so:

Column 1 Column 2

a.dll ! 0x000001234 56
a.dll ! 0x000001234 64
a.dll ! 0x000001234 72
b.dll ! 0x012345689 89
c.dll ! 0x012345890 100

How do I compress the repeated sets of say a.dll into one row with all
values pertaining to a.dll in column 2 summed up so to read something
like:

a.dll ! 0x0000001234 182
b.dll ! 0x012345689 89
etc


???

Thanks
 
If the data is sorted by column A, you could add headers (in row 1) and then use
Data|subtotals.

Then use the outlining symbols to the left to hide the details.

Or you could add headers and then do data|Pivottable to get a nice summary
report. With pivottables, the data doesn't have to be sorted.

And if you've never use pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
Dave said:
If the data is sorted by column A, you could add headers (in row 1) and then use
Data|subtotals.

Then use the outlining symbols to the left to hide the details.

Or you could add headers and then do data|Pivottable to get a nice summary
report. With pivottables, the data doesn't have to be sorted.

And if you've never use pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
Click to expand...

Ok, I used the subtotals function. But the annoying feature is this: If
I now have data in seperate columns like-


Test 1 Bytes Test 2 Bytes

a.dll 123 a.dll 124
a.dll 124 a.dll 123

If I perform subtotal on one of the columns, it creates an extra row on
the the other column. Is there a way I can mass-remove all blank rows
from my spreadsheet?
 
Is this two questions?

#1. Nope. But I'd suggest that you put all your data in a single column.
It'll make life easier for further processing.

If the data has to be in different columns (some kind of segregation of
data???), I would add an indicator column to show the difference.

#2. If you can pick out a column that always has data in it when that row is
used, you can:

Select that column
edit|Goto|Special|Blanks
edit|Delete|entire row

This will delete the entire row.

Brian wrote:
 
Dave said:
Is this two questions?

#1. Nope. But I'd suggest that you put all your data in a single column.
It'll make life easier for further processing.

If the data has to be in different columns (some kind of segregation of
data???), I would add an indicator column to show the difference.

#2. If you can pick out a column that always has data in it when that row is
used, you can:

Select that column
edit|Goto|Special|Blanks
edit|Delete|entire row

This will delete the entire row.

Brian wrote:
Click to expand...

Solution #2 worked. Thanks!
 
Back
Top