Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-15
5
Medium Priority
?
125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

721 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