Merge duplicates with cell color

N

neulite

New Member
Joined
Feb 20, 2018
Messages
3
I have a list of duplicate cases (column A). Both duplicates have information that the other may or may not have. I would like to somehow merge the duplicate cases and return the non empty value corresponding to the attribute. Additionally, I would like to carry over the cell color (pretend the number colors below are the cell fill colors). Is this possible? See below


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Original data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case[/TD]
[TD]Attribute1[/TD]
[TD]Attribute2[/TD]
[TD]Attribute3[/TD]
[TD]Attribute4[/TD]
[TD]Attribute5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]36[/TD]
[TD](blank)[/TD]
[TD]3[/TD]
[TD]AO[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD](blank)[/TD]
[TD]apple[/TD]
[TD]3[/TD]
[TD]AO[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD](blank)[/TD]
[TD]orange[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]61[/TD]
[TD](blank)[/TD]
[TD]3[/TD]
[TD]AD[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]54[/TD]
[TD]orange[/TD]
[TD](blank)[/TD]
[TD]BL[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD]4[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Output I am looking for[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case[/TD]
[TD]Attribute1[/TD]
[TD]Attribute2[/TD]
[TD]Attribute3[/TD]
[TD]Attribute4[/TD]
[TD]Attribute5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]36[/TD]
[TD]apple[/TD]
[TD]3[/TD]
[TD]AO[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]61[/TD]
[TD]orange[/TD]
[TD]3[/TD]
[TD]AD[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]54[/TD]
[TD]orange[/TD]
[TD]4[/TD]
[TD]BL[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Take a copy of the sheet and then try running this macro:

Code: 
Public Sub MergeCaseRows()

Dim lastRow As Long
Dim thisRow As Long
Dim lastCol As Long
Dim thisCol As Long

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Start on row 3
thisRow = 3

' Keep going until the end
Do While thisRow <= lastRow
    ' Is this case the same as the previous row?
    If Cells(thisRow, "A").Value = Cells(thisRow - 1, "A").Value Then
        ' Find the last column
        lastCol = Cells(thisRow, Columns.Count).End(xlToLeft).Column
        
        ' Process all columns
        For thisCol = 2 To lastCol
            ' Check if the previous row is empty
            If IsEmpty(Cells(thisRow - 1, thisCol).Value) Then
                ' Copy this row
                Cells(thisRow, thisCol).Copy Destination:=Cells(thisRow - 1, thisCol)
            End If
        Next thisCol
        
        ' Delete this duplicate row
        Cells(thisRow, "A").EntireRow.Delete
        
        ' We've lost a row from the sheet
        lastRow = lastRow - 1
    Else
        ' Move to the next row
        thisRow = thisRow + 1
    End If
Loop

End Sub

WBD
 
Upvote 0
wideboydixon said:
Take a copy of the sheet and then try running this macro:

Code: 
Public Sub MergeCaseRows()

Dim lastRow As Long
Dim thisRow As Long
Dim lastCol As Long
Dim thisCol As Long

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Start on row 3
thisRow = 3

' Keep going until the end
Do While thisRow <= lastRow
    ' Is this case the same as the previous row?
    If Cells(thisRow, "A").Value = Cells(thisRow - 1, "A").Value Then
        ' Find the last column
        lastCol = Cells(thisRow, Columns.Count).End(xlToLeft).Column
        
        ' Process all columns
        For thisCol = 2 To lastCol
            ' Check if the previous row is empty
            If IsEmpty(Cells(thisRow - 1, thisCol).Value) Then
                ' Copy this row
                Cells(thisRow, thisCol).Copy Destination:=Cells(thisRow - 1, thisCol)
            End If
        Next thisCol
        
        ' Delete this duplicate row
        Cells(thisRow, "A").EntireRow.Delete
        
        ' We've lost a row from the sheet
        lastRow = lastRow - 1
    Else
        ' Move to the next row
        thisRow = thisRow + 1
    End If
Loop

End Sub

WBD
Click to expand...

Thank you. Unfortunately, this is the output I get with that code:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Case[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Attribute1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Attribute2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Attribute3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Attribute4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Attribute5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]36[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"](blank)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]AO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"](blank)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]orange[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"](blank)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"](blank)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]85[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]orange[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"](blank)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]BL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

It seems to have retained the blank rows instead of going to the first non-blank cell.

I opened up the editor, pasted the code and saved. Then alt-F8 and run on the tab with the data. Am I doing it wrong?
 
Upvote 0
Does it actually say "(blank)" in those cells or are they just empty? I assumed they were empty.

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
hide cell value if another cell is blank
Replies
9
Views
421
Joe4
Joe4
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
326
shg
shg
Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
393
mumps
mumps
V
  • Solved
Progressive average formula that counts also blank cells
2
Replies
17
Views
969
kvsrinivasamurthy
K
D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top