condensing a list for each category

carpetony

New Member
Joined
May 26, 2008
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
In sort of a follow up to the Condensing A List Thread
http://www.mrexcel.com/forum/showthread.php?t=172764&highlight=condense+list

I would like to condense the list into a series of list for each category.

The example below is a synoptic view with some data and does not include the full table.

I would like to do it with functions, i can do it with vb, at this point, I'm more on a mission to do it without it.

TIA:
Book4
ABCDEFGHIJKLMN
1PlaceNameTimePlacardNumberCategoryCategoryRankingM39NameTimeM40NameTime
21Gaines,Jason0:36:5125m39131??????1??????
32Forrester,Kevin0:37:4924m40322
43Charles,Joel0:38:4723m391233
54Loverich,James0:39:4622m39114
65Eppen,John0:40:4121m39105
76Perez,Jucas0:41:3920t26
87Chesterman,Jay0:42:4419m3997
98Dice,Jenn0:43:3518w3938
109Abbot,Roxanne0:44:3417w3929
1110Clayton,Trevor0:45:3316mss310
1211Limpach,Kevin0:46:3115m39811
1312Wilhelm,Anthony0:47:2914m39712
1413Hurrocks,Henry0:48:2813mss213
Sheet1
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
An easy built-in way to do this is using a PivotTable (it's funny, within the last 10 minutes this is the second time I advocate this Excel feature).

First, you will need to give a name to your blank columns, G and K (and any others). Something like Blank1, Blank2 (or Separator1, Separator2) will do. Next, select your data and go to Tools->PivotTable and PivotChart Report. Follow instructions from there.

Here's a result, I'm guessing this is something along the lines of what you're looking for. It took me under 30 seconds to create and format.

Excel Workbook
ABC
3Sum of Time
4CategoryNameTotal
5m39Charles, Joel0:38
6Chesterman, Jay0:42
7Eppen, John0:40
8Gaines, Jason0:36
9Limpach, Kevin0:46
10Loverich, James0:39
11Wilhelm, Anthony0:47
12m39 Total4:52
13m40Forrester, Kevin0:37
14m40 Total0:37
15mssClayton, Trevor0:45
16Hurrocks, Henry0:48
17mss Total1:34
18tPerez, Jucas0:41
19t Total0:41
20w39Abbot, Roxanne0:44
21Dice, Jenn0:43
22w39 Total1:28
23Grand Total9:14
Sheet4
 
Upvote 0
iliace,
thanks for reply, althoughthe result is somewhat like that of I want, it's not something I want to use.
 
Upvote 0
So what is the result that you'd like to see? And why don't you want to use what I proposed?
 
Upvote 0
I would like the results to look like they do in the example data, in their own table for each category (m39, m40, w39. . .)

I played with the pivot table, and at the point I would much rather create a vba procedure to generate the tables.

Ideally, I want the results to be real time, so as each rider finishes the rankings can be viewed as the their number is entered and finish time calculated.
 
Upvote 0
Right, but if you put the category in the Page area (Report Filter in 2007), you can have Excel generate a separate sheet for each category. It will be a pivot table and will update each time you refresh the Pivot cache.
 
Upvote 0
ok.
I have it in a pivot table.

1) Create the separate sheets for each category?
 
Last edited:
Upvote 0
Ok, you go to the PivotTable toolbar and choose the Show Pages option. If this option is not available (like it isn't on my crazy installation of Excel), find it under Data in Customize dialog box.

showPages1.jpg


Once you click OK, you will see that Excel creates an individual sheet for each of your categories, using the current PivotTable layout:

showPages2.jpg


Hope that helps.
 
Upvote 0
Yeah, I can't find it, I'm using '07, I'm going to try and back configure it for '03 on the other machine when I get a chance.

If you want to take a look at the file I came up with and the HTML results I get generated from it you can check out my website. carpetony.com

I got links to the HTML result files.

On the XLS link, there's one for the file.

Thanks for the help.
 
Upvote 0
Same option in Excel 2007, under PivotTable Options tab:

PivotReportFilterPages.jpg
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top