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

MEExcelhelp

New Member
Joined
Jul 14, 2022
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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:

NAME1NAME2NAME3ADDR1ADDR2ADDR3ADDR4

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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.

NAME1NAME2NAME3ADDR1ADDR2ADDR3ADDR4
ABC CompMike Smith123 Main streetNew York, NY 12345123 Main streetNew York, NY 12345
BCD CompAccounting DepartmentJohn Doe456 Plain StreetColumbus, OH 23456456 Plain StreetColumbus, OH 23456
CDE Comp789 AveWilmington, NC 45678789 AveWilmington, NC 45678
 
Upvote 0
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
ABCDEFGHIJK
1NAME1NAME2NAME3ADDR1ADDR2ADDR3ADDR4ChkBChkCChkDChkE
2ABC CompMike Smith123 Main streetNew York, NY 12345123 Main streetNew York, NY 123450110
3BCD CompAccounting DepartmentJohn Doe456 Plain StreetColumbus, OH 23456456 Plain StreetColumbus, OH 234560011
4CDE Comp789 AveWilmington, NC 45678789 AveWilmington, NC 456781100
Sheet1
Cell Formulas
RangeFormula
H2:K4H2=IF(OR($F2=B2,$G2=B2),1,0)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top