Populate a column by extracting unique values from another column?

[Mike Palmer]

Hi

I work for a charity. We have a workbook which tracks donations received. It
has 3 worksheets - Daily Amount, Weekly Amount and Monthly Amount. In the
Daily Amount sheet we enter the date, name of the donor and amount. The
Weekly Amount & Monthly Amount worksheets (which only contain dates and
amounts, not names) are populated using SUMIF formulas and this works very
well. We create a new workbook every year.

Our trustees have asked for a list of donors, together with the total amount
donated by each person. As we have never tracked this before we face the
task of going through the workbook by hand and compiling a list of donor's
names which we can then add to a new worksheet and use SUMIF to extract the
total amounts donated by each from the Daily sheet. As we have about 6
months of donations to wade through for this year, making sure that the list
of names is accurate with no one missing is giving us a massive headache.

Is there a function or formula that will extract UNIQUE names from the Daily
Amount sheet Names column and populate the Names column in the Donors
worksheet automatically to avoid missing any names? I am guessing it could
be done with a macro but no one has any idea about using macros so we need
to keep it simple.

I know that Access would probably allow us to do this fairly easily but none
of us know Access and having to retrain a dozen volunteers to use a new
system would be out of our capabilities and, more importantly, out of our
budget.

Can someone please help with a simple solution.

Thanks very much

 

[Duke Carey]

Your need is met VERY well by the Pivot Table feature of Excel.

Note - You need to have column headings for each column - well, Donor and
Amount, anyway.

Select any cell in your Daily Sheet. Use Data>Pivot Table etc...
Make sure Excel's guess about your data range is correct and click on
Finish.
That will create a new sheet and invoke the Pivot Table Wizard.
Drag the Donor heading button to the area for Row Fields and drag the Amount
heading button to the Data area.

That should do it for you

 

[Mike Palmer]

Thanks for you help - that works brilliantly

Your need is met VERY well by the Pivot Table feature of Excel.

Note - You need to have column headings for each column - well, Donor and
Amount, anyway.

Select any cell in your Daily Sheet. Use Data>Pivot Table etc...
Make sure Excel's guess about your data range is correct and click on
Finish.
That will create a new sheet and invoke the Pivot Table Wizard.
Drag the Donor heading button to the area for Row Fields and drag the
Amount heading button to the Data area.

That should do it for you