Find and Replace carriage return in MS Excel

Scenario: Here is a scenario that I have come across recently in Excel. I have the data in column that is split across multiple lines in each cell, more technically speaking, separated by carriage returns. i.e., the text in the cell is separated by pressing ALT + ENTER. see the below screenshot:

Excel_Find&Replace_CarriageReturn

Challenge: Now the challenge is to reformat the column C in a way that it may appear as in the below screenshot:

Excel_Find&Replace_CarriageReturn3

Solution: We will have to find and replace the carriage return with a comma followed by a space (i.e., “, “). Invoke the Find and Replace dialogue hitting Ctrl + H on the keyboard. Now place the cursor in the Find What text box and enter the value by hitting Ctrl + J. When you do this, the value in Find What text box appears like a very small blinking dot. Now enter a comma followed by a space in the Replace With text box. Then hit on Replace All button. Excel now pops up a message indicating the number of places the replace occurred, as in the below screenshot.

Excel_Find&Replace_CarriageReturn2

Now expand the width of column C and contract the height of all rows, so that table appears as desired.

Remember that Ctrl + J is the hotkey combination for Carriage Return.

Hope you enjoyed reading this post. Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right.

Do leave your comments or questions, if any..

Advertisements

INDEX and MATCH – an efficient alternative to VLOOKUP

Although VLOOKUP in Excel is a very useful function, it suffers a drawback. If the lookup value to be searched is not in the first column of lookup table, VLOOKUP wouldn’t work. In the last part of this learning series, titled “VLOOKUP: Right to Left Lookup” I have taken you through a trick to address this. Here is another one.

Hoping that you understand the functioning of MATCH and INDEX functions, let’s understand how the combination of INDEX and MATCH as an efficient alternative to VLOOKUP function in terms of  addressing the issue if the column in the lookup table where the lookup value is be searched is not in the first position.

Example Scenario: Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.

Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:

VLookup_3_1

