Clear contents of cell if it is duplicated later in the same row

[MEExcelhelp]

Hi!

I have a sheet with shipping addresses but the data isn't all perfectly lined up. My headers are as follows, starting with Col A:

NAME1

NAME2

NAME3

ADDR1

ADDR2

ADDR3

ADDR4

Some of the addresses are mixed up and come in under NAME2 or NAME3 so I was able to find a couple of formulas that I've placed in ADDR3 and ADDR4 so that all of the addresses appear in those columns (ADDR3 being street address and ADDR4 being city/state/zip).

I need to erase the street address and city/state/zip from the previous columns (B,C,D or E) though so that I only have that information once in ADDR3 and ADDR4. I can't find any VBAs online to help. I was trying to do =IF(OR(F2=B2, F2=C2, F2=D2) then erase cell contents of whichever one it matches and then do the same for G2 but with C2, D2, E2, but it didn't work.

I also don't really know anything about VBA so any help is appreciated.

 

[Kerryx]

Would you have any dummy data we could look at , use the XL2BB addin. But you probbably dont need VBA if doing this exercise once, a helper column with the formula to identify the duplicates you can then filter and delete rows.

 

[MEExcelhelp]

Would you have any dummy data we could look at , use the XL2BB addin. But you probbably dont need VBA if doing this exercise once, a helper column with the formula to identify the duplicates you can then filter and delete rows.

I don't want to delete the row, just clear the cell with the first instance of the duplicate. See below. I would want cells C2/C3 to be blank, D3, E3 to be erased, and B4, C4 to be erased.
The data is normally over 1000 rows so doing it manually can be a pain.

NAME1	NAME2	NAME3	ADDR1	ADDR2	ADDR3	ADDR4
ABC Comp	Mike Smith	123 Main street	New York, NY 12345		123 Main street	New York, NY 12345
BCD Comp	Accounting Department	John Doe	456 Plain Street	Columbus, OH 23456	456 Plain Street	Columbus, OH 23456
CDE Comp	789 Ave	Wilmington, NC 45678			789 Ave	Wilmington, NC 45678

 

[Kerryx]

Ok not the most friendly way but it allows you to delete whatever you want manually
Added helper columns H,I,J,K to check Col B,C,D,E for matches, , if you filter by 1 for example in Col H, it allows you to delete matches in Col B

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	NAME1	NAME2	NAME3	ADDR1	ADDR2	ADDR3	ADDR4	ChkB	ChkC	ChkD	ChkE
2	ABC Comp	Mike Smith	123 Main street	New York, NY 12345		123 Main street	New York, NY 12345	0	1	1	0
3	BCD Comp	Accounting Department	John Doe	456 Plain Street	Columbus, OH 23456	456 Plain Street	Columbus, OH 23456	0	0	1	1
4	CDE Comp	789 Ave	Wilmington, NC 45678			789 Ave	Wilmington, NC 45678	1	1	0	0
Sheet1

Cell Formulas
Range	Formula
H2:K4	H2	=IF(OR($F2=B2,$G2=B2),1,0)