Remove Duplicates from Array and Retrieve Data from 2nd Array

[tmcmahon74]

I am trying to create a macro that will place 2 sets of data (basically Category and Category Description) into separate arrays, remove duplicates from the first array, and place the appropriate Category Description in the second array.

I have been able to accomplish placing data from the named ranges into arrays and removing the duplicates from the first array using a collection, but I haven't been able to retrieve the corresponding Category Description that goes with the Category.

A vlookup is not the best solution for me due to the fact that new Categories may be added without my knowledge, so I want to be able to handle the new data without maintaining a separate table.

Also, using the same code to remove duplicates for the Category Description may not be viable due to data errors. I have come across a few times that a category erroneously has multiple descriptions. This is a separate issue to be fixed in our data downloads, but I want to be able to take it in to account in case it happens again (the solution, right or wrong, will be to take the first description for a particular category that it comes across).

Thanks in advance for any help.

Tim

 

[mirabeau]

if using excel 2007 or later maybe this would do the job

Sub dupremove()
With Intersect(Range("A1").CurrentRegion, Range("A:F"))
    .RemoveDuplicates Columns:=Array(3, 4, 5, 6), Header:=xlYes
    .Sort Range("B1"), 1, Header:=xlYes
    .Columns(1).Delete
End With
End Sub