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.

Display Product Image


This solution is created with a combination of functions and formula. The frame for the changing image is created using the Camera function in Excel. Adding the Offset and Match formula make the address reference change according to the product code appearing in Cell H2. We use data validation to create a dropdown list for users to select the product code to display.  

Autofill Frustration

Question: when I drag a date to the line below it, the date advances by one day. I don't want this. I would like the same date to drag down each time

Answer: If you turn off autofill, you will also turn off the ability to drag cells down. I don't think this is what you are looking for. An alternative approach is to highlight all the cells you wish to fill, type the value and then press Ctrl + ENTER at the same time. It will help you fill up the highlighted cells with the same date. 

Financial Quarters

If your company has financial year that does not coincide with the calendar year, you will have problem presenting your numbers in Financial Quarters, especially when you have to work with hundreds or thousand of records. But by employing a few formulas, this job can be done in just a few minutes. Take a look at my new write up on Calculating Financial Quarters.

find a particular word from of group of words in a cell

Question:
can excel find a particular word from of group of words in a cell and in turn should give a particular output for a particular text, the text needs to be searched and the output which needs to be displayed will be given when created so that from the next time if it finds that text it needs to give the output which is mentioned earlier, can you please help me on this?

Answer:
You can use the SEARCH formula to find the word. Combined it with IF and the ISNUMBER formula. Assuming that the list is in column A and we are working on A2. The formula to place in B2 would be

=IF(ISNUMBER(SEARCH("REPLACE THIS WITH YOUR OWN TEXT",A2)),"THE OUTPUT YOU WANT","")

Which view is good for Pivot Table and data management?

Question: I have a large data of about 700 columns & 4000 rows relating to the sales.Great of columns(fields) are due to no. of sale-able items (About 670)


I have designed the table with each individual sale-able items as separate Fields instead of One field under Item Name as I want to view sales transaction in one record. This is to overcome disadvantage of entering data for party name, bill no, date...etc several times for sales transactions having more than one items.
But when I converted the data into Table & make use of Database functions like dsum, dget to extract the results, it slows down.Calculation is very slow,even if I open or save the excel file it takes a lot of time.

I tried pivot table but it didn't worked As I need to drag and drop almost 650 fields.


Answer:
The correct approach is what you have been avoiding, that is, to put the items names under One column. This arrangement facilitate the use of Pivot Table to generate results. What you are concerned with, to view transactions in one records can still be achieved in the Pivot Table view, instead of the raw data view. Right not, you have no choice but to re-organise everything in the other view. A macro can be very helpful for this situation.

Convert multiple rows record into one single row

Question:

The worksheet shows some payments under each customer (header). How can I apply the “sum if” formula to copy the customer code and customer name to every “PY” in Column D?

Answer:

You cannot apply SUMIF to copy the customer code and company name to every PY in column D. The formula you should use is IF.

First, you must put the customer code and company in the first row. The best way to do this is to use the formula method. For example, in A1 put the formula =C1 to display the customer code in A1.

Using the IF formula, we can set the condition to pick up the customer code or the company name is column D contains "PY", i.e. in cell A1, enter the formula

=IF(D2="PY",A1,....)

That is the first part.

Now the IF formula has a part to present a value when column D does not contain the text "PY". Specifcally, we are interested in is to return the company name if it ever changes and discards all other values. To do this, we have to find the pattern that we can use to identify that the row contains company name. We can check whether column E is empty. We can use another IF formula to do this. Since we have dealt with "PY" in column D, it will not pick up "PY" again. In this case, it will pick up the customer code when it detect the column E is empty. So we have to add another IF formula into the first IF formula. Now the formula becomes

=IF(D2="PY",A1,IF(E2="",C2,....

If it does not find a blank, return the value that is above the formula, i.e. A1. So the the formula in A2 finally becomes

=IF(D2="PY",A1,IF(E2="",C2,A1))

Cannot format my dates in Excel

Question:

I have a worksheet containing hundreds of rows of data whereby no matter how I tried formatting the “date” (Column D), it still does not shows in ascending or descending sequence but instead it sort by the 1^st  2-digits of the data.

Answer:

The date column is actually not presenting the dates in the right format. They are text formatted probably because your computer is accepting dates with dd/mm/yy format while the dates in your worksheet is in mm/dd/yy. Or it could be the other way round. To resolve this, the fastest way is to use

Data > Text to Columns > Next > Next > Date Format (Select “Date”)” and choose the right date format (dd/mm/yy) for your dates.

After the conversion, you will be able to format the dates in any format you prefer.

Presenting numbers in thousands

Watch the video below and find out 4 different methods to display a number in thousands. The 4th method is real cool! I am sure you will like it.

Mesh two tables of data

Question:
How can i "mesh" two tables of data?  Each block of data has a common column with the person's last name.  When I sort, however, there are missing names in the smaller block of data.  I want excel to link or mesh the two blocks.

Answer:
You can apply the VLOOKUP formula on the bigger table for those fields you want to combine. If the formula find the last name (common field), they would return the values found in the smaller table. If not, you will get a #N/A error which is an indication that the value is not available in the small table. Remember to set the last parameter to FALSE.

Combined First Name with Last Name

Question:
I have 1 column of 2000 rows.  Each row consists of a person's name; street address; city, state, and zip code.  Thus far, I've gone to the "Data" tab then "Text To Columns" used the "Delimited" option to delimit by "Space." That option has seperated the text within each row into seperate cells.  How do I combine the cells to combine,for example, first name with last name, house number with street name, and so on?

Answer:
You can use the "&" key which is a substitute for concatenate. Assuming that your first name and last name is in A1 and B1 respectively, you can combined them together (with a comma in between them) using the following formula:

=A1&", "&B1

Remove line breaks

Question : I have a report with more than 60K rows and in one column, the cells contains text in more than one line (within the same cell). Instead of manually removing the line breaks cell by cell, is there a easier way?

Answer : There are 2 ways to solve this problem.
One is to go to format cell, alignment tab and remove the check mark on wrap text.
The second method is to use the CLEAN formula. CLEAN formula removes all non-printable characters from the cell and line break is one of them. Assuming that the text is in A2, enter the following formula without quotes in B2 "=clean(A2)".