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
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.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
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
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:
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
P3: FOUND P4: =COUNTIF($C:$C,$M4)>0 copied to P60
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).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
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.
Last edited by DonkeyOte; 01-05-2011 at 12:58 PM. Reason: said M meant P
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:
Then assuming you want the found items in Col RP3: KEY P4: =SUM($P3,IF($M4<>"",COUNTIF($C:$C,$M4)>0)) copied down to P100 etc...
R4: =IF(ROWS(R$4:R4)>MAX($P:$P),"",INDEX($M:$M,MATCH(ROWS(R$4:R4),$P:$P,0))) copied down to R100
Hey that's it! That's the way to do it, I appreciate everyone's time thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks