Menu

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

Report
Posts
5
Registration date
Monday February 28, 2011
Status
Member
Last seen
March 2, 2011
 -
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 -
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

5 replies

Answer 1 / 5
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 756
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5391 users have said thank you to us this month

Answer 2 / 5

Hi rizvisa1,

Can you provide the macro?

Thank you so much!
Answer 3 / 5
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 756
With macro you can do that
Answer 4 / 5

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

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?
Report
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 756
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
Ask a question