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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
352
Joe4
Joe4
V
  • Solved
Progressive average formula that counts also blank cells
2
Replies
17
Views
956
kvsrinivasamurthy
K
R
  • Question Question
Merge Sheets
Replies
0
Views
186
rtr1811
R
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
115
Rex987456
R
A
  • Solved
Merging duplicates items after words contains colon based on different price for the same ID
2 3 4
Replies
39
Views
627
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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