2010 VBA - Ordered by Collection or Scripting.Dictionary

Hi ,
My problem is not easy to describe but I will try to be as simple as possible.
I import a csv file into Excel but the csv is not filtered.
Depending on a value in Column B, I would like to re-order the column D to AL.
In the attached file, for example, for all value in column B = 113, I would like to order the following groups:
D to H, filter is ‘Tag’
I to M, filter is ‘Type’
N to R filter is ‘Stock Code’
S to W filter ‘STD’

for all value in column B = 228, I would like to order the following groups:
D to H, filter is ‘Tag’
I to M, filter is ‘Type’
N to R filter is ‘Stock Code’
S to W filter is ‘STD’
X to AB filter is ‘Emergency’
AC to AG filter is ‘Panel’
AH to AL filter is ‘CKT’

Instead of making a long and painfull speech, the result I would like is in cells A28 a AL51.

I have been told to use a ‘Collection’ or ‘scripting.dictionary’; the process will be fast to execute the macro but I am not familiar with these.
NB, In the original file, there are much more values in column B to be re-ordered.

Thank you

Herve

Just ignored the value in col.B. No meaning to me....
Only rearrange the data within a row.

See if this is what you wanted.

Hi Jindon,

That is working perfectly however, the reason I mentioned the value in Column B is that I have some more line for which the 'Filters' (Tag, Type, Stock Code...) are different.
Example, a value of 340 would have respectively the fields:
-JB
-STD_1
-STD_2
-Tag
-Price

So I was thinking of creating 1 Dictionary per Value in B. But may be there is a more suitable solution.
Thank you

Hi Jindon,
If you have a couple of minutes to spare, could you explain me the VBA code you made?
If I understand the process, i will try to adapt it to my need.
Thanks you in advance.
Herve

Originally Posted by Herve_Rob

Example, a value of 340 would have respectively the fields:
-JB
-STD_1
-STD_2
-Tag
-Price

So I was thinking of creating 1 Dictionary per Value in B. But may be there is a more suitable solution.
Thank you

Try this one

Please Login or Register  to view this content.

Hi,

Thank you for helping me on this,
With the new code, from line 39 and below, the cell in column A, B and C are blank.

I will add tonight a link to the complete file I have.
Thank you.

Jindon
PS: there is the same issue with the 1st code you posted, after line 39, cells in column A, B and C are empty.

Thanks