Count Unique Values based on 2 columns
Hello Again,
I am trying to count the unique values based in 2 column.
Search within this list and get the result above format
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
Get Result below
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 10: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/
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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).=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Sheet1!$A$2:$A$40,MATCH(0, INDEX(COUNTIF(A$1:A1,Sheet1!$A$2:$A$40),0),0))))
then in B2:
copied down and across.=IF($A2="","",SUMPRODUCT(--(Sheet1!$A$2:$A$40=$A2),--(Sheet1!$B$2:$B$40=B$1)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
Actual formula:=IF($L5="","",SUMPRODUCT(--(Data!$B$2:$B$2011=$L5),--(Data!$D$2:$D$2011=M$4)))
I am not a VBA expert... Please post a new question in the Programming forum if you want VBA solution.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks