merging duplicate donors and donation amounts from different years

[eli_]

hi i see lots of posts about merging but could find none that reall
address my problem.
im trying to organize a 10k+ name donor data base list that has names o
donors, with how much they have donated, every year in yearly increment
starting from 2006..... the problem is that excel displays the list a
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!

+-------------------------------------------------------------------
|Filename: donor list sample.pdf
|Download: http://www.excelbanter.com/attachment.php?attachmentid=532
+-------------------------------------------------------------------

 

[Spencer101]

hi i see lots of posts about merging but could find none that reall
address my problem.
im trying to organize a 10k+ name donor data base list that has names o
donors, with how much they have donated, every year in yearly increment
starting from 2006..... the problem is that excel displays the list a
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!!

Hi, perhaps with some anonymised sample data in an Excel file it woul
be easier to help you out with this...


S

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[eli_]

sure i attached a pdf ..thnx so muc

+-------------------------------------------------------------------
|Filename: donor list sample.pdf
|Download: http://www.excelbanter.com/attachment.php?attachmentid=533
+-------------------------------------------------------------------

 

[Bruno Campanini]

eli_ explained :

sure i attached a pdf ..thnx so much

You did really!

What solution are looking for: Functions or VBA?

Bruno

 

[Bruno Campanini]

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

 

[Bruno Campanini]

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

 

[Bruno Campanini]

Bruno Campanini laid this down on his screen :

Bruno Campanini formulated the question :

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

 

[eli_]

If this is what you mean, then this is one way of doing it.

But I'd have thought a simple pivot table would have been far easier!

like that but the donations per year is important ...i already have
total list..is there a way to keep the single row per name with th
different amounts donated over the different years

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

like that but the donations per year is important ...i already have
total list..is there a way to keep the single row per name with th
different amounts donated over the different years

Like this

But again, a pivot table would probably be even easier

+-------------------------------------------------------------------
|Filename: sample donor list2.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=540
+-------------------------------------------------------------------