Using Macro to Extract unique data with multiple criteria

[Xiaojiang]

Dear all,

I have a spreadsheet include 3 columns and 20000 rows, I attached my sample file as below. I want to use dictionary object to extract data in this case. Now I must filter by manual, It takes alot of time. Please help me to improve it.

Thanks in advanced.

[Xiaojiang]

Who can help me?

[skywriter]

Try this.

Please Login or Register  to view this content.

[Xiaojiang]

Thank Skywrite so much.

But the result show incorrect, not match with the result that I wrote in my file above.

When I filter Store Column with criteria : CA001 and Filter Code Column HA001, Price show 10, 10, 10. It is the same Price is 10, I ignore this value, don not get data to Result range.

Could you help me to improve this code.

[skywriter]

You said you wanted to use a dictionary which is how you create a unique list. I gave you a unique list. It's not apparent to me by your example what you are trying to do. Anyway I spent a while putting this together, so I'm done. Good Luck.

[Xiaojiang]

Thank you very much.

So how to improve your code to get data that I desire?

Thank for your help.

[karedog]

If you can drop the requirement to use dictionary, you can try this :

Please Login or Register  to view this content.

[jindon]

Xiaojiang

If you can upload a workbook with EXACT desired result based on the data you uploaded in your first post, it will be clearer to understand how you want it.

[Xiaojiang]

Thank Karedog alot.

The result show correct as my request. You use colllection in this case.

I really want to use Dictionary Object in this case, but I can not find out the algorithm for dictionary object in this case.

Could you help me?

[Xiaojiang]

Xiaojiang

If you can upload a workbook with EXACT desired result based on the data you uploaded in your first post, it will be clearer to understand how you want it.

Hi Jindon,

Thank you very much for your concern with my problem.

The exact desired result like the result range that I describe in my first post. Raw data : Range("C4 : E19") and Result show as Range("I4 : K9"). But I want use dictionary object in this case not Collection as karedog do it above. I really can not find out the algorithm if I use dictionary object.
I know you are good at in using dictionary object.

Could you help me?

Thanks so much.

[jindon]

OK, I read through your workbook...
This is how I read your problem.
Result will be output at M3
Run the code with your uploaded workbook.

Please Login or Register  to view this content.

[Xiaojiang]

Hi Jidon,

Thank you very much for your help. But when I run code, it show error in this line : Set .Item(a(i, 1))(a(i, 2)) = CreateObject("System.Collections.ArrayList")

I think may be my computer does set up Framework.

So if I do not Sort Array, how to change your code? It mean the result show data as I desire but no need to sort Array, no need to group follow Store column and Code Column.

Thanks

[jindon]

What's your Window version?

Try this then

Please Login or Register  to view this content.

[Xiaojiang]

Hi Jidon,

It works very well. I read a lot of post that you write dictionary code and I like the way that you use dictionary Object.

Thanks a lot.

And thank all member that help me in this topic.

[jindon]

You are welcome and thanks for the rep.