this is my first time posting here, but I didn't know where else to go.
I have two lists in excel, both of them with similar data but in different orders, each one has 3 columns (prefix (A, D), suffix (B, E) and data (C, F)).
Let's say list 1 is in columns A, B and C; and list 2 in D, E, F.
Each element in column C has an equal one in column F, but in different order and also, the previous two columns (prefix and suffix) may or may not have different values from their counterpart.
I want to do this (I guess it has to be done with macros but I don't really know, I'm not too much of an expert with excel), I want to take each element from column C (data) and find it's equivalent in column F, after that, I want to compare their prefix and suffix, and maybe paint it red when they're different and green when they're the same.
Is this possible? (and not too much of a hassle?)
If you didn't understand something, please reply and I'll be glad to try and explain better.
I'd do the following:
Move column F before columns D and E (now D is the data, E is the prefix and F is the suffix)
Create three new columns G, H and I with the formulas:
=vlookup(C1, D:F, 2, 0)=vlookup(C1, D:F, 3, 0)=and(G1=A1,H1=B1)Place conditional formatting on column I, with red for FALSE and green for TRUE.
The first step is necessary because VLOOKUP will look for the value in the first column of data.
On the second step, you will match the column C with column F (now moved to D) and obtain the corresponding prefix and suffixes. The next formula compares both and returns TRUE or FALSE if they both match.
You could combine all three formulas in one, if needed.
