Merge rows if it meets criteria in Excel Solved/Closed

Question

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!

Configuration: Windows XP / Firefox 3.6.14

rizvisa1 · Answer

With macro you can do that

cacaip · Answer

Hi rizvisa1,

Can you provide the macro?

Thank you so much!

rizvisa1 · Answer

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

cacaip89 · Answer

It works perfect. Thank you so much for your help!!

bookworm · Answer

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?

Merge rows if it meets criteria in Excel

5 responses

Similar discussions

Subscribe To Our Newsletter!