Home |
Search |
Today's Posts |
#1
|
|||
|
|||
merging duplicate donors and donation amounts from different years
hi i see lots of posts about merging but could find none that really address my problem.
im trying to organize a 10k+ name donor data base list that has names of donors, with how much they have donated, every year in yearly increments starting from 2006..... the problem is that excel displays the list as follows (see attachment below) what i need it to display is bob $500 600 800 jack 100 400 300 none of the merging commands that i have tried helped if anybody can help that would be much appreciated thanx so much!! |
#2
|
|||
|
|||
Quote:
S. |
#3
|
|||
|
|||
sure i attached a pdf ..thnx so much
|
#4
|
|||
|
|||
Could you post an actual .xls or .xlxs file? You'll have to add it to a zip file before posting as this forum won't allow Excel files unless added to a .zip file.
Include a bit of and explanation of how you need it to get from one format (i.e. a list of data) to the new format (i.e. the table in your .pdf) and you'll get a much quicker answer. S. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging duplicate donors and donation amounts from different years
eli_ explained :
sure i attached a pdf ..thnx so much You did really! What solution are looking for: Functions or VBA? Bruno |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging duplicate donors and donation amounts from different years
Do you want something like this:
2006-2007 2007-2008 2008-2009 2009-2010 2010-2011 2011-2012 bob 1000 1000 jack 500 1000 500 500 600 ??? Again my first question: are you looking for a solution with formulas or with code? Bruno |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging duplicate donors and donation amounts from different years
Bruno Campanini formulated the question :
Do you want something like this: 2006-2007 2007-2008 2008-2009 2009-2010 2010-2011 2011-2012 bob 1000 1000 jack 500 1000 500 500 600 ??? Again my first question: are you looking for a solution with formulas or with code? Bruno Sorry, contents unreadable! bob 1000 1000 jack 500 1000 500 500 600 Bruno |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging duplicate donors and donation amounts from different years
Bruno Campanini laid this down on his screen :
Bruno Campanini formulated the question : Again my first question: are you looking for a solution with formulas or with code? Bruno In the mean time: =================================== Public Sub NewLayout() Dim SourceRange As Range, TargetRange As Range Dim NameColl As New Collection, TargetArray() Dim NumberOfYears As Integer, NumberOfRecords As Long Dim S As Currency, SS As Currency, i, j, k ' Definition ------------------ Set SourceRange = [Sheet1!K16] Set TargetRange = [Sheet1!K27] NumberOfYears = 6 ' ----------------------------- Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set SourceRange = SourceRange.Resize(SourceRange.End(xlDown) _ .Row - SourceRange.Row + 1) NumberOfRecords = SourceRange.Rows.Count For Each i In SourceRange On Error Resume Next NameColl.Add i, CStr(i) On Error GoTo 0 Next ReDim TargetArray(1 To NameColl.Count, 1 To NumberOfYears) For i = 1 To NumberOfRecords For k = 1 To NumberOfYears For j = 1 To NameColl.Count If SourceRange(i, 1) = NameColl(j) Then TargetArray(j, k) = TargetArray(j, k) _ + SourceRange(i, k + 1) End If Next: Next: Next For j = 1 To NameColl.Count TargetRange(j, 1) = NameColl(j) For k = 1 To NumberOfYears If j = 1 Then TargetRange(0, k + 1) = SourceRange(0, k + 1) TargetRange(0, 1) = SourceRange(0, 1) TargetRange(0, NumberOfYears + 3) = "Totals" End If TargetRange(j, k + 1) = TargetArray(j, k) S = S + TargetArray(j, k) Next TargetRange(j, NumberOfYears + 3) = S SS = SS + S: S = 0 Next TargetRange(NameColl.Count + 1, NumberOfYears + 3) = SS Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub ========================================= Bruno |
#9
|
|||
|
|||
Quote:
|
#10
|
|||
|
|||
Quote:
But I'd have thought a simple pivot table would have been far easier! Last edited by Spencer101 : August 14th 12 at 08:11 PM |
#11
|
|||
|
|||
like that but the donations per year is important ...i already have a total list..is there a way to keep the single row per name with the different amounts donated over the different years?
|
#12
|
|||
|
|||
Quote:
But again, a pivot table would probably be even easier. Last edited by Spencer101 : August 14th 12 at 09:41 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dollar amounts aren't merging from Excel into a Word document... | Excel Discussion (Misc queries) | |||
Merging duplicate lines in a Database | Excel Discussion (Misc queries) | |||
Combining and merging duplicate data. | Excel Discussion (Misc queries) | |||
Look up duplicate amounts with unique batch numbers | Excel Worksheet Functions | |||
Merging duplicate rows | Excel Programming |