hi all

please tell me how can I findout the unique values in an entire coloumn.
for example I have 3 coloumn in an sheet.

Name Prod. Value
a 24 100
b 26 240
a 33 120
a 21 200
c 26 240
b 33 120
d 24 100



from this one I want to knpow that How many persons we r having. (there are only 4 persons in this example i.E. a,b,c and d.) but total rows are 7.

please tell me how can I do this.

From
Manu Verma

The best choice would be to use Microsoft Query to build a query of unique values.

You do not say which application you are using. If it is Excel there is an easy way to extract the unique records.

Highlight the column by clicking on the letter at the top of the column. Choose Data > Filter > Advanced Filter > Filter the list in place/check the "Unique Records Only" box. Be mindfull that if there are imbedded spaces before or after seemingly identical entries, they are treated as unique. This also holds true for numeric values vs. numeric values defined as text.

You can then copy and paste this filtered list to another sheet, remove the filter and you will have the original list.

I typically chose to filter the list to a new location. There are a few more steps involved for that. There are many great websites to use for learning Excel functions. I use the search engine with key words such as Excel, Filtering, Example to find what I wish to learn about.

If you need to know how many duplicates in your list... Sort the list and use Data, Subtotals, Count at each change in the column.

:) Thanks Dear...

its really very simple way... thank you very much again...

bro.. I have one more problem.. same in excel

I have 2 different spreadsheets
sheet 1 and sheet 2
in sheet 1 I have suppouse following data
Name Prod. Value
a 24 100
b 26 240
a 33 120
a 21 200
c 26 240
b 33 120
d 24 100

in sheet 2

Name Prod. Value
a 24 100
d 26 240
a 33 120
e 26 240
a 21 200
c 26 240
d 24 100

in this situation I want to find out the dropouts of sheet 1. (that persons which are not present the sheet 2)
how can I compare these sheets?
in this case I want the result as
Name Prod. Value
b 26 244
b 33 120

Thanks & Regards
Manu Verma

Could you not use the "SUMIF" function?

Could you not use the "SUMIF" function?

No... I don't know about this. Hos it works? How to apply this on sheet?
Please tell me bro.

a 24 100
b 26 240
a 33 120
a 21 200
c 26 240
b 33 120
d 24 100


78

in a cell type the formula =SUMIF(H10:J16,H10,I10:I16)

where H10:J16 defines all the cells to be evaluated
and H10 is the criteria
and I10:I16 are the cells to be added

a 24 100
b 26 240
a 33 120
a 21 200
c 26 240
b 33 120
d 24 100


78

in a cell type the formula =SUMIF(H10:J16,H10,I10:I16)

where H10:J16 defines all the cells to be evaluated
and H10 is the criteria
and I10:I16 are the cells to be added


:confused: on which sheet I have to apply this one? Or I have to create a combine file which will contain all the data (sheet 1 & sheet 2).? :confused:

Enter on both(either) sheets, and if you need to: enter a sum that subtracts/adds both values.

Enter on both(either) sheets, and if you need to: enter a sum that subtracts/adds both values.

On which cell bro?

Any cell you care to have the totals in.

Taking the example above:-
H10:J16 are the cells that will be evaluated
H10 contains the value that we are going to check
I10:I16 are the values to add if the corresponding column (H) contains the value we are checking for.

I've attached a zipped file that might help