Solution: Here is the formula of combination of INDEX and MATCH functions to address this issue (see the below screenshot: =INDEX(B50:B60,MATCH(F49,C50:C60,0))

VLookup_4_1

Explanation: Any nested formula is understood by understanding it from the inside to outside. i.e., the function that is nested most inside need to be understood/evaluated first and then proceed to evaluating the function nested at next higher level. So, in our example, we will understand and evaluate the MATCH function first.

Inner Function: MATCH(F49,C50:C60,0) – Here MATCH searches for the value passed in the first argument to it (i.e., value in cell F49), in the array C50:C60 and then returns number corresponding to its position in the array by counting from top to bottom in the array. so, here, it searches for “rathod” in the array C50:C60 and returns 4, since the value “rathod” appears in 4th row starting from the top of the array.

Outer Function: INDEX(B50:B60,MATCH(F49,C50:C60,0)) – Now our outer function, INDEX goes to the row number represented by its 2nd argument in the array represented by its first argument and then returns the value present in that row of the array. In our example, after the MATCH function is evaluated, the formula becomes –  INDEX(B50:B60,4). Now the INDEX function goes to the 4th row in the array B50:B60 and returns the value present in that row. i.e., 7174683825 is returned.

Testing: You can further test by changing the value in cell F49 to another customer name.

Hope you enjoyed reading this post..!!

Wondering if there are more such tricks in Excel..? No doubt, inumerable..!! Follow my blog to learn more tips, trips and techniques in MS Excel, by entering your email and then hitting on FOLLOW button at the top right. Happy learning.. 🙂

Do leave your comments or questions, if any..

VLOOKUP: Right to Left Lookup

We all now know that VLOOKUP has one major constraint that the column in the lookup table from where the lookup value is to be searched should be the first column in the lookup table.

Here is a tricky way that you can use to overcome this constraint. The trick is, if we can build the table_array argument (i.e., the lookup table) of the function at runtime in a way ensuring that the first column is the column in the lookup table that is to be searched for the lookup value, the problem is resolved. This is achieved using CHOOSE function inside VLOOKUP in the place of table_array argument to build the lookup table in desired manner at runtime.

Example: Lets understand this with an example. Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.

Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:

VLookup_3_1

Solution: Here is the VLOOKUP function with CHOOSE function in place of lookup table to return the right values entered in cell F36 to lookup the value in cell F35 and search it in the range B36:C36 (obviously not in the first column) and return the phone number. see the below screenshot:

=VLOOKUP(F35,CHOOSE({1,2}, C36:C46, B36:B46), 2,FALSE)

VLookup_3_2

Explanation:

First argument to the function:  WHAT to search/Lookup: cell F35 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: Here is the CHOOISE function is used as follows: CHOOSE({1,2}, C36:C46, B36:B46). Here the first argument {1,2} will arrange the arrays referred in 2nd and 3rd parameters in that order (i.e., the array referred in 3rd argument of CHOOSE function – C36:C46 comes first and then comes the array referred in 4th argument of CHOOSE function – B36:B46) and returns it as a range/lookup table. so when this happens, the returned lookup table will have C46:C36 as first column that has customer names where the lookup value from cell F35 will be searched, and B36:B46 as second column that has the phone number to be returned.

3rd argument to the function – WHICH column in the lookup table, returned at runtime, enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned ? Here phone number to be returned is in column in the 2nd position (i.e., B36:B46) in the lookup table returned at runtime.

4th argument to the function – How to search. Here we only want to perform exact search. So, argument passed here is 0(zero) or FALSE.

Testing:

Now in the screenshot above, we already have a customer name in the cell F35 – “rathod”, so the value returned should be: 7174683825. When you hit enter “after” key after entering the formula in cell F36, you would see the correct value being returned as in the below screenshot.

VLookup_3_3

Hope you enjoyed reading this post..!!

Want to learn more VLOOKUP tricks..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right. Happy Learning.. 🙂

Do leave your comments, if any..

VLOOKUP: Approximate match example

In the first part of this VLOOUP series, I have taken you through a simple example of VLOOKUP where while searching for the lookup value, we were doing an exact match. Now lets see an example scenario of approximate match.

Scenario: You have a table showing the percentage of discount to be given to the customer based on the number of units of the product purchased as in the below screenshot, indicating that no discount is given if less than 50 units are purchased, 2% discount is given if 50 or more but less than 500 units are purchased, 10% discount is given if 500 or more but less than 1000 units are purchased, 15% discount is given if 1000 or more but less than 2000 units are purchased, and 20% discount is given if 2000 or more units are purchased.

VLookup_2_1

Challenge: Now the requirement is that you will be given the units purchased and asked to arrive at the discount to be given to the customer. for example, if the number of units purchased by a customer is 700 units then the discount percent value to be returned is 10%. However, VLOOKUP is not intelligent enough at natural language processing that it can understand that >=500 and <1000 indicates all values between 500 and 1000 including 500 and excluding 1000. so when it searches for 700 in units purchased column, it will not find the value.

Solution: The option of approximate match for the last argument of VLOOKUP function comes to your rescue in such scenarios. Just that you need to adjust the units purchased column in a way that excel can understand it. To do this, you will insert a new column in the mid and write values as follows, as in the below screenshot:

  • write 0 in first row and 50 in 2nd row implying units less than 50 units for first row.
  • write 500 in 3rd row and 1000 in 4th row indicating units greater than or equal to 50 and less than 500 for 2nd row and also indicating units greater than or equal to 500 and less than 1000 units for 3rd row.
  • write 2000 in 5th row indicating units greater than or equal to 1000 units and less than 2000 units for 4th row and also indicating units greater than or equal to 2000 units for 5th row .

Now lets write the VLOOKUP formula against Discount % in cell G24 so that it may lookup the value against Units Purchased in the cell G23 and return the percentage discount to be given:

=VLOOKUP(G23,C24:D28,2,TRUE)

Explanation:

first argument to the function –  WHAT to search/Lookup: cell G24 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: first column of the lookup table C24:D28 is where the lookup value is to be searched.

3rd argument to the function – WHICH column in the lookup table enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned. Here discount percent to be returned is in the 2nd position as shown in the below screenshot.

4th argument to the function – How to search. Here, you want to first search for an exact value. If an exact value is not found, then search for the nearest available value that is less than the lookup value. example, if you are searching for 700 which is not exactly not found, it will look for nearest value less than 700. i.e., 500 will be the match in this case. so, we will specify 1 or TRUE here.

VLookup_2_2

Testing: Now enter a value in cell G23, say 990. For 990, since it lies between 500 and 1000, it should return 10% discount. It correctly returns the value in cell G24. you can further test by entering other values in cell G23.

Caveats: In case of all approximate search types of VLOOKUP, ensure that the first column in the lookup table (i.e., the column where the lookup value is to be searched) is in ascending ORDER. Otherwise, incorrect values will be returned. This constraint is owing to the search technique (Binary search) used by Excel internally while searching for the lookup value in the first column of lookup table.

This is one use case for approximate match type VLOOKUP. Hope you enjoyed reading this post..!! Follow this blog by entering your email and hitting on FOLLOW button at the top right, to learn VLOOKUP in more variations in the next post.

Do leave your comments, if any…

Mastering LOOKUPs in Excel learning Series

Here I thought of compiling a learning series covering the basics to classics of Excel Lookup functionality. So I have divided them into parts organized in some logically organized fashion. However, you are free to access and learn them in any order.

Part 1: Introduction to VLOOKUP

Part 2: VLOOKUP: Approximate match example

Part 3: VLOOKUP: Right to Left Lookup

Part 4: INDEX and MATCH combination – An alternative to VLOOKUP

Part 5: Cascading validation lists

Part 6: Nested VLOOKUP

Part 7: Troubleshooting VLOOKUP errors

Part 8: VLookup Refresher

If you have come across any challenges while using VLOOKUP that are not addressed in any of these parts of this learning series, do share them in the comments.

I will be enhancing this post as and when adding a new parts relating to Lookups in Excel and will enable each part as hyperlink as and when I post the content of that post on my blog.

Want to learn more Excel tips and tricks ? Follow my blog by entering your email and then hitting on FOLLOW button at the top right.

Happy learning and have an adventurous journey to mastering Lookups in Excel.

Introduction to VLOOKUP in MS Excel

Hi friends… one of the important features of Excel which every user of Excel is expected to be aware of, is VLOOKUP function. Here I take you through the nuts and bolts of VLOOKUP:

What does VLOOKUP do ? VLOOKUP function looks up (searches)  a value in one column and then returns the corresponding value in the same row from another desired column.

It’s similar to searching for the phone number of a person from a phone directory where you would first search for the name of the person and then move across that row towards the phone number column and then take a note of the value found in that phone number column.

Signature of the function:                                                                                                

VLOOKUP(lookup_value, table_array, column_index_num, [table_range])

Explanation of arguments of the function:

lookup_value: This argument indicates WHAT is the value that you want to lookup or search.

table_array: This argument indicates WHERE do you want to lookup or search. This argument is also known as lookup table. The lookup value will be searched ONLY in the FIRST column of the lookup table.

column_index_num: This argument indicates value from WHICH column in the lookup table is to be returned.

table_range: This argument indicates whether the search process need to be exact type or approximate type. 0 (zero) or FALSE indicates exact match, 1 or TRUE indicates approximate match. This argument is optional. Although FALSE is most frequently used, TRUE is the default value for this argument, if nothing is specified.

An example is better than a precept.

So, lets understand the working of VLOOKUP function with a couple of examples using some fictitious data:

Example 1.

Scenario: Here are some customer names with their phone numbers alongside maintained by an analyst. The requirement is that the manager will determine the lucky customer each month by some lucky draw and ask for his/her phone number from the analyst, so that the customer may be contacted on Phone.

Now the analyst do not want to search for the customer in that long list of customer and phone numbers manually as we do in a phone directory. He just wanted to enter the name of the person in the cell beside “Best Employee” (i.e., in G2) so that the phone number of the customer is automatically displayed in the cell beside “Phone Number” (i.e., in G3), as in the below screenshot.

Now, lets write the VLOOKUP formula in cell G3 to return the Phone Number of the Lucky Customer  present in cell G2 by looking up or searching for that customer in the lookup table spread across the range – B3:C13.

=VLOOKUP(G2,B6:C16,2,FALSE)

VLookup_1_1

Explanation:

WHAT to search: cell G2 has the name of the customer that you want to search.

WHERE to search: the first column in the range B3:C13 is the place where the customer name is to be searched.

value of WHICH column of the range in the corresponding row to return: proceeding from left to right in the range and numbering the columns starting from number 1, in mind, Phone Number column gets number 2. since we want the value from Phone Number column to be returned finally, we specify its enumerator, i.e., 2 here.

Last argument indicating Match Type: Since, we want search for the exact name of the customer in cell G2 from the first column of the lookup table, we will indicate 0 or FALSE here.

Now when you press enter after entering the formula in cell G3, it initially returns a “#NA” since we haven’t yet entered any Lucky Customer name in cell G2.

Testing:

Now lets test our VLOOKUP formula by entering a name of a customer as Lucky Customer in cell G2 to see if it returns the correct phone number of that customer.

I entered “Rathod” as Luck Customer and it correctly returned the respective Phone Number as highlighted the record of this customer in the lookup table in yellow:

VLookup_1_2

* Remember that VLOOKUP does case insensitive search. Hence, in the above example, if you have entered “rathod” in the place of “Rathod”, you would still see the right value being returned.

Caveats: If the column in the lookup table where the lookup value to be searched is not the first column of the lookup table, VLOOKUP wouldn’t work.

Hope you enjoyed reading this post..!! Want to learn more variations of VLOOKUP addressing more challenging business data analysis problems …..? Follow my blog by just entering your email id and hit on follow button at the top right of this blog post..

Complement Negation: SQL Design Pattern to solve a frequently occurring interesting business data analysis problem

Here is an interesting business data analysis problem that I have come across multiple times in the recent times. Since I have seen this problem surfacing multiple times and the solution boils down to the same technique, it becomes an SQL Design Pattern and I named this baby as “Complement Negation”. Have shared the justification for the name at the end of this write-up.

Now, let me explain the scenario with an example using some fictitious data.

We have a table named “Projects” with the status details of various projects. Each project is given a unique project id. A Project may have multiple milestones where each milestone is identified by milestone id. The table also has the details of status of project and the status of each of the milestones. Status project and milestone can be either “closed” or “in-progress” (i.e., they are complementary).

Here we create the “Projects” table using below SQL:

—Step:1. Creating the table
CREATE TABLE Projects
(ProjectID VARCHAR(5)
, MilestoneID VARCHAR(5)
, ProjectStatus VARCHAR(15)
, MilestoneStatus VARCHAR(15)
PRIMARY KEY (ProjectID, MilestoneID)
);

Now let’s populate the table with some fictitious data using the below SQL:

INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P201’, ‘MS01’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P201’, ‘MS02’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS01’, ‘in-progress’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS03’, ‘in-progress’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS02’, ‘in-progress’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P203’, ‘MS01’, ‘in-progress’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS01’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS02’, ‘closed’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS03’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS04’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS06’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS07’, ‘closed’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P207’, ‘MS04’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P207’, ‘MS06’, ‘closed’, ‘in-progress’);

So, below is how the table looks:

SELECT * FROM Projects;

05June2013

Business rule is that a project is considered to be closed only when all the milestones under the project are closed.

However, the table has some records that does not satisfy this rule. e.g.: Project – P204 has two milestones (viz., MS02, MS07) whose status is “in-progress” but the project status itself is “closed”. Similarly, Project – P207 has one milestone (viz., MS06) whose status is “in-progress” but the project status itself is “closed”. These cases that doesn’t satisfy the business rule are considered abnormal cases/Projects.

Challenge at hand: Now the challenge at hand is to query the details of all abnormal projects (i.e., details or projects where project status is “closed” but the status of one or more milestones under the project is “in-progress”) and report the complete data.

Solution: Below is the SQL used to query/retrieve all the abnormal packages.

WITH temp AS (
SELECT ProjectID, MilestoneID, ProjectStatus, MilestoneStatus
, SUM(CASE WHEN MilestoneStatus = ‘in-progress’
                  THEN 1
                  ELSE 0
          END) OVER(PARTITION BY ProjectID) AS Count_Indicator
FROM Projects
)
SELECT ProjectID, MilestoneID, ProjectStatus, MilestoneStatus
FROM temp
WHERE Count_Indicator > 0
AND ProjectStatus = ‘closed’;

Results: The results appear as below:

ComplementNegation_SQLDesignPattern

Explanation: First, in the CTE, we retrieve all the details of projects that need to be reported. Then we derive a column that counts only the number of “in-progress” milestones under each project and name this derived column as, say Count_Indicator.

Now we retrieve the records where Project Status is “closed” but with the count of “in-progress” milestones (indicated by Count_Indicator derived column) being greater than 0 (i.e., there exists at least one milestone that is “in-progress”). This is nothing but retrieving the records where project status appears “closed” but with one or more underlying milestones still being “in-progress”.

Name Justification: The word Complement – since the status’ “closed” and “in-progress” are complementary (i.e., # of closed projects or milestones within each project plus # of in-progress projects or milestones within each project equals the total # of projects or milestones within each project respectively). The word Negation – since we test for status of one field for “closed” and the status of the other field for “in-progress” (i.e., “closed” test is negation of “in-progress” or vice versa).

Hence I feel the name for this SQL Design Pattern is justified to be “Complement – Negation”.

Hope you enjoyed reading this post. Do share your comments, suggestions, questions or clarifications, if any. Have great time until my next post..!! Smile

WordPress Tags: CTE,common table expression,Microsoft,sql server,t-sql,SQL,Complement,Negation,Design,Pattern,Here,data,analysis,times,solution,technique,justification,scenario,example,status,Project,Step,CREATE,TABLE,ProjectID,VARCHAR,MilestoneID,ProjectStatus,MilestoneStatus,PRIMARY,INSERT,INTO,VALUES,SELECT,FROM,records,cases,Challenge,packages,CASE,ELSE,OVER,PARTITION,Count_Indicator,WHERE,Results,Explanation,column,Name,word,Complementary,equals,itself

Truncate time part from Datetime column or literal in Oracle SQL

I have shown how to truncate the time part from a datetime column value in SQL Server T-SQL in one of my previous posts here –

https://analystarsenal.wordpress.com/2012/12/19/comparing-datetime-column-with-a-date-literal-in-t-sql/

Here is the same (how to truncate the time part from a datetime column value) in Oracle SQL:

TRUNC(datetime_column)

or

TRUNC(datetime_expression)

very straight forward…!! isn’t it.. ? 🙂

Microsoft Office Excel 2003 menu to 2010 ribbon mapping Workbook and Interactive Reference Guide

Friends..

Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded:

http://office.microsoft.com/en-in/templates/results.aspx?qu=Excel%202010%20Menu%20to%20ribbon%20reference%20workbook&queryid=6b2d60e7%2D5451%2D4a60%2Daee7%2Dc4f4983aa767#ai:TC101842354

Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded:

http://www.microsoft.com/en-us/download/details.aspx?id=16642

Hope you will find this helpful.. 🙂

Microsoft Office Excel 2003 menu to 2007 ribbon mapping Workbook and Interactive Reference Guide

Friends..

Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded:

http://office.microsoft.com/en-in/templates/excel-2007-menu-to-ribbon-reference-workbook-TC010212862.aspx?CTT=5&origin=HA010149151

Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded:

http://www.microsoft.com/en-us/download/details.aspx?id=14650

Hope you will find this helpful.. 🙂