Merge duplicates with cell color

[neulite]

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

Original data
Case	Attribute1	Attribute2	Attribute3	Attribute4	Attribute5
1	36	(blank)	3	AO	3
1	(blank)	apple	3	AO	(blank)
2	(blank)	orange	(blank)	(blank)	85
2	61	(blank)	3	AD	(blank)
3	54	orange	(blank)	BL	40
3	(blank)	(blank)	4	(blank)	(blank)

<tbody>
</tbody>

Output I am looking for
Case	Attribute1	Attribute2	Attribute3	Attribute4	Attribute5
1	36	apple	3	AO	3
2	61	orange	3	AD	85
3	54	orange	4	BL	40

<tbody>
</tbody>

 

[wideboydixon]

Take a copy of the sheet and then try running this macro:

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

 

[neulite]

Take a copy of the sheet and then try running this macro:

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

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

Case <tbody> </tbody>	Attribute1 <tbody> </tbody>	Attribute2 <tbody> </tbody>	Attribute3 <tbody> </tbody>	Attribute4 <tbody> </tbody>	Attribute5 <tbody> </tbody>
1 <tbody> </tbody>	36 <tbody> </tbody>	(blank) <tbody> </tbody>	3 <tbody> </tbody>	AO <tbody> </tbody>	3 <tbody> </tbody>
2 <tbody> </tbody>	(blank) <tbody> </tbody>	orange <tbody> </tbody>	(blank) <tbody> </tbody>	(blank) <tbody> </tbody>	85 <tbody> </tbody>
3 <tbody> </tbody>	54 <tbody> </tbody>	orange <tbody> </tbody>	(blank) <tbody> </tbody>	BL <tbody> </tbody>	40 <tbody> </tbody>

<tbody>
</tbody>

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?

 

[wideboydixon]

Does it actually say "(blank)" in those cells or are they just empty? I assumed they were empty.

WBD

 

[neulite]

Does it actually say "(blank)" in those cells or are they just empty? I assumed they were empty.

WBD

I'm an idiot.