Remove rows with empty cells depending on multiple columns

Hi there !

Basically I have the following table (see sheet named "BEFORE" on attached file):

I'm trying to merge the rows that have obvious duplicate values into unique ones by adding/replacing empty cells with information from other rows.

For instance, here rows [ID] 1, 2 and 3 hold details about company A.
Rows 4 and 6 hold details about company B.
Rows 5 and 7 hold details about company C.


I have tried the following code to merge duplicate rows into unique ones.

Please Login or Register  to view this content.

The issue here is I don't get the expected result : .Removeduplicates works fine when you specify 1 or 2 columns in the Array() subproperty but does not work properly when you specify too many columns.

Therefore I came up with the following method for which I need help at the coding:

1. Sort rows based on company name (column B)
2. If 2 or more rows have duplicate values based on company name (column B), then add values from other rows to the first row according to the following rule : Replace [EmptyCell] with [CellWithValue]
3. Apply this rule to all columns
4. Delete duplicate rows using .Removeduplicates

The expected result should look like the sheet named "AFTER".

Any ideas?

P.S: I had a table prepared in HTML to make it easier but forum policies forbid me from using it

It works! Thank you WBD

Here is a different option just for grins:

Please Login or Register  to view this content.