Removing duplicate rows depending on range of columns

[simontlc]

Hi,

I have an Excel problem that I need help with! I have searched and tried different things but to no avail.

I have a spreadsheet that contains a number of duplicate rows, that have slight differences in a range of column values. I would like to remove the duplicates, which will be the rows where this column range is blank, keeping the row where data exists in the column ranges.

Best explained with an example:


A B C D E F G
1
1 x y z a b c
2
2 d e f g h i
3
3 j k l m n o


Columsn A-G are the table, with column A containing a unique code. There are duplicates, that I wish to remove. Where data exists in columns B-G I want to keep this row, but remove the duplicate row that has nothing in these columns. So in the example above the first and second rows (both '1') are duplicates and I want to remove the first one.

Does anyone know if this is possible, and if so how it is done? Ideally I would like to achieve this through a function rather than macros, as I don't know VBA and want to understand how the formula may work. I have been looking at nested IFs, ANDs and COUNTIFs but so far no joy. Any help is much appreciated!

[José Augusto]

Hi
Formulas don't remove rows. We can mark them as duplicate
So if data in A1:G6, use in H1 and copy down the following array formula (array formula must be enter with CTRL+SHIFT+ENTER instead only ENTER)

Please Login or Register  to view this content.

[simontlc]

Hi Jose,

That's perfect - thanks for your (very quick) help!

[José Augusto]

You are welcome and thanks for the feedback.

Do not forget to mark this thread as SOLVED.