Compare values in adjacent columns, replace if identical

Dear forum

I am struggling with a problem in a massive set of data.
It is a table with four columns and different number of rows. Each individual item consists of a varying number of rows.

I need to compare two of the columns, and if they are identical, replace the content of the other column.

So if columns are named A B C D. Column A has only one entry per item, and B C D all has several entries per item.

So the codes has to compare A with B, and if A = B, then replace all the B's with A. But only until there is a change in B. Then it has to compare again.

I hope it makes sense. I have attached a sample workbook illustrating what I am looking for.
SampleWorkbook.xlsm

I am thinkin I need two if statements, where one of them monitors when B changes, and the other doing the comparison?
But I am not sharp enough with coding to do this..

Hope you are able to help.

Hi dannyjoer,
try it

Please Login or Register  to view this content.

Hi Nilem

That works perfectly, I must admit I don't know exactly why.
Let's say their are many tables next to each other in a sheet. All with the same horizontal distance. Can I get the code to repeat it self at many tables in a sheet?

Br/Danny

Hi Danny,
can you show an example of your data with multiple tables

Absolutely. Actually the new sample diverts a little bit from the first, (I discovered an error)

The number in column A that is to be compared to column B, always has a number above.

So this new sample workbook is a perfect example of my situation. I have marked the number in column A with Grey, the one to replace the number in column B, if they differ.

SampleWorkbookRev1.xlsx

Hope you understand my rather cryptic explanation.

try

Please Login or Register  to view this content.

I must say that it is very impressing. It works perfectly.

Could you tell a little bit about the code, and how it can be modified to fit other tables. I mean, what are the critical points?
If I for instance wanted to add a "comment" column between each table. What do I have to change then.

Br

If you add a column "comment" between each table, then modify this line

Please Login or Register  to view this content.

ok, try again

Please Login or Register  to view this content.

Hi again nilem

I have come across a little issue with the otherwise extremely effective code you have provided me with.

The code works smoothly whenever the numbers in the currentregion keep changing.
But if the number in the column where new values are to be inserted, the code overwrites it and put in the last number.

Is there a workaround for that?
I have included a sample workbook SampleWorkbookRev4.xlsm.
It contains the code and an example of where it has gone "wrong"

I would very much appreciate any help on this.

Br/Danny