Count Unique Values based on 2 columns
Hello Again,
I am trying to count the unique values based in 2 column.
Please Login or Register to view this content.
ColumnB2 Column D2
Class Relation
B CHILD
VIP Employee
B CHILD
C CHILD
B CHILD
B CHILD
A Spouse
A Spouse
A Spouse
A CHILD
B CHILD
C Employee
C Employee
C Employee
Please Login or Register to view this content.
ColumnL2 ColumnM2 ColumnN2 ColumnO2
CLASS EMPLOYEE SPOUSE CHILD
VIP 1 o
A 0 3 1
B 0 0 5
C 3 0 1
Can anyone help me to solve this issue please
Last edited by hecgroups; 02-12-2012 at 11:31 AM.
Try using a Pivot Table from Data|Pivot tables and Pivot Charts.. follow the prompts, in third dialogue, click Layout and drag headers to appropriate areas.
http://peltiertech.com/Excel/Pivots/
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
the problem is the list is automatically generating with other calculation that is why i cannot use pivots any formula that can be applied to the cell
See attached...
With data in Sheet1, column A and B
In sheet2, A2 use formula:
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down. This accomodates for up to 40 items in Sheet1.. adjust as necessary (but not too large).Please Login or Register to view this content.
then in B2:
copied down and across.Please Login or Register to view this content.
Hai NBVC,
I did not understand the first formula. Why you want me to put in sheet 2 columns whereas without this formula the attached sheet showing correct result. Let me applied this formula to my original file. I will get back to you if i got an error.
As you said the quantity is 40 but might me my data will be large. Can you tell me uptill what is the last range i can apply.
You can put it in Sheet1, if you want.. then you don't need to reference the sheet name.
The first formula actually can just be confirmed with ENTER... but it is still a formula that will take much processing times, if the range is too large... you can go to 1000 or 2000 without too much problems, I think.
Thanks brother. It works file on my original sheet.
God Bless you buddy.
One more request on the same. Can it be possible to create macro it will be easy for me. because once i clear my data list the values still remain their which i don't want.
The requirement is if the data present in the cells then the values will appear otherwise the cell which contain formula will be empty not even zeros.
Please Login or Register to view this content.
I am not a VBA expert... Please post a new question in the Programming forum if you want VBA solution.
Ok kindly see the updated sheet.
I forget to mentioned that i have to calculate the average.
Kindly see the attached sheet.
The theme is total employee in each class have rate so the calculating is total employee's rate / no. of employee.
See the attached sheet for easy reference.
Not sure, but perhaps:
=IFERROR(VLOOKUP($A11,Sheet1!$A:$D,4,0)/VLOOKUP($A11,$A$1:$D$5,2,0),0)
copied down
same in other columns only change the 2 in the second VLOOKUP to correspond to column number from table at top of sheet2.
I will be very thankful to you if you upload my sheet back with your formula which is easy to understand. Because i am not very much use excel formulas. i hope you understand.
Thanks for your help and support to solve this critical issue.
Here you go.
One question can you tell me about that2 in red color.
The 2 is the column index number for the Vlookup... it means return results from 2nd column in the table (ie Employee).. then in the SPOUSE column, you change the number to 3 to get from 3rd column, and for Child from column 4.
thanks for your help. what will happened if my only calculation result is on the same sheet not on other sheet. I have change the formula as i need but the values are showing zeros.
I have attached file can you check it please.
You mean as per attached...
Formula in M14:
=IFERROR(VLOOKUP($L14,$B:$J,9,0)/VLOOKUP($L14,$L$5:$O$8,2,0),0)
copied down
and to other columns with appropriate column index adjustments...
thanks for respond. Can you explain me what is 9,0 & 2,0because i am getting wrong result
Even on sample sheet it is wrong.
total employee for VIP is 11 with total amount is 504.30
504.30/11=45.84
Where does the 504.30 come from?
This is the total of 11 Vip employee. per VIP employee rate is 45.85
So, 45.85*11 = 504.30
please the sample file which i have given to you for reference
Well, I am using the same logic as we had previously.... so not sure what you want any more.
See attached for my table.
The first VLOOKUP looks for VIP in column B and get the rate from the 9th column in B:J (which is column J). It then divides by the number in the upper table that corresponds to VIP in L5:L8 and takes number from Employee column, i.e. 2nd column in L5:O8 (the second VLOOKUP)
now i understand.
It should take all the final rate i.e. column j which is 504.30 then divided by values of upper range i.e. M5:O8.
But what you are doing is counting the VIP's again and dividing it by upper range values i.e. M5:O8
Then I think you want:
in M14:
=IFERROR(SUMIFS($J:$J,$B:$B,$L14,$D:$D,M$12)/VLOOKUP($L14,$L$5:$O$8,2,0),0)
copied down
in O14:
=IFERROR(SUMIFS($J:$J,$B:$B,$L14,$D:$D,O$12)/VLOOKUP($L14,$L$5:$O$8,3,0),0)
copied down
in =IFERROR(SUMIFS($J:$J,$B:$B,$L14,$D:$D,Q$12)/VLOOKUP($L14,$L$5:$O$8,4,0),0)
copied down
Thanks NBVC
It works?
Thanks to you from my bottom of my heart for your efforts and help which you have provided to me.
i will get back to you if i got any error otherwise i will marked this topic as closed.
Reputation have been added to you.
You are welcome. Sometimes it takes a while for both parties to get onto the same page.
Good luck.
Hello NBVC,
I have discovered some errors on the above request. What will i do. Can i continue here or open a new topic and notify to you.
for your suggestion please.
If directly related to this question, then post here, else start a new thread if it is a new topic.
This code is calculating all the values which is having Zeros.Please Login or Register to view this content.
What i want is if the zeros in the final amount (Column J) then is should not count.
kindly see the previous attached sheet for more details.
Sorry i forget to provide you the sample.
Check the SPOUSE in class C is 174 but the total premium is for 172 the rest 2 records is having zero in Column J
so, it should calculate total premium/172 only not 17 because total members is 174 but the total premium is for 172 members only
Change formula in M5 to:
=IF($L5="","",COUNTIFS(Data!$B$2:$B$2011,$L5,Data!$D$2:$D$2011,M$4)-COUNTIFS($B:$B,$L5,$D:$D,M$4,$J:$J,0))
copied down and across the table.
Last edited by NBVC; 02-13-2012 at 04:11 PM.
No No No.
What i am trying to tell you is if should count the census all but bottom table when it dividing the total amount (Column J) with total members at that time it should take only 172 members not 172.
This formula is showing that the total members is 172 whereas actual members are 174.
I hope this make you clear
Unfortunately not!Originally Posted by hecgroups
It solved now thanks for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks