Go Premium for a chance to win a PS4. Enter to Win

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
?
128 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 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn various types of data validation for different data types in Excel 2013.

927 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