Solved

Merge multiple rows into a single row based on a common id number in column 1

Posted on 2015-01-15
5
110 Views
Last Modified: 2015-01-19
I have two excel files. They both share a common id number in column 1. The file titled Letters-CC has the id number in column 1 and name and address information along with other information through column K.
The second file, Gifts-CC has 4 columns, including the id in column 1.  The first spreadsheet has one record per id number. The second spreadsheet has a variable amount of records per id. What I need is to merge these  two spreadsheet’s into one with only 1 id per row. I would like to have the information from Gifts-CC start in column L and continue across the row. I know this is vague, please ask questions and I will try to clarify
Gifts-CC.csv
Letters-CC.csv
0
Comment
Question by:Dan Purcell
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40551655
So, for example, there are three rows in Gifts-CC, with the ID of 188. Each record in Gifts-CC has the ID + 3 other columns.

Should we assume that you want to write:
 the first of these rows to columns L,M,N
the second of these rows to columns O,P,Q
the third of these rows to columns R,S,T

of the row with that ID on the Letters-CC worksheet.

I am assuming you don't want to write the repeat the ID value with each set of data - correct?
0
 

Author Comment

by:Dan Purcell
ID: 40551768
Yes, that is correct. We don't need to repeat the id and what you described in the the rest is right on . That's what I'm looking for. Thanks
0
 

Author Comment

by:Dan Purcell
ID: 40554788
I've requested that this question be deleted for the following reason:

Not sure how to handle live data
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
ID: 40554624
Hi, please try this code. It requires that you put both your worksheets into a single workbook first, so that you have two worksheets named:
"Gifts-CC"
"Letters-CC"

If you use other worksheet names, change the code in lines 8-9 to reflect this.

Option Explicit
Sub AddToMatchingRow()
Dim shtSource As Worksheet
Dim shtDest As Worksheet
Dim intColsCount As Integer
Dim intNoMatchCount As Integer

Set shtSource = ActiveWorkbook.Sheets("Gifts-CC")
Set shtDest = ActiveWorkbook.Sheets("Letters-CC")
intColsCount = shtDest.Columns.Count

Dim rngSource As Range 'store the source range to iterate through
Dim rngMatch As Range 'store the range that the match is found in.
Dim rngCopy As Range 'store the range to be copied if a match is found
Dim rngPaste As Range 'store the range to paste rngCopy into if a match is found

Set rngSource = shtSource.UsedRange.Offset(1, 0) 'offset by one row to skip the headers
Debug.Print rngSource.Address

Dim c As Range
For Each c In rngSource.Rows
    Debug.Print c.Cells(1).Row, c.Cells(1).Value;
    With shtDest
        Set rngMatch = .Columns(1).Find(c.Cells(1).Value, LookIn:=xlValues)
        If Not rngMatch Is Nothing Then
            Debug.Print " found at " & rngMatch.Address;
           Set rngCopy = c.Offset(0, 1).Resize(1, c.Columns.Count - 1)
           Set rngPaste = .Cells(rngMatch.Row, intColsCount).End(xlToLeft).Offset(0, 1)
           Set rngPaste = rngPaste.Resize(1, rngSource.Columns.Count - 1)
           Debug.Print " copy to " & rngPaste.Address
           rngPaste.Value = rngCopy.Value
        Else
            Debug.Print " not found"
            intNoMatchCount = intNoMatchCount + 1
        End If
    End With
    Set rngMatch = Nothing
    'DoEvents
    Next
    MsgBox "Done" & vbCrLf & IIf(intNoMatchCount > 0, intNoMatchCount & " rows from " & shtSource.Name & " not matched in " & shtDest.Name, "All rows from " & shtSource.Name & " matched in " & shtDest.Name)
End Sub

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now