Excel Macros

[tarunshrivastava01]

I am having excel sheet with huge data. I want the vb macros to take out the unique count of Column 'Computer Name' by filtering the column 'Location'. Like:

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Row1
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="class: xl74, width: 125"]Computer Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="class: xl74, width: 125"]Something[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="class: xl74, width: 125"]Something[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="class: xl74, width: 125"]Something[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="class: xl74, width: 125"]Location[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>Row2
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]India
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Row3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]India
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Row4
[/TD]
[/TR]
[TR]
[TD="width: 125"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]India
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
Row5
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]India
[/TD]
[/TR]
[TR]
[TD]Row6
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]India
[/TD]
[/TR]
[TR]
[TD]Row7
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop11
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]China
[/TD]
[/TR]
[TR]
[TD]Row8
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop12
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]China
[/TD]
[/TR]
[TR]
[TD]Row9
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop13
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]China
[/TD]
[/TR]
[TR]
[TD]Row10
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop11
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]China
[/TD]
[/TR]
[TR]
[TD]Row11
[/TD]
[TD][TABLE="width: 125"]
<colgroup><col width="125"></colgroup><tbody>[TR]
[TD="width: 125"]Desktop13
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]China
[/TD]
[/TR]
[TR]
[TD]Row12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


First it look for India (Column 'Location') and take the unique count for Column 'Computer Name' from row2 to row6 means filtering by India in column 'Location' i.e., Unique count = 3(Desktop1-3) and then look for Column 'Location' China and take the unique count for column 'Computer Name' from row6-row10.

 

[royUK]

Try using the Macro Recorder to get started. post back for help adapting the generated code

 

[xrichardx]

Excel Filter function should work as per your requirement.

Cheers
Rich