I have a listview ctrl from which i need to extract a summary.
This listview is populated from access db. My listview is like:
Strategy No: Strategy Group Project Name
9 10 ABC Project
13 12 CDS Enhancement and Support
25 15 Delta Techops T&M
41 10 bbbbbbbb
41 15 cccccccccc
I want the output to be entered to a new excel sheet in following output:
Strategy Group Projects
10 2
12 1
15 2
Total Projects 5
I'm able to transfer whole listview data to excel. But how to get the count of unique items from a column?
i.e. count of each Strategy group is there in listview column 2?
Thanks for the quick reply. Sample workbook is attached.
The listview details are extracted to the excel. The output which i mentioned is where i'm stuck.
Need to extract the details shown as in output from the listview. Thank you
Your solution works great. Thanks. but the output which i mentioned is the one which i've to extract from a listview ctrl in a seperate workbook(Summary sheet). Can u provide a vba code to extract the count of unique items from a listview ctrl?
Do you need the results as i gave you in range D15:E19, IN another sheet in the same workbook or in another workbook? Why don't you use my formula for this?
Do you want to do this using VBA? If yes, i hope some other will be able to helps you because VBA is not my best card! Or just to record in a macro the formulas that i used!
Distinct query cant be used since for one strategy group there may be more than one projects. I need to count how many times a specific strategy group is coming in Listview (2nd col) so that i can get the total projects assigned to that strategy group.
Is there anything like Count(Listview1.Subitems(1) or something? Pls help
Sorry for the confusion. I need VBA code to get the count of similar items in a column of Listview ctrl.
All the solutions provided is for getting the result once its extracted to Excel.
I am not looking for extracting whole data from listview and then apply formulas. Directly extract the count of similar items from Listview ctrl.
If the listview is populated directly from sql, and you aren't adding or removing items in the listview then I guarantee 100% you can do this with a query. Can you post your SQL?
SELECT strategy_group, Count(strategy_group) FROM (SELECT DISTINCT projectstrategy.strategy_no, projectstrategy.strategy_group, projectstrategy.strategy_a, projectstrategy.d_date, projectstrategy.voice_special_infra, projectstrategy.strategy_b, projectstrategy.strategy_c, projectstrategy.strategy_d, projectstrategy.strategy_e, project_details.* FROM projectstrategy, project_details WHERE project_details.window_of_ops IN ( " & sstring & " ) AND project_details.state = ( '" & StateList & "' ) AND projectstrategy.hours_of_ops = '" & HourOps & "' AND projectstrategy.work_type = '" & WorkT & "' AND project_details.penalty_clause = projectstrategy.penalty_clause AND project_details.auto_redundancy = projectstrategy.auto_redundancy AND projectstrategy.d_date = '" & DelDate & "' AND project_details.voice_project = projectstrategy.voice_special_infra AND projectstrategy.political_sensitiviy = '" & PolS & "' AND projectstrategy.facility_vulnerability = '" & FacS & "') sub GROUP BY strategy_group
The syntax may be off slightly, access sql isn't my thing
The other way would be to count the groups as you loop through the listview when populating it - if you use a collection or dictionary, you can just increment the count each time an existing group is encountered
LinkBack URL
About LinkBacks
Register To Reply
Re: count of unique listview items
Bookmarks