Excel Query

  freddy-firecracker 09:12 03 Jun 2005


Does anyone know how to set up a formula which does following??

I have a workbook set up with two sheets. The first has approx 3000 entries, the 2nd has approx 600 entries although some entries appear in both sheets. Is it possible to highlight or pick out these duplicates in sheet 2 so that i can show them as a percentage of sheet 1.

Hope you understand



  pauldonovan 09:46 03 Jun 2005

There are duplicates in sheet 2, or just entries in sheet 2 that are also in sheet 1?

When you say as a percentage of sheet 1 - you can't have a percentage of a sheet!

Can you give an example of what you are trying to do with a bit more detail, if you want to email me a sample spreadsheet (if it isn't confidential) you can click the message link next to my name.

  freddy-firecracker 09:55 03 Jun 2005

Paul what i want to do is show the entries which are the same in sheet 2 as sheet one (not duplicates as such) e.g sheet one has numbers 1 to 3000, sheet 2 has 300 numbers which are between 1 and 3000. how do i show these. I can work the percentage out by dividing the amount of matches in the two sheets by the total entries in sheet one. I cant e-mail a sample as it is a bit confidential. hope you understand!!


  harristweed 10:55 03 Jun 2005

You can use the MATCH function (use 0 as the third variable)to find the cells that have duplicate entries, then use the COUNT function to get the total number of these, from this you can calculate the percentage.

  pauldonovan 11:00 03 Jun 2005

..I wonder if the (same) values from 1 can appear more than once in Sheet 2? If not you can use a vlookup to lookup the values from 1 in 2.

What I did was this example:

Sheet 1 has cells A1 to A10 with the numbers 1 to 10 in. Sheet 2, in column A, has 2,5,3,5 in it.

Alongside my numbers in sheet1, in cell B1, I put the following formula, then dragged it down alongside all the numbers in A:


The $A%1:$A$4 represents the size of the range in sheet 2. You can make it the entire column if you like.

Does that explain?

Then alongisde each number on sheet 1 you will have the number of times it appears on sheet 2...which you could then sum or something to calculate your percentage?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Converse's new logo: the trainer brand looks to its heritage for a fresh identity

Mac power user tips and hidden tricks

Comment lancer Windows 10 en mode sans ├ęchec ?