Extracting a list of unique values

S

SillyJokes

I have a column of dates in a random order, some dates have multipl
entries, some dates are missing altogether.

I would like to produce a new column (on another worksheet) of sorte
dates that appear (each date only appearing once).

I will then be able to create the totals for each date (I can do thi
bit)

Finally, I would like to add a column of listing all the amounts (as
string) for the date in question, so if I have 5 entries for
particular date, I get a total of the 5 entries and also a list of th
5 entries
 
P

Peo Sjoblom

Create a new column, first select only the date range (plus header) and do
data>filter>advanced filter,
copy to another location, unique records only, that will give you a unique
list with the dates, then use a sumif formula to return the amounts for each
date

=SUMIF($A$2:$A$200,E2,$B$2:$B$200)

copy down (where E2 is the first unique date, A2:A200 are the original date
and B2:B200 are the amounts)
then select then new table, copy and edit>paste special as values in place,
finally sort the new table by the dates
in ascending order



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Top