conditional formatting and transposeClosed

Question

Hi,

Can someone please help me with the following problem...

I need to compare two sets of data and if they do not match, one of the two values should be coloured in red. The problem however is, that one set of figures is contained in a row while the other one is stated in a column - I will try to explain that in detail now.

I have a table with Export Countries - e.g. Germany exports to Belgium 123, Germany to Denmark 345, Germany to Spain 678 and so on (the countries to which the export goes is in the first column; the exporting country is a header of the row) => data is in a column

Exports Germany Belgium Denmark Spain
Germany 0
Belgium 123
Denmark 345
Spain 678

The other table is Importing Countries - e.g. Belgium imports from Germany 123, Denmark from Germany 345, Spain imports from Germany 678 etc. (in this case the first column contains all the countries from where the import is coming and the headers are the importing countries) => data is in a row

Imports Germany Belgium Denmark Spain
Germany 0 123 345 678
Belgium
Denmark
Spain

Thus, the imports of one country have to match with the export from the other country.

Which formular can I apply for getting the desired result, which is, colouring the import values that do not match with the export values from the respective country.

I hope you understand what I mean...

I would be really very grateful if anyone could help me finding the solution. (If possible I would like to avoid using VBA.)

Thank you so much :)

Excelguru · Answer

Hello lizzy

Do a vlookup to get either of the figures to make both of them rowwise / column wise 
then do a comparison in the near by column using if
The do a conditional formatting based on this cells value for the entire row /column

lizzy · Answer

Hi Excelguru,

Thanks for your reply! I'm so glad that there seems to be a solution for this :)

Would you be so kind as to go more into detail with your suggestion? And the thing is, the cells that contain the values are linked to another spreadsheet. So I don't really know how to include the filepath into the formular with the vlookup. (I think I'm doing something wrong here, but Excel Help is not really bringing me any further...)
What do you mean with the near by column? Unfortunately I'm no good with vlookup - that was the one I used to copy in exams... ;)

I would be soo grateful if you could explain it to me in a simple way :)

Have a great day!

Excelguru · Answer

Hello Lizzy

The syntax of vlookup is 
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
to get entries to a row
=vlookup(a1, sheet2!$a$1:$b$5,2,false) to get your data where sheet2!$a$1:$b$5 contains your data in columns

Open both the files then select the range in the formula.

lizzy · Answer

Hey :)

Thanks again!
After now having tried all kinds of vlookup-things and conditional formatting, I still have not been able to achieve the desired result. And I really do not want to do it manually... Please help me :)

I guess, I did not express myself very well enough... The two data tables are both in the same spreadsheet and the data is not in any order at all - so there is no chance of sorting it in an ascending or descending way. I guess this is what makes the vlookup difficult or impossible, as far as I have now figured that out.

And its still the problem of comparing vertical data to horizontal one - is there maybe a possibility to use the transpose function in combination with the conditional formatting?

If it cannot be worked out with Excel functions, maybe someone can help me finding a solution with VBA... I will be grateful for anything...

Thanks a lot!

Excelguru · Answer

Just to inform you that vlookup does not require a sorted list

Conditional formatting and transpose

5 responses

Similar discussions

Subscribe To Our Newsletter!