Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem | Solution

Each column can have duplicates. So a client can have been contacted multiple
times re the same problem by the same consultant, sometimes on the same day

Would like a code that

- can tell me how many and which clients have been contacted for the Problem
"Crash". So if client no 7589 has had a crash should be counted as one for
the specified date range

- can tell me how many contacts all client have had in a specified date range
by the Problem reported.

TIA
ngarutoa

You should be able to do this with a pivot table. No code or formulas
required.

If you are not familiar with pivot tables, see Debra Dalgleish's site:
http://www.contextures.com/tiptech.html

Look under P .

--
Regards,
Tom Ogilvy


"ngarutoa" wrote in message
m...

Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem | Solution

Each column can have duplicates. So a client can have been contacted
multiple
times re the same problem by the same consultant, sometimes on the same
day

Would like a code that

- can tell me how many and which clients have been contacted for the
Problem
"Crash". So if client no 7589 has had a crash should be counted as one for
the specified date range

- can tell me how many contacts all client have had in a specified date
range
by the Problem reported.

TIA
ngarutoa

Hello Tom,

Thanks for that

I can use pivot tables but was hoping that there was a way that I could use
code because will be using a sheet where the data will be imported and so
the range will be dynamic not static. Will be utilising pivot tables in the
short term. Until I can figure a way to get the queries working.

Thanks again

You should be able to do this with a pivot table. No code or formulas
required.

If you are not familiar with pivot tables, see Debra Dalgleish's site:
http://www.contextures.com/tiptech.html

Look under P .

"ngarutoa" wrote in message
m...
Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem |
Solution

Each column can have duplicates. So a client can have been contacted

multiple

times re the same problem by the same consultant, sometimes on the
same

day

Would like a code that

- can tell me how many and which clients have been contacted for the

Problem

"Crash". So if client no 7589 has had a crash should be counted as
one for the specified date range

- can tell me how many contacts all client have had in a specified
date

range

by the Problem reported.

TIA
ngarutoa

i use pivot tables with a data that varies in the number of rows


Sub Pivot2()
Dim Curbook As String
Dim lastrow As Long
Application.ScreenUpdating = False
Curbook = ActiveWorkbook.Name
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Worksheets("sheet2").Activate
Cells.Clear
'On Error GoTo Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R3C2:R" & lastrow & "C18").CreatePivotTable
TableDestination:= _
"[" & Curbook & "]Sheet2!R4C2", TableName:="PivotTable2",
DefaultVersion:= _
xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Site")
.Orientation = xlRowField
.Position = 1

End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("FSA")
.Orientation = xlRowField
.Position = 2
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Total Loan / LoC Amount"), _
"Sum of Total Loan / LoC Amount", xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ( _
"Total Loan / LoC Amount")
.Orientation = xlRowField
.Position = 3

End With
ActiveSheet.PivotTables("PivotTable2").PivotFields ( _
"Sum of Total Loan / LoC Amount").NumberFormat = "#,##0.00"




ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable2").Format xlReport6
ActiveSheet.PivotTables("PivotTable2").PivotFields ("FSA").Subtotals =
Array( _
False, True, True, False, False, False, False, False, False, False,
False, False)
Columns("A:E").AutoFit
Application.CommandBars("PivotTable").Visible = False
Application.ScreenUpdating = True
Range("A1").Select
Exit Sub
Last:
MsgBox "Please Remove Subtotals on Sheet1" & Chr(10) & _
" And Then Run Update", vbInformation Or vbSystemModal, "Loan
Import"
End Sub

--


Gary


"ngarutoa" wrote in message
m...
Hello Tom,

Thanks for that

I can use pivot tables but was hoping that there was a way that I could
use code because will be using a sheet where the data will be imported and
so the range will be dynamic not static. Will be utilising pivot tables in
the short term. Until I can figure a way to get the queries working.
Thanks again

You should be able to do this with a pivot table. No code or formulas
required.

If you are not familiar with pivot tables, see Debra Dalgleish's site:
http://www.contextures.com/tiptech.html

Look under P .

"ngarutoa" wrote in message
m...
Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem |
Solution

Each column can have duplicates. So a client can have been contacted

multiple

times re the same problem by the same consultant, sometimes on the
same

day

Would like a code that

- can tell me how many and which clients have been contacted for the

Problem

"Crash". So if client no 7589 has had a crash should be counted as
one for the specified date range

- can tell me how many contacts all client have had in a specified
date

range

by the Problem reported.

TIA
ngarutoa

If the imported data is placed in the same location, you can use a
dynamically defined named range

Name=List
RefersTo= =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)

where list is then used as the source of your pivot table.

As an example.

--
Regards,
Tom Ogilvy

"ngarutoa" wrote in message
m...
Hello Tom,

Thanks for that

I can use pivot tables but was hoping that there was a way that I could
use
code because will be using a sheet where the data will be imported and so
the range will be dynamic not static. Will be utilising pivot tables in
the
short term. Until I can figure a way to get the queries working.

Thanks again

You should be able to do this with a pivot table. No code or formulas
required.

If you are not familiar with pivot tables, see Debra Dalgleish's site:
http://www.contextures.com/tiptech.html

Look under P .

"ngarutoa" wrote in message
m...
Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem |
Solution

Each column can have duplicates. So a client can have been contacted

multiple

times re the same problem by the same consultant, sometimes on the
same

day

Would like a code that

- can tell me how many and which clients have been contacted for the

Problem

"Crash". So if client no 7589 has had a crash should be counted as
one for the specified date range

- can tell me how many contacts all client have had in a specified
date

range

by the Problem reported.

TIA
ngarutoa

Thanks again to both of you

That gets the data into my pivot table. But may have not explained myself
properly re what I expect to report on. Pivot tables don't tell me how many
unique people had crashes just the number of crashes. For example.

Date | Time | ID | Division | Consultant | Client ID | Problem |Solution

Client A345 had a crash which was fixed on 1/1/2005
Client A345 had a crash which was fixed on 15/2/2005.
Client ??? had a bug which was referred on 3/3/2005
Client ??? had a crash which was referred on 5/3/2005
client 7890 had a bug which was fixed on 6/3/2005

The report would show 2 Clients have had crashes not 3.

Again thanks for the help
ngautoa


If the imported data is placed in the same location, you can use a
dynamically defined named range

Name=List
RefersTo= =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)
where list is then used as the source of your pivot table.

As an example.

"ngarutoa" wrote in message
m...
Hello Tom,

Thanks for that

I can use pivot tables but was hoping that there was a way that I
could

use

code because will be using a sheet where the data will be imported
and so the range will be dynamic not static. Will be utilising pivot
tables in

the

short term. Until I can figure a way to get the queries working.

Thanks again

You should be able to do this with a pivot table. No code or
formulas required.

If you are not familiar with pivot tables, see Debra Dalgleish's
site: http://www.contextures.com/tiptech.html

Look under P .

"ngarutoa" wrote in message
m...
Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem |
Solution

Each column can have duplicates. So a client can have been
contacted

multiple

times re the same problem by the same consultant, sometimes on the
same

day

Would like a code that

- can tell me how many and which clients have been contacted for
the

Problem

"Crash". So if client no 7589 has had a crash should be counted as
one for the specified date range

- can tell me how many contacts all client have had in a specified
date

range

by the Problem reported.

TIA
ngarutoa

if you use client id as a row field and as a data field set to count and
use a page field (Problem) to filter on crashes, then the number of rows the
table produces is the number of clients who have had crashes. This could be
gotten from the pivot table with =CountA(A4:A500) or similar.

--
Regards,
Tom Ogilvy

"ngarutoa" wrote in message
m...
Thanks again to both of you

That gets the data into my pivot table. But may have not explained myself
properly re what I expect to report on. Pivot tables don't tell me how
many
unique people had crashes just the number of crashes. For example.

Date | Time | ID | Division | Consultant | Client ID | Problem |Solution

Client A345 had a crash which was fixed on 1/1/2005
Client A345 had a crash which was fixed on 15/2/2005.
Client ??? had a bug which was referred on 3/3/2005
Client ??? had a crash which was referred on 5/3/2005
client 7890 had a bug which was fixed on 6/3/2005

The report would show 2 Clients have had crashes not 3.

Again thanks for the help
ngautoa


If the imported data is placed in the same location, you can use a
dynamically defined named range

Name=List
RefersTo= =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)
where list is then used as the source of your pivot table.

As an example.

"ngarutoa" wrote in message
m...
Hello Tom,

Thanks for that

I can use pivot tables but was hoping that there was a way that I
could

use

code because will be using a sheet where the data will be imported
and so the range will be dynamic not static. Will be utilising pivot
tables in

the

short term. Until I can figure a way to get the queries working.

Thanks again

You should be able to do this with a pivot table. No code or
formulas required.

If you are not familiar with pivot tables, see Debra Dalgleish's
site: http://www.contextures.com/tiptech.html

Look under P .

"ngarutoa" wrote in message
m...
Any suggestions for the following would be appreciated

Have a workbook with columns of data as follows:

Date | Time | ID | Division | Consultant | Client ID | Problem |
Solution

Each column can have duplicates. So a client can have been
contacted

multiple

times re the same problem by the same consultant, sometimes on the
same

day

Would like a code that

- can tell me how many and which clients have been contacted for
the

Problem

"Crash". So if client no 7589 has had a crash should be counted as
one for the specified date range

- can tell me how many contacts all client have had in a specified
date

range

by the Problem reported.

TIA
ngarutoa