# Excel Query

freddy-firecracker 09:12 03 Jun 05
Locked

Hi

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

Thanks

FF

pauldonovan 09:46 03 Jun 05

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 05

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!!

FF

harristweed 10:55 03 Jun 05

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 05

..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.

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:

=COUNTIF(Sheet2!\$A\$1:\$A\$4,Sheet1!A1)

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.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

How VR is being used to simulate space

New iPad, iPhone SE & Red iPhone 7 on sale now