Excel 08 - Splitting tables

[PatrickL]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am not an Excel expert and hope someone can help.
I have a table containing a schedule of events. There are multiple entries for each event.
I would like to create separate tables for each event type and populate with the data from the original but cannot get my head round how to do this.
So table
Ref Type Date Location
1 KC 01/02/2008 a
2 RP 02/02/2008 b
3 KC 04/07/2008 c
4 PE 07/07/2008 a
5 QE 23/08/2008 a
6 RP 24/08/2008 d
7 TR 03/09/2009 a
8 TR 03/09/2008 b
9 RP 04/05/2008 c
10 RP 06/01/2008 c

Becomes
Ref Type Date Location
1 KC 01/02/2008 a
3 KC 04/07/2008 c

Ref Type Date Location
2 RP 02/02/2008 b
6 RP 24/08/2008 d
9 RP 04/05/2008 c
10 RP 06/01/2008 c

Ref Type Date Location
4 PE 07/07/2008 a

Ref Type Date Location
5 QE 23/08/2008 a

Ref Type Date Location
7 TR 03/09/2009 a
8 TR 03/09/2008 b

Does anyone have any ideas? My solutions to date have resulted in tables the same size as the original with lots of empty rows that need sorting every time something changes to ensure the results are together!!

Thanks for you help

Patrick

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am not an Excel expert and hope someone can help.
I have a table containing a schedule of events. There are multiple entries for
each event.
I would like to create separate tables for each event type and populate with
the data from the original but cannot get my head round how to do this.
So table
Ref Type Date Location
1 KC 01/02/2008 a
2 RP 02/02/2008 b
3 KC 04/07/2008 c
4 PE 07/07/2008 a
5 QE 23/08/2008 a
6 RP 24/08/2008 d
7 TR 03/09/2009 a
8 TR 03/09/2008 b
9 RP 04/05/2008 c
10 RP 06/01/2008 c

Becomes
Ref Type Date Location
1 KC 01/02/2008 a
3 KC 04/07/2008 c

Ref Type Date Location
2 RP 02/02/2008 b
6 RP 24/08/2008 d
9 RP 04/05/2008 c
10 RP 06/01/2008 c

Ref Type Date Location
4 PE 07/07/2008 a

Ref Type Date Location
5 QE 23/08/2008 a

Ref Type Date Location
7 TR 03/09/2009 a
8 TR 03/09/2008 b

Does anyone have any ideas? My solutions to date have resulted in tables the
same size as the original with lots of empty rows that need sorting every time
something changes to ensure the results are together!!

Thanks for you help

Patrick

Do they really need to be split? I think you can view it the way you want by
filtering the list. Or, you can sort the list on Type and then break it up
manually.

 

[PatrickL]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am not an Excel expert and hope someone can help.
I have a table containing a schedule of events. There are multiple entries for
each event.
I would like to create separate tables for each event type and populate with
the data from the original but cannot get my head round how to do this.
So table
Ref Type Date Location
1 KC 01/02/2008 a
2 RP 02/02/2008 b
3 KC 04/07/2008 c
4 PE 07/07/2008 a
5 QE 23/08/2008 a
6 RP 24/08/2008 d
7 TR 03/09/2009 a
8 TR 03/09/2008 b
9 RP 04/05/2008 c
10 RP 06/01/2008 c

Becomes
Ref Type Date Location
1 KC 01/02/2008 a
3 KC 04/07/2008 c

Ref Type Date Location
2 RP 02/02/2008 b
6 RP 24/08/2008 d
9 RP 04/05/2008 c
10 RP 06/01/2008 c

Ref Type Date Location
4 PE 07/07/2008 a

Ref Type Date Location
5 QE 23/08/2008 a

Ref Type Date Location
7 TR 03/09/2009 a
8 TR 03/09/2008 b

Does anyone have any ideas? My solutions to date have resulted in tables the
same size as the original with lots of empty rows that need sorting every time
something changes to ensure the results are together!!

Thanks for you help

Patrick

Do they really need to be split? I think you can view it the way you want by
filtering the list. Or, you can sort the list on Type and then break it up
manually.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

--[/QUOTE]
Thanks Bob
I can break the table manually but what I would like to do is get Excel to input automatically all the occurrences of each event type into separate consecutive columns of separate sheets. So Sheet 2 would have columns with 'KC' Sheet 3 'RP' etc.

Thanks
Patrick

 

[Bob Greenblatt]

Thanks Bob
I can break the table manually but what I would like to do is get Excel to
input automatically all the occurrences of each event type into separate
consecutive columns of separate sheets. So Sheet 2 would have columns with
'KC' Sheet 3 'RP' etc.

Thanks
Patrick

Sorry, but you are probably going to have to do that manually. Sort the lsit
first and it should be easier.

 

[CyberTaz]

Hey Bob -

Couldn't that be accomplished using the Advanced Filter with a separate
criteria range on each target sheet & using the "Type" as the criteria? That
seems like it would be a little quicker than sorting the list then selecting
& copy/pasting each group one at a time... And you wouldn't even need to
sort the main list.

Regards |:>)
Bob Jones
[MVP] Office:Mac