CODEWITHSUNDEEP
excelvbafilter
Gabriel L. Oliveira
Gabriel L. Oliveira

Reputation: 4062

VB: Filtering data on excel table

In python, using libs to work with excel files, I could do what I want. But now, because I'm trying to learn VBA, I need to ask this question.

I'm working on a worksheet that has around 12 columns, and 50000 rows. This data represents Requests sent to the company. The 5# column represents its code, 10# the time took to finish it. But, for example, rows 5, 10 and 12 could belong to the same Request, and was just divided for organizational purposes.

I need to treat these data, so that I can:

  1. Column 6# represent the person who answered the request. So, I need to put each request on the "person's worksheet". Also, create this worksheet for him before starting to add requests to it.

  2. For each person (worksheet), contabilize request types (Column 2#) attended by him. I.e., create another table on its worksheet showing: Type_Of_Request | Number_of_ocurrences

  3. Create a final Report Worksheet, showing the same table above, but accounting all requests (without person filter)

Obs: I know that most questions on stackoverflow are to solve a specific question, but I'm asking for start routes here. Or even solutions, if possible.

For explanation purposes, I think that explaining the algorithm used in python will help persons who know a little of python and VBA to help me here. So, for each issue:

  1. Create a dict that manages the 6# column data. This dict will have the person's unique name as the key, and for each request that him answered, it will be added to a list pointed to his name (the dict key). Something like: {person1: [request1, request2, request3, ...], ... }

  2. Another dict that manages the 2# column data (the request type). Now, I will have a dict where each entry will have a list showing requests that are of that type. After positioning all requests, I did a simple sum on the list, and filled a table with (key, sum(dict[key])) where dict[key] is the list of requests of same type, and a sum on it returns the total of requests of that type. Something like: {request_type1: [request1, request2, request3, ...], ... }

  3. Well, same of 2, but applying the algorithm on the initial complete table.

I don't know if VB has a dict type like python has (and helps a lot!), even because I'm new on VB. Thanks, a lot, for any help.

Upvotes: 1

Views: 672

Answers (2)

Fionnuala
Fionnuala

Reputation: 91336

You may find this easier with ADO, which works quite well with Excel using the Jet/ACE connection. It also will allow to use rs.CopyFromRecordset to write suitable sets to worksheets.

Upvotes: 0

horatio
horatio

Reputation: 1436

vba does indeed have a dictionary type, but it's usage may not mirror python's implementation. (see: http://msdn.microsoft.com/en-us/library/aa164502%28v=office.10%29.aspx )

you can also create a user defined type ( see: http://msdn.microsoft.com/en-us/library/aa189637%28v=office.10%29.aspx )

If you have a working solution, that is your best jumping-off point. Many of the python string function etc are probably even named the same or close enough for you to easily find them in the language reference.

Upvotes: 3

Related Questions