How can I compare lists in Excel


I want to be able to paste 2 separate lists into excel and then automatically sort and compare them.

For example

1st list
Apples 25%
Pears 60%
Banana 30%
Figs 20%

2nd list
Pears 90%
Apples 10%
Plums 80%
Dates 5%

I need to be able to easily paste the lists into excel and then excel should sort and compare them in separate columns on matching lines with a balance in last column. Preferably the higher percentage would highlight in green i.e apples 25% would be green.

Can it be done?

Apples 25%      Apples 10%        +15%
Pears 60%        Pears 90%          -30%
Banana 30%                                   +30%
Figs 20%                                          +20%
                                Plums 80%      -80%
                                Dates 5%         -5%
Who is Participating?
luconstaConnect With a Mentor Commented:
Considering each list is composed on data using 2 columns - one for fruit names the other with the percentage, I propose you the following (as an example):

List 1 in range A2:B5
List 2 in range E2:F5 (you could use another sheet if this will suit you)

And a "balance" list in range I2:L7 where you could have on the first column (I) the complete list of fruits (unique names  may e easely obtain by combining on a single column the fruit names from both lists and then remove duplicates).
On this balance list you could VLOOKUP for the name of the fruit on each list and return the percentage, and on the balance colum just substract J-K columns.

Finally using conditional formatting you could mark the MAX value from the each line of fruits.

For more details see the attached sample file.
RartemassConnect With a Mentor Author, martial arts coach, IT ConsultantCommented:
Is each list a single cell, ie is "Pears 90%" the content of a single cell or two cells side by side?

To do the calculations it is easier if they are side by side.
Otherwise you would need to extract the percentages from the text before calculations can be made. This will make for a very long and complicated formula.

The highlighting can be done with conditional formatting and not part of the formulas for sorting and percentage calculation.

I'd say a macro is in order but it should be possible with formulas depending on your answers.
GRiTechAuthor Commented:
Apologies for the delay in replying

I need to copy the data from a website so data wont be in two columns like in excel.

Rartemass ~ yes I figured that would make it awkward.

luconsta ~ that is the kind of thing im looking for although would need the list to fill in the items as well from what is pasted in.

many thanks for your replys guys
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
GRiTechAuthor Commented:
Many thanks for your time and input sorry I left it so long to reply

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.