Add up based on given values - VBA

Dear Friends,

I am tied up with many issues while programming for a bigger project.
Kindly refer to the attached excel file.

Column named 'CSQ Name' contains the call center descriptions for various departments and vehicle franchise.
I need a report that has only CSQ Names that end with 'FORD', 'JLR', POC' and 'FM' (valid ones). Rest are ignored.
In a particular valid CSQ Name say 'CEN-ENG-SAL-FORD' I need to know how many 1s are read in the 'Disposition' column. Plus I need the total count for both dispositions, 1 and 2.
The above should be done for all valid CSQ Names.

Kindly advise how I would go about it using VBA.
I need the most efficient method, as this is just a part of a bigger project and involves 70,000+ rows.
Sample result report also shown in 2nd sheet.

Much appreciate any help in this regards,
Philip

I'm thinking of a Dictionary approach with the Keys incremented per Item

I'll see what I can do - tomorrow!

May be we can arrive it using Countif() function.

But please let us know where is your expected result and how you are arriving it for giving exact solution

Considering the Data Size can we go for Pivot Table?

Please confirm

I am really confuse about how you are arriving the values

Can you please explain it for these two from your expected result?

	A	B	C
1	CSQ Name	Calls Presented (1s and 2s)	Calls Handled (1s)
2	ALA-ARA-SAL-FORD	116	89
3	ALA-ARA-SAL-JLR	134	110

Any method is fine with me, however it need to be done via VBA.
My clients are least bothered with the method but more concerned with speed, accuracy & user usage simplicity.

Explanation:

If there is an occurance of 'ALA-ARA-SAL-FORD' and the corresponding 'Disposition' reads '1', then say a 3 dimension(3D) array would be updated with '1' and the 3D also updates as '1'.
'ALA-ARA-SAL-FORD' ---------- 1-------------- 1
If there is a second occurance, program would check again the 'Disposition' column. If it reads '1', then the new array value would be updated as:-
'ALA-ARA-SAL-FORD' ---------- 2-------------- 2
If however, on another occurance 'Disposition' reads '2' then the array would be updated as:-
'ALA-ARA-SAL-FORD' ---------- 2-------------- 3

Hope this clarifies...
I don't know how to be more clear than this... :-)

Thanks,
Philip

The ALA-ARA-SAL-FORD* is available only in 4889 row alone.

Without any proper description I cannot go further...

Originally Posted by pjacob

Yes, that was only a sample file with less data.
I have now included one with more data.

Please don't waste our time

When you show the sample (limited) data then you should have adjusted the expected result also.

That is the reason in three continuous posts I have asked for the description about how you are arriving the expected result. But you simply said that is the expected result.

We are not here to do the data study since your sample data was 8000 rows of data.

Sorry I cannot continue with this thread since I feel I am wasting my time, I hope someone will provide you the solution.

Best of luck!

Hi zbor,

I am quite new to these forums.
I have read the rules though & keep upto it.

It did not pass me that I had to provide data and result as Exact, neither was such matter mentioned in the rules.
I thought an expert might understand a sample with an explanation given.
Now, that I have understood your recommendation, I am working on it right now and will post the exact data->result in a while. Thank you.

Just to point out though from the forum rules...
A vicious delivery may result in possibly good suggestions being ignored. Don't be the cause of that.
•No matter how angry or upset you may be over any issue, courteous discourse will be expected at all times.

I should be the one offended from that matter; refer the earlier post(sithsense) being very rude.
But I have taken it lightly in the spirit of obtaining the answer from other experts/professionals.

Thanks,
Philip

Yes, Sir.
Got it.
Will adhere to that.
Won't repeat it ever again, I promise.

Hi Philip,

try this:

Please Login or Register  to view this content.

* Obviously, the code is based on your original sample

Hi xladept,

Thank you very much.
I have just read through your code and in the initial analyses itself, I can vouch that this is exactly what I am looking for.
I am not at work today, as it is weekend in this part of the world.
I will implement this first thing on Saturday at office and will let you know on the outcomes.
No worries, I am sure this is as per the logic I mentioned and so the actual set of data does not matter.
Besides, now that I have the basic codes to work on a dictionary object and the concept itself, I can easily improvise on this, if any changes is required.

Really appreciate your time on this.

Just a query though, is the dictionary object initialized with the sheet1 range?
I don't seem to have found any line to that effect. Maybe it was an oversight?
Please let me know.

I am going to mark your reputation on this and leave the thread as is.
Once I run the code and every query is dealt with to satisfaction, I would mark the code as solved.
I am guessing that's how things are done around here... Is it?

Good Day & Many Thanks,
Philip

Hi Philip,

Thanks for the rep!

I'm glad you like my code

The dictionaries, I used two, are named B and O - They are separate from the sheet, they are dimensioned as objects and

Please Login or Register  to view this content.

This code enables them and

Please Login or Register  to view this content.

This code initializes the respective keys (which I named Key in the code).

After the first, and only pass:

Please Login or Register  to view this content.

This code declares the key array and the items by key are the accumulation of B both 1 and 2 and O just 1's.

Hi Philip,

Excellent! Glad to hear that it's perfect, you've commented the code nicely

Orrin