Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Counting & Matching Text
Dear Experts,
Please consider this & help me to sort this out: I have a Data Sheet contains 5 col namely: Emp; From; To; #OfDays; LeaveType I fill these columns on daily-basis using designed VB Form as below Emp From To #OfDays LeaveType ----------------------------------------------------------------------- Mr. A 01-Jan-06 02-Jan-06 2 Sick Mr. B 08-Jan-06 08-Jan-06 1 Casual Mr. C 12-Jan-06 16-Jan-06 5 Earned Mr. A 18-Jan-06 20-Jan-06 3 Casual ....so on Now, I have a complete list of Employees & we have 3 Leave Category i.e. Sick, Casual & Earned Leave What I want to do from above lengthy data is: Emp Sick Casual Earned Total Leaves ------------------------------------------------------------ Mr. A 2 3 0 5 Mr. B 0 1 0 1 Mr. C 0 0 5 5 ....so on I tried the Vlookup formula but I can not use it with Count forumla...Is it possible? I tried my best to explain the situation, If still I am unclear, please ask Best regards, Murtaza PS: I know you people must have already provide the solution for this kind of problem, but how can I search it in Excel Newsgroups. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting & Matching Text
It seems to me that the sumproduct function would be appropriate col A col B col C col D col E name from to days type tom jan 1 jan 4 3 sick dick feb 1 feb 9 8 earned harry mar 1 mar 3 2 casual tom jan 1 jan 4 3 sick dick feb 1 feb 9 8 earned harry mar 1 mar 3 2 casual sick casual earned row 10 tom xxxx row 11 dick row 12 harry xxx = =sumproduct((a1:a7=a10)*(e1:e7="sick") you should be able to work out the next to formulas and then add the results in the final column bob -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504 View this thread: http://www.excelforum.com/showthread...hreadid=511024 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Counting & Matching Text
You could get the results you want using a Pivot Table and making leave type
a pivot column. -- Kevin Backmann "Murtaza" wrote: Dear Experts, Please consider this & help me to sort this out: I have a Data Sheet contains 5 col namely: Emp; From; To; #OfDays; LeaveType I fill these columns on daily-basis using designed VB Form as below Emp From To #OfDays LeaveType ----------------------------------------------------------------------- Mr. A 01-Jan-06 02-Jan-06 2 Sick Mr. B 08-Jan-06 08-Jan-06 1 Casual Mr. C 12-Jan-06 16-Jan-06 5 Earned Mr. A 18-Jan-06 20-Jan-06 3 Casual ....so on Now, I have a complete list of Employees & we have 3 Leave Category i.e. Sick, Casual & Earned Leave What I want to do from above lengthy data is: Emp Sick Casual Earned Total Leaves ------------------------------------------------------------ Mr. A 2 3 0 5 Mr. B 0 1 0 1 Mr. C 0 0 5 5 ....so on I tried the Vlookup formula but I can not use it with Count forumla...Is it possible? I tried my best to explain the situation, If still I am unclear, please ask Best regards, Murtaza PS: I know you people must have already provide the solution for this kind of problem, but how can I search it in Excel Newsgroups. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Counting similar text | Excel Worksheet Functions | |||
Compare cells/columns and highlight matching text strings | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |