Solved: Data Summary

[phendrena]

Hi,
I've got two worksheets setup : Data and Summary

In the data sheet there is a list of inbound contacts, a list of contact numbers and a list of why they called.

In the summary sheet i need to create a dynamic list that will show :
Contact | Number | Total Of Why Called | Reason 1 | Reason 2 | Reason 3 |

The Contact & Number fields will contain duplicate entries in the data sheet as people can call multiple times.

The Summary should therefore only list these details once.
The third column, Total Of Why Called would then give a total of how many times they have called.

Reason 1/2/3 would then show how many times the person has called based on what has been entered into the Why Called column (Reason 1/2/3)

Can anyone suggest a good why of doing with VBA.

Thanks,

[mdmackillop]

Have you tried using a pivot table?

[phendrena]

Have you tried using a pivot table?
It's a shared workbook as such i can't use a pivot.
To be honest i try and avoid using pivots - i like to try and sort these things out myself!
(complicated SUMPRODUCT stuff in other workbooks, but in this one..... that just won't cut it!)

[stanl]

maybe dcount() or dcountA... dunno w/out seeing your layout. Stan

[Bob Phillips]

Why doesn't SP not cut it?

[phendrena]

Why doesn't SP not cut it?
Ok SP could be used, but then i'd need to start with a list of dealership thats well over 1000 and then list them all. What i need is the MI to create a dynamic list.

I've added the workbook for you to look at. (InboundData worksheet & MI worksheet)

[Bob Phillips]

I get Project Unviewable in the VBIDE.

[phendrena]

I don't think i unshared the workbook so you might need to unshare it...

[phendrena]

File posted again, un-shared.
Project should be viewable now.

[Bob Phillips]

Public Function ContactLog()
Dim mDealers As Collection
Dim mDealer As Variant
Dim mLastrow As Long
Dim i As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set mDealers = New Collection
With Worksheets("InboundData")

mLastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For i = 2 To mLastrow

If .Cells(i, "B").Value = "Dealer" Then

mDealers.Add .Cells(i, "C").Value, .Cells(i, "C").Value
End If
Next i
On Error GoTo 0
End With

With Worksheets("MI")

i = 52
For Each mDealer In mDealers

.Cells(i, "C").Value = mDealer
i = i + 1
Next mDealer
.Range("B52").Resize(i - 1).Formula = "=INDEX(InboundData!E:E,MATCH(C52,InboundData!C:C,0))"
.Range("D52").Resize(i - 1).Formula = "=COUNTIF(InboundData!C:C,C52)"
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function

[phendrena]

That's brilliant! Works a treat! Many many thanks :)