200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
excel help. if last 7 numbers in any cell of column b match last 7 numbers in any cell of column a replace matching cell from column b with its matched cell in column a
example:
111 thanks 5252527 111 you 5214785
111 hello 9874520 855 help 5252527
The 5252527 match. So the whole cell 855 would replace 111 thanks.
How do I translate this into a formula?
I somewhat struggled with how the question reads, but here's my attempt... I would create some 'helper' columns (makes life a little easier, and I like to 'show my work' so it can be verified). I would 1) create Helper column (Column E in my file) of the last 7 digits in Column A and then next to it have a formula for the Original Column A (my Column F). Once this is set up, you could perform a lookup on Column B last 7 characters. if it doesn't find a match, the results are N/A. If it Does find a match, the results will be the 'Matched' cell that you want to replace it with.
Next I would mave the list filtered. The values in Column G are what need to replace Column B. You can do this in the filtered list, by making a formula in Column B to pull the results from the same row in Column G (Example in Col B3 the formula would be "=+G3"). You would then unfilter the list, Copy and replace Column B with it's own values (Paste Special as Values) and you would have updated cells. See example
It may be easier to break down into smaller more manageable steps. Give it a try
