Prev: Password entry userform
Next: Redirecting ALT+1 keystrokes to run routine
From: Shane Devenshire on 26 Mar 2010 17:44
Hi,

One can do this with a pivot table:

1. Insert a column in the data source between the customer and type columns
and give it a title, I will call it X.
2 Enter the formula =LEFT(C2,1) and fill it down. Assumption is that data
starts in A1 with one row of titles.
3. Insert the pivot table.
4. Add the Name, Customer, X and Type into the Row area, add the Region to
the Values (Data) area.
5. Right-click the Name field and choose Field Setting, set Subtotals to
None and click Layout, check Insert blank line after each item and click OK
twice.
6. Repeat step 5 on column X.
7. Right-click the Customer field and choose Field Setting, set Subtotals to
None, click OK.
8. High the X column or
8a. Select the entire pivot table and choose Copy, and then choose Edit,
Paste Special Values.
9a. Delete the X column.

You can record a macro to do this also.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"fzl2007" wrote:

> On Mar 26, 11:07 am, dan dungan <stagerob...(a)yahoo.com> wrote:
> > Will a pivot table work for you?
>
>
> No. It doesn't do exactly what I describe.
> .
>
From: Shane Devenshire on 26 Mar 2010 17:48
And here is a copy of the results

Name customer type Total
1100813 John a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

1102312 Amy a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

1102367 Todd a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

I negelected to tell you to turn off column totals - Pivot Table, Table
Options...
This was all done in 2003 but you can do the same in 2007 or 2010.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"fzl2007" wrote:

> On Mar 26, 11:07 am, dan dungan <stagerob...(a)yahoo.com> wrote:
> > Will a pivot table work for you?
>
>
> No. It doesn't do exactly what I describe.
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Password entry userform
Next: Redirecting ALT+1 keystrokes to run routine