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

Question

Previous Topic Next Topic

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

Report

cacaip89

Posts: 5
Registration date: Monday February 28, 2011
Status: Member
Last seen: March 2, 2011

- Mar 1, 2011 at 07:07 PM
rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

- 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!

Excel macro to merge cells based on condition
Merge rows in excel based on criteria - Best answers
Excel merge cells based on condition - Best answers
Short cut to merge cells ✓ - Forum - Excel
Excel Macro copy to cell based on criteria ✓ - Forum - Excel
Excel - A macro copy to cell based on a specific criteria - How-To - Excel
Excel macro to insert rows based on cell value - Forum - Excel
Excel - macro to lookup cell and add new row - How-To - Excel

5 replies

Answer 2 / 5

cacaip
Mar 4, 2011 at 05:39 PM

Hi rizvisa1,

Can you provide the macro?

Thank you so much!

Answer 3 / 5

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
Mar 4, 2011 at 05:28 PM

With macro you can do that

Answer 4 / 5

cacaip89
Mar 6, 2011 at 12:28 AM

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

Answer 5 / 5

bookworm
Jun 21, 2011 at 02:38 PM

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: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
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

Ask a question

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

rizvisa1 · Accepted Answer · 2011-03-04T18:05:45-0500

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

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

5 replies

Your reply

Subscribe To Our Newsletter!