hi all

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.

tell me how can i do this.

From
Manu Verma

ScottGem

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

Quixotic2pw

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.

younker1983

Thanx 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

Thanx & Regards
Manu Verma

colbtech

Could you not use the "SUMIF" function?

younker1983

No... i dont know abt this. hos it works? how to apply this on sheet?
tell me bro.

colbtech

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

younker1983

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

colbtech

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

younker1983

on which cell bro?