|
|
|
|
New Member
|
|
Aug 10, 2006, 05:43 AM
|
|
how to find unique values in an entire column
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
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 10, 2006, 06:05 AM
|
|
The best choice would be to use Microsoft Query to build a query of unique values.
|
|
|
New Member
|
|
Aug 10, 2006, 06:11 AM
|
|
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.
|
|
|
New Member
|
|
Aug 10, 2006, 10:43 PM
|
|
:) 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
|
|
|
Senior Member
|
|
Aug 11, 2006, 01:18 AM
|
|
Could you not use the "SUMIF" function?
|
|
|
New Member
|
|
Aug 11, 2006, 02:16 AM
|
|
Originally Posted by colbtech
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.
|
|
|
Senior Member
|
|
Aug 11, 2006, 02:27 AM
|
|
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
|
|
|
New Member
|
|
Aug 11, 2006, 03:55 AM
|
|
Originally Posted by 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
: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:
|
|
|
Senior Member
|
|
Aug 11, 2006, 04:29 AM
|
|
Enter on both(either) sheets, and if you need to: enter a sum that subtracts/adds both values.
|
|
|
New Member
|
|
Aug 13, 2006, 10:48 PM
|
|
Originally Posted by colbtech
Enter on both(either) sheets, and if you need to: enter a sum that subtracts/adds both values.
On which cell bro?
|
|
|
Senior Member
|
|
Aug 14, 2006, 12:42 AM
|
|
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
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
What is a water column
[ 10 Answers ]
My question is actually two questions in one. I believe that in my studies in school I learned that a water column is a stand of water in a pipe to a certain height. If the pipe ended at the bottom of a tank then the water column would extend up through the water in the tank to the water level but...
Unique pet names for a kitten
[ 27 Answers ]
Hi I'm new. I was looking for pet names when I found this site. I was wondering if anyone had any or could come up with any ideas on pet names for a cat, with ice cream being the topic, that are unique, that you wouldn't mind me considering for my new kitten. She is calico with short hair.
I've...
Looking for unique clothes
[ 3 Answers ]
I want to find some clothes that may be rave/futuristic/technoey ( I think I just made a new word )/ j.street style looking. Can anyone help? :cool:
View more questions
Search
|