Compare cell results and adjust data

[neilmonk]

I would like some help with creating a Macro that will save me several hours each day.

I need to compare the contents of two cells, and if they don't match, I need to find the corresponding data and move it into the correct row to ensure the data matches.

The file is, essentially, split into two sides, left and right. Some data is pasted into the left, and some data into the right. The data is from two different sources, and so there are always some data that isn't lined up to the corresponding record on the right.

Two VLookups compare the contents of columns F&P and columns D&Q respectively. Where there are "#N/A" results, I have to manually move the cells on the right to the correct rows, so that they correspond to the data on the left. In the attached example, I'd need to move the contents of cells L2:Q2 to calls L3:Q3, and vice-versa.

Can anyone write me some code that would automate this, please?

 

[neilmonk]

Indeed, thanks to my old buddy Richard pointing out the bleeding obvious, once again!

 

[RichardMGreen]

Always happy to help!!!

Might not work perfectly, though.
I can see you've got the same code several times (12345, etc). Vlookup will pick up the first one every time.
Might need a bit of a rethink on that one to create a distinct in each data set so you pick up the right one.

 

[neilmonk]

That was just example data, there is a unique value in each of the two sets of data, so the sorting solution has been working brilliantly. Thanks again chief.

 

[RichardMGreen]

If each value only appears once in each data set, there's no need to sort it.
You can use a vlookup or index/match to find the necessary data you need and check the entire column at once rather than looking for specific cells.

 

[neilmonk]

Yes, but that was the initial problem. The unique value always appears (hence a VLookup looking in the whole data would return ok), but it needs to be in the same row in both sets of data (which it isn't, all of the time, but it needs to be re-ordered so that it lines up).

I'll explain more next time I see you in person, but sorting it, as you suggested, works a treat.

 

[RichardMGreen]

No worries.
Glad it worked.