Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Populate a column by extracting unique values from another column?
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 |
#2
|
|||
|
|||
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 DataPivot 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" wrote in message ... 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 |
#3
|
|||
|
|||
Thanks for you help - that works brilliantly
"Duke Carey" <duke_No_Junk_carey_at_hotmail.com wrote in message ... 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 DataPivot 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup values in a column and display them in order with no gaps | Excel Worksheet Functions | |||
Column Chart with 2 values in each column | Charts and Charting in Excel | |||
Move column values w/o formula | Excel Worksheet Functions | |||
Count number of Unique values | Excel Worksheet Functions | |||
How do I get unique values from 2 columns? | Excel Discussion (Misc queries) |