Previous Topic Next Topic

Help with Formula to Find/Remove if same data in another cell

Closed
Kristen - Apr 11, 2015 at 11:02 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Apr 20, 2015 at 11:40 AM
Hello, I am having some trouble determining a formula that would help me with my problem below.

I have a large Excel sheet and am hoping you can recommend a formula that will extract data from one cell, if that same data is found in another cell right next to it. But, each row has different data, so I can't say 'Find ____ and Replace' because ________ is different each row. Normally, I would use Left, Right, or Mid, but the data is not standard each row.

For example, one row, cell B2 has 'Navy Front to Back Tank' in the cell and right next to it in C2 is 'Navy'. Since 'Navy' is part of B2 and in C2, Navy should be removed from B2.

On the next row, B3 has 'Optic White Birdseye Blouse' and C3 has 'Optic White'. Since Optic White is in B3 and C3, it needs to be found/removed from B3.

Thank you so much in advance for any help you could provide. I have been struggling with this one.

1 reply

Answer 1 / 1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Apr 20, 2015 at 11:40 AM
Hi Kristen,

Try the following macro code to see if it yields the desired result: 
Sub RunMe()
Dim lRow As Integer

lRow = Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Range("B2:B" & lRow)
    cell.Replace What:=cell.Offset(0, 1), Replacement:=vbNullString, LookAt:=xlPart
    cell.Value = Trim(cell)
Next cell

End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.


Best regards,
Trowa
0