Need a way to automatically sort into unique items in a particular order...

[trickyricky]

I have a list of items in a column that changes week to week and I'm tired of having to copy that column, paste it in another spot, remove the duplicates, vlookup the order they need to be in, and sort it in that order...

Is there a way to do all this automatically.. I don't care if I have to use helper column's with formula's, or vba code. Just whatever works.

Thanks

[MarvinP]

Hi trickyricky,

See the attached. I think you just need an easy to make Pivot Table of Column C which returns Unique cells. That way you don't need to delete dups. Then you drag the data to the sum area and it does a count. Lastly you can sort by count. Is that what you wanted?

See attached: If you have two columns of data it is almost as easy.

[trickyricky]

Hello thanks for your time,

That almost worked out for me but im not well versed with pivot tables. My goal is to have column C automatically put into unique values (which the pivot table does well), but then sorted in the order in column O. I don't need counts of each unique item.

In the pivot table I couldn't figure out how to retrieve column O (the numbered order I want) for each Unique Item of column C. If the pivot table can automatically vlookup or index:match then it might work.

However I'd prefer to stick with VBA Code or excel formula's to achieve my goal.

Thanks

[DonkeyOte]

A pivot would seem a logical and efficient approach here.

If we assume content of Col C changes but M:O does not then I would be inclined to do the following:

Please Login or Register  to view this content.

Now, via your Excel Options -> Advanced -> General Section (towards bottom) -> Edit Custom Lists: create a new Custom List by Importing M4:M60 as the source

Now, create a Pivot table off M3:P60 and set FOUND as Page Field / Report Filter (to be set to TRUE) and ITEM as Row Field / Label.

The Pivot should sort the Found items per M3:M60 order (by virtue of your new Custom List).

[trickyricky]

That's not a bad idea. Kinda complicated but it works. Only setback is occasionally there are new items entered in M3:M60 so I would have to update my custom list each time. Other con is having to refresh pivot table each time the list changes. I can't help but think there has to be a more "hands free" way to do this.

Thanks

[DonkeyOte]

You can use formulae - the PT was based on assumption that M3:M60 did not alter.

I'd still keep the helper column in P (extend for excess capacity) but change the TRUE/FALSE flag to an incrementing number construct and use a basic INDEX/MATCH approach thereafter to retrieve items 1 to n (n being MAX of P)

There's little value in analysing C to generate the unique list as you're just adding unnecessary overhead.

[DonkeyOte]

I realised in the above I referred originally to M whereas I meant P of course.

Given I probably added to confusion, see below for an example of my ramblings:

Please Login or Register  to view this content.

Then assuming you want the found items in Col R

Please Login or Register  to view this content.

[trickyricky]

Hey that's it! That's the way to do it, I appreciate everyone's time thank you!