Solved

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

Posted on 2015-01-15
5
114 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:Simon
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:
Simon 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

821 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