Merge rows if it meets criteria in Excel [Solved/Closed]

cacaip89 5 Posts Monday February 28, 2011Registration date March 2, 2011 Last seen - Mar 1, 2011 at 07:07 PM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Jun 21, 2011 at 06:14 PM
Hello,

I would like to know if I can merge rows if it meets certain criteria in Excel. If Project ID on column A is matched, it will merge all the data on launch qty into column U of the other worksheet.

For example:

Worksheet 1:

Column A Column J

P-123 IB100: 367500
P-124 IB300: 1000
P-123 IB123: 10003
P-123 IB145: 1002
P-124 IB121: 3000

WorkSheet 2:

Column Q Column U

P-123 IB100: 367500,IB123: 10003,IB145: 1002
P-124 IB300: 1000,IB121: 3000

Thanks for the help!

See more 

6 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 4, 2011 at 06:05 PM
1
Thank you
here

Sub doCopyData()
   Dim lRow             As Long
   Dim sUnqID           As String
   Dim Cell             As Range
   Dim lTgtRow          As Long
   
   lRow = 1
   Do While (Sheets("Sheet1").Cells(lRow, "A") <> vbNullString)
      sUnqID = Sheets("Sheet1").Cells(lRow, "A")
       Debug.Print sUnqID
      Set Cell = Sheets("Sheet2").Range("Q:Q").Find(sUnqID, Sheets("Sheet2").Cells(Rows.Count, "Q"), , xlWhole, xlByRows, xlNext)
      If (Cell Is Nothing) _
      Then
         Set Cell = Sheets("Sheet2").Cells.Find("*", Sheets("Sheet2").Cells(1, 1), , xlWhole, xlByRows, xlPrevious)
         If Cell Is Nothing _
         Then
            lTgtRow = 1
         Else
            lTgtRow = Cell.Row + 1
         End If
         Sheets("Sheet2").Cells(lTgtRow, "Q") = sUnqID
         Sheets("Sheet2").Cells(lTgtRow, "U") = Sheets("Sheet1").Cells(lRow, "J")
      Else
         lTgtRow = Cell.Row
         If (Sheets("Sheet2").Cells(lTgtRow, "U") = vbNullString) _
         Then
            Sheets("Sheet2").Cells(lTgtRow, "U") = Sheets("Sheet1").Cells(lRow, "J")
         Else
            Sheets("Sheet2").Cells(lTgtRow, "U") = Sheets("Sheet2").Cells(lTgtRow, "U") & ", " & Sheets("Sheet1").Cells(lRow, "J")
         End If
      End If
      lRow = lRow + 1
   Loop
End Sub

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1706 users this month

0
Thank you
Hi rizvisa1,

Can you provide the macro?

Thank you so much!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 4, 2011 at 05:28 PM
-1
Thank you
With macro you can do that
-1
Thank you
It works perfect. Thank you so much for your help!!
-1
Thank you
what if you dont want to switch sheets?
If you just want to merge the data on the current sheet without using a different sheet?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 21, 2011 at 06:14 PM
hard to tell just like that. Most easy approach can be, use a new sheet and once done, copy the content back to original sheet