Help Merging Multiple Row Entries into 1 row with more columns

[jpayne]

I have a data file of donors. The file has a list of each donor and their giving history listed individually. I need it all on the same row. For example, if John Smith gave State University 6 donations over 10 years. John Smith ID# 12345 is listed 6 times, taking up 6 rows. I need it so that there is only one row per donor, while listing all their gifts in one row.

 

[hiker95]

jpayne,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook (sensitive data scrubbed/removed) that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste


Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[jpayne]

Thanks for the tip. I've uploaded a sample workbook to box.net

http://www.box.net/shared/eihgs4xcup

The first tab is how we see the data (before) the second tab (after) is how we need it displayed. The person originally on this project has been cutting and pasting but there HAS to be a simpler way to do this. Any help would be much appreciated.

-JP

 

[hiker95]

jpayne,

Thanks for the workbook.

I am not going to display screenshots because of the width of your data set.

The macro sorts your raw data on Sheet1, column A ascending, column B ascending, and then moves the data to your formatted Sheet2.




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MergeData()
' hiker95, 01/27/2011
' http://www.mrexcel.com/forum/showthread.php?t=524184
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, LC As Long, a As Long, b As Long, NR As Long, SR As Long, ER As Long, NC As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
LC = w1.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
w1.Range(w1.Cells(2, 1), w1.Cells(LR, LC)).Sort Key1:=w1.Range("A2"), Order1:=xlAscending, Key2:=w1.Range("B2") _
  , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
w1.Range("A1").EntireColumn.Insert
w1.Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=w1.Columns("A:A"), Unique:=True
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
NR = w2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
w1.Range("A2:A" & LR).Copy w2.Range("A" & NR)
w1.Range("A1").EntireColumn.Delete
LR = w2.Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To LR Step 1
  SR = Application.Match(w2.Range("A" & a).Value, w1.Columns(1), 0)
  ER = Application.Match(w2.Range("A" & a).Value, w1.Columns(1), 1)
  w1.Range("B" & SR & ":AC" & SR).Copy w2.Range("B" & NR)
  NC = 30
  For b = SR + 1 To ER Step 1
    w2.Cells(a, NC).Value = w1.Cells(b, 2).Value
    w2.Cells(a, NC + 1).Resize(, 16).Value = w1.Range("J" & b & ":Y" & b).Value
    NC = NC + 17
  Next b
Next a
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the MergeData macro.

 

[hiker95]

jpayne,

With the above macro, I assume that Sheet2 only contains the titles in row 1, beginning in cell A1, without any other data.

 

[jpayne]

I can't seem to enter visual basic mode. Are the keys different for 2008 Excel or Mac?

 

[hiker95]

jpayne,

I can't seem to enter visual basic mode. Are the keys different for 2008 Excel or Mac?

I have no experience with Excel 2008 on a Mac.

You may want to click on the Post Reply button, and just enter the word BUMP, and then click on the Submit Reply button, and someone else may be able to assist you.

 

[jpayne]

Oh okay. Thanks for your help!