Here’s a dataset with 4 columns and 2 rows. In column A, there is Patient ID and in the other columns are observations recorded by 3 Doctors for each patient. As can be seen, in each cell there are multiple observations which are either separated by Alt+Enter (another line in the same cell) and/or a […]
Read More →
In this simple 3 column dataset shown below, one can see the month wise demand and energy charge for 2 years – 2017 and 2018. The objective is to compute the month wise demand charge for x months ended a certain user defined Year and Month. So, if a user selects the Year as 2018, […]
Read More →
{ 2 Comments }
In a certain Organisation, assume that there are two work schedules – one that runs from Friday to Thursday and another that runs from Sunday to Saturday. In the image below, Work schedule 1 is in range A2:C4 and Work schedule 2 is in range E2:G4. In each dataset, the start date is the first […]
Read More →
{ 0 Comments }
Visualise a simple 6 column Table as shown below – Project Name and the finish date for each of the 5 stages that the projects go through. Each project goes through 5 stages – Requirement (Req), Development (Dev), UAT, Implement and Warranty. The objective is to report on the status of each project at the end […]
Read More →
Any well arranged dataset should be “Pivot Table” ready with the following 3 important properties: There should be no merged and centered cells; and Every column should have a unique heading; and Every column should have only 1 heading Here’s one dataset which violates all rules mentioned above. Headings in row 1 are merged; and […]
Read More →
Consider a 2 column dataset as shown below User Location AAA Tokyo AAA Osaka AAA Nagoya AAA Hakone AAA Kyoto BBB Sapporo BBB Nara CCC Tokyo CCC Hakone CCC Osaka DDD Osaka DDD Tokyo Customer AAA travelled from Tokyo to Osaka, Osaka to Nagoya, Nagoya to Hakone and Hakone to Kyoto. All locations appear in […]
Read More →
Here’s a simple Sales data of a retail Store which sells Apple Products. Since a customer can transact multiple times, there will be repetitions in the Cust ID column. While Cust ID 123 and 782 purchased multiple products from the same Store in one transaction, Cust ID 53 purchased multiple products from different stores (Store […]
Read More →
Imagine a dataset like this. This dataset shows vendors that submitted proposals for supplying various parts to a Company. There is one column for each of the twelve months. Via a simple Pivot Table, one can determine the lowest bidding vendor per product (part) for any chosen month. However, one may also want to know […]
Read More →
Assume a single column dataset as shown below. As you can observe, there are multiple entries appearing the same cell separated by commas. The objective is to sort, in ascending order, the entries in each cell. The expected result is shown below. I have solved this problem using Power Query a.k.a Data > Get & […]
Read More →
Assume a simple two column dataset as shown below. In column A are some text strings. In each cell are multiple phrases separated by commas. In column C are some phrases – one in each cell. The objective is to search for all phrases in each cell and extract the phrases found in another column […]
Read More →
Remove duplicates from each cell of a dataset
{ 1 Comments }