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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RartemassLife CoachCommented:
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.