Here is a simple 5 column dataset showing basic employee information. The objective is to show only those rows of data in which the Score (column D) is greater then 3. While one can solve this with a simple filter, the solution will not be dynamic. To get a dynamic solution, one may use the […]
Read More →
{ 0 Comments }
Visualise a 3 column dataset as shown below – Location, Product and Sales. Each location can have multiple products (Product A has Banana, Apple and Carrot) and each product can be sold in multiple locations (Banana is sold in locations A, B and F). The objective is to determine the location with highest sales for […]
Read More →
Assume a three column dataset with Patient ID, Smoking Status and Review Date PatientID SmokingStatus ReviewDate P1 10-03-2018 P1 9 09-03-2018 P1 1 08-03-2018 P1 4 07-03-2018 P2 9 10-03-2018 P2 9 09-03-2018 P2 9 08-03-2018 P2 9 07-03-2018 P3 2 10-03-2018 P3 09-03-2018 P3 9 08-03-2018 P4 9 10-03-2018 P4 1 09-03-2018 P4 4 […]
Read More →
{ 34 Comments }
In range A1:BG2185 of this workbook, there is macro economic and demographic data for years from 1984 to 2009 (26 years) for 84 countries . Furthermore, there are 57 parameters being tracked for each of the 26 years for all 84 countries. There are missing observations for some years across many parameters and many countries. […]
Read More →
{ 4 Comments }
Assume a three column database – Site ID, Reason & Date and time. The data is available in range A2:C6 of sheet1 of this workbook. The objective is to view all those records where Mains failed between 23:00 and 03:00 (irrespective of the date). Applying a Custom Filter > Between on the Data and time column […]
Read More →
Assume data in range B4:B18 (heading is in cell B3). To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down =B4=B5 True’s indicate matching values with the value in the next row. However, the above formula would fail when […]
Read More →
{ 0 Comments }
Assume data in range A3:D8 (headings are in A3:D3). One may apply an auto filter on any column(s). The task is to count the blank cells in visible rows of range C4:C8 and D4:D8. You may refer to the data set and my solution in this workbook.
Read More →
{ 72 Comments }
Assume there is data in range A2:BG2185 of a worksheet (Range A1:O1 have headings). In column A are years and in column B are Countries. Other columns have some text/numeric data. In column A are years ranging from 1984 to 2009 for each country mentioned in column B. Therefore, Albania would appear in range B2:B27 and 1984-2009 […]
Read More →
{ 4 Comments }
Assume the following numbers in range C5:C22. Heading is in cell C4. Range C5:C7 – 11 Range C8:C11 – 14 Range C12:C13 – 23 Range C14-C19 – 56 Range C20-C22 – 78 One may want to colour cells for every change in number in range C5:C22 with the following two modifications: 1. Shading should be for […]
Read More →
{ 0 Comments }
Assume a three column database showing Site ID, Customer, Status and Requested Date. On the same site ID, the same customer may have different status on different dates. In such a scenario, one may want to know the farthest/latest requested date and its corresponding status for all unique combinations of Site ID and Customer. I initially attempted to […]
Read More →
After filtering a dataset, allow the user to display only specific columns in the result
{ 2 Comments }