Sort data into order of product

Hi there,

I've got a list of products for my company but it is listed in product
group, the products for that group coming underneath.. like this:

MT-AUDIO-MOUNTS:
Vogels VRS045 VCR/DVD support in black
Vogels VRS045 VCR/DVD support in silver
Vogels VRS045 VCR/DVD support in white

MT-SPEAKER-WALL:
Vogels VLB100 tilting speaker wall mount in black
Vogels VLB1015 center speaker wall mount in silver
Vogels VLB1025 speaker wall mount in silver
Vogels VLB200 speaker wall mount in black
Vogels VLB50 speaker wall mount in black
Vogels VLB50 speaker wall mount in silver
Vogels VLB50 speaker wall mount in white

MT-SPEAKER-WALL:
Kef 3 x wall brackets for KHT1001

MT-AUDIO-MOUNTS:
B-Tech BT325 disco/PA speaker mount in black
B-Tech BT325K fixing kit for BT325
B-Tech BT325X adaptor for external mount

I want if possible to create a new list which will show the product group
with the products underneath..like this

MT-AUDIO-MOUNTS:
B-Tech BT325 disco/PA speaker mount in black
B-Tech BT325K fixing kit for BT325
B-Tech BT325X adaptor for external mount
Vogels VRS045 VCR/DVD support in black
Vogels VRS045 VCR/DVD support in silver
Vogels VRS045 VCR/DVD support in white

MT-SPEAKER-WALL:
Kef 3 x wall brackets for KHT1001
Vogels VLB100 tilting speaker wall mount in black
Vogels VLB1015 center speaker wall mount in silver
Vogels VLB1025 speaker wall mount in silver
Vogels VLB200 speaker wall mount in black
Vogels VLB50 speaker wall mount in black
Vogels VLB50 speaker wall mount in silver
Vogels VLB50 speaker wall mount in white

Anybody got any ideas?

TIA...Jack

A cursory look suggests that you could use a macro to look for "wall" with
INSTR.
Look in VBA help for instr and incorporate into a loop.

--
Don Guillett
SalesAid Software
[email protected]
"Jackmac" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> I've got a list of products for my company but it is listed in product
> group, the products for that group coming underneath.. like this:
>
> MT-AUDIO-MOUNTS:
> Vogels VRS045 VCR/DVD support in black
> Vogels VRS045 VCR/DVD support in silver
> Vogels VRS045 VCR/DVD support in white
>
> MT-SPEAKER-WALL:
> Vogels VLB100 tilting speaker wall mount in black
> Vogels VLB1015 center speaker wall mount in silver
> Vogels VLB1025 speaker wall mount in silver
> Vogels VLB200 speaker wall mount in black
> Vogels VLB50 speaker wall mount in black
> Vogels VLB50 speaker wall mount in silver
> Vogels VLB50 speaker wall mount in white
>
> MT-SPEAKER-WALL:
> Kef 3 x wall brackets for KHT1001
>
> MT-AUDIO-MOUNTS:
> B-Tech BT325 disco/PA speaker mount in black
> B-Tech BT325K fixing kit for BT325
> B-Tech BT325X adaptor for external mount
>
> I want if possible to create a new list which will show the product group
> with the products underneath..like this
>
> MT-AUDIO-MOUNTS:
> B-Tech BT325 disco/PA speaker mount in black
> B-Tech BT325K fixing kit for BT325
> B-Tech BT325X adaptor for external mount
> Vogels VRS045 VCR/DVD support in black
> Vogels VRS045 VCR/DVD support in silver
> Vogels VRS045 VCR/DVD support in white
>
> MT-SPEAKER-WALL:
> Kef 3 x wall brackets for KHT1001
> Vogels VLB100 tilting speaker wall mount in black
> Vogels VLB1015 center speaker wall mount in silver
> Vogels VLB1025 speaker wall mount in silver
> Vogels VLB200 speaker wall mount in black
> Vogels VLB50 speaker wall mount in black
> Vogels VLB50 speaker wall mount in silver
> Vogels VLB50 speaker wall mount in white
>
> Anybody got any ideas?
>
> TIA...Jack
>

Are those entries in a single column?

Are the groups always separated by a blank cell?

Are the group descriptions always in upper case?

I'm gonna guess yes to the first and second question and ignore your answer to
the third...

Your data will be much easier to sort if you have the group description on each
row.

One way to do that is:
Insert a new column A.
In A1, put: =B1
(Just to get us started)

In A2, put: =IF(B1="",B2,A1)
And drag down the list.

Select column A.
Edit|copy
edit|paste special|Values

Now insert another new column A and put this in A1:
=IF(COUNTIF(B$1:B1,B1)=1,B1,IF(or(B1=C1,c1=""),"delete",""))
and drag down.

Select column A.
Edit|copy
edit|paste special|Values
Apply data|filter|autofilter to column A
show the deletes
select those cells
edit|delete|entire row.
(and data|filter|show all)

Now select A:C
Data|Sort
no header row
primary sort is column B
secondary sort is column A (descending)
tertiary sort is by column C

Then delete columns A:B
Or if you need a blank row between each group, do a Data|subtotals using column
B.



Jackmac wrote:
>
> Hi there,
>
> I've got a list of products for my company but it is listed in product
> group, the products for that group coming underneath.. like this:
>
> MT-AUDIO-MOUNTS:
> Vogels VRS045 VCR/DVD support in black
> Vogels VRS045 VCR/DVD support in silver
> Vogels VRS045 VCR/DVD support in white
>
> MT-SPEAKER-WALL:
> Vogels VLB100 tilting speaker wall mount in black
> Vogels VLB1015 center speaker wall mount in silver
> Vogels VLB1025 speaker wall mount in silver
> Vogels VLB200 speaker wall mount in black
> Vogels VLB50 speaker wall mount in black
> Vogels VLB50 speaker wall mount in silver
> Vogels VLB50 speaker wall mount in white
>
> MT-SPEAKER-WALL:
> Kef 3 x wall brackets for KHT1001
>
> MT-AUDIO-MOUNTS:
> B-Tech BT325 disco/PA speaker mount in black
> B-Tech BT325K fixing kit for BT325
> B-Tech BT325X adaptor for external mount
>
> I want if possible to create a new list which will show the product group
> with the products underneath..like this
>
> MT-AUDIO-MOUNTS:
> B-Tech BT325 disco/PA speaker mount in black
> B-Tech BT325K fixing kit for BT325
> B-Tech BT325X adaptor for external mount
> Vogels VRS045 VCR/DVD support in black
> Vogels VRS045 VCR/DVD support in silver
> Vogels VRS045 VCR/DVD support in white
>
> MT-SPEAKER-WALL:
> Kef 3 x wall brackets for KHT1001
> Vogels VLB100 tilting speaker wall mount in black
> Vogels VLB1015 center speaker wall mount in silver
> Vogels VLB1025 speaker wall mount in silver
> Vogels VLB200 speaker wall mount in black
> Vogels VLB50 speaker wall mount in black
> Vogels VLB50 speaker wall mount in silver
> Vogels VLB50 speaker wall mount in white
>
> Anybody got any ideas?
>
> TIA...Jack

--

Dave Peterson