Migrated

Dear visitors, we have moved our blog to http://www.excel-course.com. Do visit us using the link. Thanks!

Comparing 2 set of data from 2 different dates

In last Friday training, one of the participants came with an exceptionally interesting problem. On a weekly basis, she needs to donwload a spreadsheet from the HR systems which will list down the employees' profiles, such as employee ID, Name, Department, Reporting Manager, etc. Using the downloaded worksheet, she has to compare the details against the previous week worksheet and identify the changes that have taken place. The first answer i thought of is to use VLOOKUP. But the problem is there are more than 20 columns and any of these columns could be different from the previous week. And there are not just a few rows but a few thousand rows. So how on earth can we identify all the cells from 20 columns x say about 6000 rows, total of 120,000 cells? Impossible if we are to eye ball at every cells.

The solution called for the use of VLOOKUP formula using employee ID as the lookup value plus conditional formatting. Using a formula based conditional formatting option, we can input the VLOOKUP formula (to pick up the data from the previous week) and compare the result with the value in the current cell. (Take note that this is not the normal VLOOKUP formula. You have to setup the VLOOKUP formula so that it will return the value TRUE or FALSE, the basis for conditional formatting to work.) If the values are different between the 2 weeks, the cell will be highlighted with a colour. This will allow us to zoom in into the colored cells and work with them. Of course, this is based on the assumption that most of the cells would remain the same between the 2 weeks. We have to make the formula more dynamic and To make the formula dynamic, we have to add in the COLUMN formula so that it can automatically change it's value as you apply the conditional formatting to the right and down the worksheet).

Create a list with no blank cells

Somebody sent me an excel file containing a list of hospitals in Column A. In the first row, it listed 40 different types of rebates. For each hospital, only certain rebates were used.

He would like to create a dropdown list for each hospital, showing only the rebates that contain a value. For example, he would like the dropdown list to show only Rebate1 and 3 for hospital 1 because only these 2 rebate columns contains values. He asked if it can be done.

I have tried googling on whether I can create a list with no blank cells before and couldn't find a solution. But I deceided to try again. To my surprise, I managed to find a few websites that demonstrated how it can be done using formulas. One of them that explained how it can done can be found here.

I modified the formula to do it for columns. Then I setup the formula such that he can do a double selection, select an hospital first before showing the list of rebates. This involved the use of the all powerful OFFSET formula to complete the solution. To see how it works, download this excel file.

List of Working Days

Question: How do I automatically create a list of working days from one date to another? I want to make a range of dates from 8/31/11 thru 3/1/2012, for example. I want to have the dates listed in a column.

Answer:  You can use the Workday formula WORKDAY(start_date,days,holidays)

About Edate

Question: We use edate function for finding the date after  certain months .do we have similar functions for years and days?

Answer: As far as we know, edate is the only function in Excel. For years, we always multiple the number of years by 12. EYear is not really necessary because the number of days is consistent for the months in every year except Feb. There is no need for days, just add the number of days to the start date.

Extract data within brackets

Problem:
I want the word in brackets to appear only (Under the Class Column). Eg:

Degree Class
BACC(MERIT) MERIT

Answer:
You can use the formula


=MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-1-SEARCH("(",A2))

Or TEXT-TO-COLUMN with “(“ as the separate. To remove the “)” use find “)” and replace with nothing.

Problem:
What if I want the data from within the 2nd bracket?
Degree Class
B.ENG.(CHEM.& BIOMOL.ENG.)(SECOND LOWER) SECOND LOWER

Answer:

You can use the formula.

=MID(A2,SEARCH("(",A2,SEARCH("(",A2)+2)+1,LEN(A2)-SEARCH("(",A2,SEARCH("(",A2,SEARCH("(",A2)+2))-1)

Work Order Status

Background



1. I will receive work order from planning section
2. I will key those information into excel "work order" sheet from column C to G
3. My production member will follow the work order to assemble as per required
4. After completed, I request them to key in the instrument serial number + details on "Instrument SN" sheet.
5. I create "pivot table" so to understanding the daily output of requirement
6. I want to link the daily output qty from "Pivot table" sheet to "Work order" sheet so to monitor whether Work order completed.

Problem encountered:


Step 6: I need to match date from "Pivot table" to date to "work order" as well as work order no. from "pivot table" to "work order". I have no idea how to do for 2 dimensional matching. At first I try to use sumif but fail.

Answer

I used GetPivotData, a formula that can be used to extract data from Pivot Table dynamically for this. You can change the parameters to pick up the data basd on 2 conditions, the workorder and the date. The number picked up is already a consolidated number from pivot table. I have combined it with IF and ISERR to make sure that all the cells return zero if no data is found.

You can find the solution in this file http://www.everydayexcel.com/files/work_order_status.xls

Why HR need to use Excel?

1. To monitor the renewal of passports/work permits
2. Calculate anniversaries of employees
3. Track trainin programs attended by employees
4. Analyse benefits paid out
5. To compute salary details including CPF.

 

Display Product Image (con't)

Yesterday, I saw a promotion list and found that there were too much information in the print out. It reminded me of a price list that I had from a distributor. There were thousands of products in the list and navigating through the list was a nightmare. So I decided to improve on it with 2 dropdown lists, one for selecting the product model and the other to select the product code. The second list is dependent on the selection on the first list. It helps to zoom into the particular product group and reduce the no of models for selection from a few hundreds to a few. And combined with the Display Product Image solution, I can now view a particular product model and the given description easily. Download this file for more details.