• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Generate a unique list from a large set of data

G

Garetjax

New Member
Well, this will be my second question here, I hope I'll get the chance to help people too! I am setting up a sheet that is copy/pasted into and does a whole bunch of calculations based on what is pasted in. The paste is detailed data of the contents of a team's accounts. I have the sheet set up to do calculations in aggregate on all of the team's accounts automagically. However, I was also hoping to be able to set it up to do calculations for an individual account. To do so, I need a formula that will give me a list of all the unique accounts listed in what is pasted in. For example:

---------Irrelevant header information-----

Account|||Type of Spend|||Spend amount

A3342 Travel $200

A4600 Material $234

A6409 Travel 2 $435


This is what would be pasted into the sheet. I need a formula that would look at the account column, find all of the unique accounts, and list them. Then in a summary sheet, I would pull in the unique accounts listed and categorize the spend, i.e. Travel: $635, material $234, etc. for each account.


I know there are some crazy complicated offset formulas that will do this, but I am having a difficult time figuring out how to make them work, even after extensive searching.
 
Seems like you could use a pivot table? Based on your sample you might need to massage the source data a little (e.g., to map "Travel 2" to "Travel") but otherwise a PT seems like the tool made for the job.
 
Why don't you use Advanced Filter?

Choose "Copy to another location"

And choose "Unique records only"
 
I've found myself having to do this a lot recently, so much so that I've mapped the following macro to a custom toolbar button! I use this to get the unique entries, then vlookup and array formulae to pull what I'm after from the source data.


Just select a cell in the column you want the unique list from and then run it...


Sub Copy_Uniques()


Application.ScreenUpdating = False

Selection.EntireColumn.Select

Selection.Copy

Sheets.Add

ActiveSheet.Name = "UNIQUES"

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Application.CutCopyMode = False

Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _

"D1"), Unique:=True

Columns("A:C").Delete Shift:=xlToLeft

Columns("B:B").EntireColumn.AutoFit

Range("A1").Select

Application.ScreenUpdating = True


End Sub
 
While all of these are valid solutions to my problem, part of my desire for the formula is to make the process completely automated. Users that don't know at all how the sheet works will be using it. So I want the list of unique values to generate just as a result of pasting in the list. I appreciate the help, and can't beleive I managed to forget that I started this thread until just now.
 
You must log in or register to reply here.
Back
Top