A magical Excel trick for flattening data copied from a PivotTable report in tabular layout into usable format

Magical Trick for Data Flattening in Excel

Here is a scenario that I come across sometime ago when I received an Excel file with some apparently messy data for further processing to generate another report. The data in the file appear similar to below screenshot. This data is self-explanatory. It’s the sales in units and dollars of each product under each product category made by each sales person. If you observe the data, for all the sales made by each sales person, name of the sales person is listed only once. Same goes with product category appearing only once for each product.

Now the challenge is to flatten the data in a way that for each product row, respective product category and sales person name should get repeated. Only then, it becomes consumable in downstream process for further preparation of another report. By the way, this is just some fictitious small amount of data. The file I received has more than a few thousands of records, which means it’s not feasible to perform this task manually.

By the way, this kind of data mess happens when you copy the data from a PivotTable report in Tabular Layout, with Repeat All Item Labels option not selected under Layout group of Design tab, +/- toggle button disabled under Show group of Analyze tab of PivotTable Tools ribbon; and then paste the values of such copied data to different location for the further sharing, like it was shared with me.

ExcelDataFlatteningSource

Here is a magical trick to complete this task accurately in just a couple of steps:

Step.1. Apply filter to the header row. Click on Salesperson field filter drop down. Deselect all items and then select only blanks. Hit OK.

The data looks as below after this step.

image

Step:2. After applying the filters, observe that the cell address of the first blank cell in Salesperson column is A4 (boxed in red). This means that the cell above A4 (i.e., cell A3) is the first non-blank cell. This gives us a logic that the value in A4 should have been the same as the value in cell A3. So, just enter the formula =A3 in cell A4. The same logic applies for all the other blank cells too. So, copy the formula to the rest of the blank cells. After this, clear the applied filters from salesperson column header. That works the MAGIC..!! This completely fills up all the blank cells in the Salesperson column accurately as in the below screenshot:

image

Repeat these two steps for Category column as well. The magical trick works there too..!!

For more such tricks follow the blog. Hit the likes if you like the trick. Happy Learning..!!

Advertisements

Trick for Listing the names of all sheets in Excel workbook

List of Sheet Names output

Here is a 2 step technique to retrieve the names of all sheets in Excel workbook:

Step:1. First define a named range using the below formula:

Step: 1a. Click “Define Name” command in “Define Names” group under “Formulas” ribbon tab to invoke “New Name” dialogue

Step: 1b. Let the name of the range be “SheetNames”

Step: 1c. Let the name refer to the range defined by the formula: = Get.Workbook(1)

Step: 1d. Click OK

New Name Dialogue
New Name Dialogue

 

 

Step:2. Write  the below array formula simply referring to the range name defined in Step 1 to get the names of all the sheets in the current workbook listed:

– Since there are 3 sheets in the workbook in the screenshot, select any three consecutive cells in a row.

– Enter “=” to start writing the formula and then type the range name defined in Step.1 as in the below screenshot:

Array Formula referring the defined Name
Array Formula referring the defined Name

 

– Hit CTRL + SHIFT + ENTER , to enter the formula as array formula and the list of all sheet names are listed. See the below screenshot:

List of Sheet Names output
List of Sheet Names output

Note that the sheet names are listed along with the workbook name. You can write a formula using Excel’s built-in functions to extract only sheet name from these names that are qualified by the workbook name.

Happy learning..!! Do leave your comments.

Generating Date Dimension data in Microsoft Excel

Generating Date Dimension data in Excel

Having shared the SQL technique and the script for generating data for Date Dimension for use in self-service BI and Analytics in my , here I share another technique for the same purpose but for those of you who are not familiar with SQL.

Here is the that lets you create Date Dimension data for two years from a given start date. All that you have to do is enter the desired start of the year in date format in cell B3. Rest is taken care that everything refreshes automatically.

Hit the like stars if you find it useful. Share the post further to spread the learning. Follow the analyst arsenal for effortless continued learning. Happy Learning..!!

Truncating time part from date & time data in Excel

Did you ever come across a situation where you had to compare two dates ignoring their time part to arrive at a status indicator ? Let’s see an example and the trick to address it:

Example Scenario: Here you have some order ids and their respective order dates, target dates and delivery dates as in the below screenshot. Now, you are asked to arrive at the order status using a business rule that states – “If the delivery date is on or before target date, disregarding the time of the day, then update the status as ‘Target Met’ else update it as ‘Past Due’.”

Excel_TruncateTime_1

so the business rule when translated into Excel formula, looks like this: =IF(D2<=C2, “Target Met”, “Past Due”)

When this formula is entered in cell E2 and copied down till E8, the order status looks as in the below figure:

Excel_TruncateTime_2

As per the business rule, the comparison of Order Date and Delivery Date should ignore time part of the day. Irrespective of what time of the day the order/product has been delivered, if the delivery date falls on the target date, the status should be updated as ‘Target Met’.

However, the status’ for Order IDs 12126 and 11231 are updated as ‘Past Due’ although they are expected to be updated as ‘Target Met’.

Challenge: While comparing Order Date and Delivery Date, Excel is comparing the complete date and timestamps.

Solution: To let Excel consider only dates and ignore timestamps while comparing, truncate the timestamps from dates using INT function, before doing the comparison. So the formula becomes:     =IF(INT(D2)<=INT(C2), “Target Met”, “Past Due”)

Now the result appears as desired as in the below figure.

Excel_TruncateTime_3

Explanation: All dates and timestamps in Excel are actually serial numbers. 1-Jan-1900 is treated as 1, 2-Jan-1900 is treated as 2 and so on. While Excel also stores timestamps as fractional numbers between 0 and 1. 0 represents 12:00:00 AM (start of the day) and 0.999 represents 11:59:59 PM (end of the day).

Using this process, in our example, for the Order ID 12126, the numerical representation of Order Date becomes 41443.66 and for Delivery Date, it becomes 41443.68. When these two values are compared, Delivery Date (41443.68) is not less than Order Date (41443.66), so the status is updated as ‘Past Due’.

INT() is a function in Excel that takes a number as input and returns the number rounded down to the nearest integer. i.e., for Order ID 12126, INT(D4)<=INT(C4) becomes INT(41443.68)<=INT(41443.66) which evaluates to 41443<=41443 which is TRUE. Hence the status gets updated as ‘Target Met’.

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 or simply click on the FOLLOW button at the bottom right of the window, if it appears in your browser.

Do leave your comments or questions, if any. Happy learning.. Smile

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..

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..

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..

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.. 🙂