condensing a list for each category

carpetony

New Member
Joined
May 26, 2008
Messages
11
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
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
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
 

carpetony

New Member
Joined
May 26, 2008
Messages
11
iliace,
thanks for reply, althoughthe result is somewhat like that of I want, it's not something I want to use.
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
So what is the result that you'd like to see? And why don't you want to use what I proposed?
 

carpetony

New Member
Joined
May 26, 2008
Messages
11
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.
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
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.
 

carpetony

New Member
Joined
May 26, 2008
Messages
11
ok.
I have it in a pivot table.

1) Create the separate sheets for each category?
 
Last edited:

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
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.



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



Hope that helps.
 

carpetony

New Member
Joined
May 26, 2008
Messages
11
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.
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
Same option in Excel 2007, under PivotTable Options tab:

 

Forum statistics

Threads
1,081,845
Messages
5,361,665
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top