nickirvine
08-12-2011, 02:36 AM
Hi there,
I’m new to this forum and also a complete novice in Excel so I’m sorry if my macro problem is too simple…
I need a macro to delete duplicate rows on a worksheet but it needs to check between two columns instead of just one.
To try and explain exactly what’s required I’ve come up with this basic example:
Column A will be for ‘Type’ and will only be one of the following values: 1, 2, 3 or 4
Column B will be for ‘Title’ and can be any value
I need the macro to delete duplicate rows based on what’s in Column B but only if Column A is 3 or 4 not 1 or 2 and if there are duplicate entries under 3 as well as 4, I only need the first entry under Type 3 to be kept and all other entries under Type 3 and 4 to be deleted. All entries under Types 1 & 2 need to be shown, including duplicates.
For instance; if a title appears in Column B say 6 times but one entry is under Type 1 and two entries are under Types 2, 3 & 4 each I need all the entries under Types 1 & 2 to be kept and only one entry under Type 3 to be kept.
e.g.
Column A Column B
Type 1 Title X
Type 3 Title X
Type 4 Title X
Type 4 Title X
Type 2 Title X
Type 3 Title X
Type 2 Title X
Should show:
Column A Column B
Type 1 Title X
Type 3 Title X
Type 2 Title X
Type 2 Title X
If there is an entry under Type 4, however, and none under Type 3 then the Type 4 record will need to be shown.
I’ve found a few macro codes online but could only get one to work at all (found here http://www.cpearson.com/excel/deleting.htm (http://www.cpearson.com/excel/deleting.htm)) but this removed all duplicates based on Column B so removed the lines that are still required under a different Type from Column A.
Hopefully this makes sense!
Thanks,
Nick
I’m new to this forum and also a complete novice in Excel so I’m sorry if my macro problem is too simple…
I need a macro to delete duplicate rows on a worksheet but it needs to check between two columns instead of just one.
To try and explain exactly what’s required I’ve come up with this basic example:
Column A will be for ‘Type’ and will only be one of the following values: 1, 2, 3 or 4
Column B will be for ‘Title’ and can be any value
I need the macro to delete duplicate rows based on what’s in Column B but only if Column A is 3 or 4 not 1 or 2 and if there are duplicate entries under 3 as well as 4, I only need the first entry under Type 3 to be kept and all other entries under Type 3 and 4 to be deleted. All entries under Types 1 & 2 need to be shown, including duplicates.
For instance; if a title appears in Column B say 6 times but one entry is under Type 1 and two entries are under Types 2, 3 & 4 each I need all the entries under Types 1 & 2 to be kept and only one entry under Type 3 to be kept.
e.g.
Column A Column B
Type 1 Title X
Type 3 Title X
Type 4 Title X
Type 4 Title X
Type 2 Title X
Type 3 Title X
Type 2 Title X
Should show:
Column A Column B
Type 1 Title X
Type 3 Title X
Type 2 Title X
Type 2 Title X
If there is an entry under Type 4, however, and none under Type 3 then the Type 4 record will need to be shown.
I’ve found a few macro codes online but could only get one to work at all (found here http://www.cpearson.com/excel/deleting.htm (http://www.cpearson.com/excel/deleting.htm)) but this removed all duplicates based on Column B so removed the lines that are still required under a different Type from Column A.
Hopefully this makes sense!
Thanks,
Nick