Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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

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
Dan Purcell
Asked:
Dan Purcell
  • 2
  • 2
1 Solution
 
SimonCommented:
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
 
Dan PurcellManagerAuthor Commented:
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
 
Dan PurcellManagerAuthor Commented:
I've requested that this question be deleted for the following reason:

Not sure how to handle live data
0
 
SimonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now