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

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…