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?

 

[RichardMGreen]

Can you post some example data?
It doesn't seem to be much of a problem, but can't say for definite without examples.

 

[neilmonk]

37209263	03/04/2011	11:13:00	£10.00	7022	123456	PPM1008832064	123456	£10.00			Woolley	N1	1AA	null@null.cm	123456	£10.00
37209263	03/04/2011	11:13:00	£10.00	7022	123465	PPM1008832064	#N/A	#N/A			Woolley	L1	2BB	null1@null.com	654321	£11.00
37209263	03/04/2011	11:13:00	£11.00	7022	654321	PPM1008832064	#N/A	#N/A			Woolley	S1	3CC	null2@null.com	123465	£10.00
37209263	03/04/2011	11:13:00	£10.00	7022	564321	PPM1008832064	564321	£10.00			Woolley	G1	4DD	null3@null.com	564321	£10.00

 

[RichardMGreen]

Can you post the column headers as well?

 

[neilmonk]

Not really...it doesn't seem to like it.

The column headers are A-I (the £10, #N/A #N/A, £10 being col I) and it then misses J&K , then is L-Q (Wooley being col L).

There or no formulae other than two VLookups in H & I - code below (obviously changes to reflect the row number):

Col H:
=VLOOKUP(F1,P1,1,0)

Col I:
=VLOOKUP(D1,Q1,1,0)

 

[RichardMGreen]

Might be just as easy to sort the columns somehow.
Have you got anything unique you can sort on?

 

[JB_Scotland]

Why not split the data. Sort so that the two sides match. Re-combine the data. Re-sort in the order you want.

 

[neilmonk]

Might be just as easy to sort the columns somehow.
Have you got anything unique you can sort on?

omg why didn't I think of that! That looks like it'll work a dream.

 

[neilmonk]

@JB - The data is already split (kinda...it's seperated by tweo empty columns) so I can simply sort the two sets of data (in place...no need to split, just highlight the range and then sort).

 

[JB_Scotland]

Looks like you have a way to a solution!