Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining/Merging Rows
I have a spreadsheet with 9500 rows of data.
The rows look like this: 305-129, Bill, Smith, $50 305-129, Bill, Smith, $250 305-129, Bill, Smith, $100 005-277, Mary, Maze, $100 005-277, Mary, Maze, $500 I need the rows to look like this: 305-129, Bill, Smith, $50, $250, $100 005-277, Mary, Maze, $100, $500 There are many more columns, but thats the idea. I need to merge all the rows so that each person with their unique ID number has the entire string of information in one row. Right now for every donation, the person has a row, so one person with 10 donations has 10 lines. I need all 10 donations in the same row. Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining/Merging Rows
As-is, you could easily apply autofilter for queries, and more importantly,
very quickly create a pivot table on your source table to drive out a useful summary table to count the no. of donations and total amount donated per unique listing Here's some easy steps to create a pivot table (PT) based on your source table Assume source data as posted in cols A to D, with top row col headers: Ref# Nam1 Nam2 Amt 305-129 Bill Smith $50 305-129 Bill Smith $250 305-129 Bill Smith $100 005-277 Mary Maze $100 005-277 Mary Maze $500 etc Select any cell within the table Click Data Pivot table Click Next Next In step 3 of the wiz. click Layout Drag n drop Ref#, Nam1, Nam2 within the ROW area, each below the other Then double click (in turn) on each of the 3 fields, set subtotals to "None" ok Drag n drop Amt twice within the DATA area Double click on the 1st field, change it to summarize by Count ok. Leave the 2nd as it is, to provide the sum Click OK Finish. Hop over to the PT sheet (just to the left of your source data sheet) Drag n drop "Data" over Total The resulting table will give you a clear summary table with a. a uniques row listing by Ref#, Nam1 and Nam2 b. corresponding counts (ie no. of donations) and sums of the amounts (total donations) next to each unique listing -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Grace" wrote: I have a spreadsheet with 9500 rows of data. The rows look like this: 305-129, Bill, Smith, $50 305-129, Bill, Smith, $250 305-129, Bill, Smith, $100 005-277, Mary, Maze, $100 005-277, Mary, Maze, $500 I need the rows to look like this: 305-129, Bill, Smith, $50, $250, $100 005-277, Mary, Maze, $100, $500 There are many more columns, but thats the idea. I need to merge all the rows so that each person with their unique ID number has the entire string of information in one row. Right now for every donation, the person has a row, so one person with 10 donations has 10 lines. I need all 10 donations in the same row. Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Excel files into 1 (no merging) | New Users to Excel | |||
Combining / Merging several rows - but want to keep formatting | Excel Discussion (Misc queries) | |||
Merging Data in Different Rows | Excel Discussion (Misc queries) | |||
Merging rows | New Users to Excel | |||
Merging Two Rows Into One | Excel Worksheet Functions |