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 →
Here is a simple three column dataset showing Item ID, Catalogue Name and Catalogue Year Item ID Catalogue Name Catalogue Year A Spring 2012 A Spring 2016 A Spring 2017 B Fall 2014 B Fall 2014 B Fall 2016 The second and third last rows are clear duplicate rows. The task is to create a […]
Read More →
{ 4 Comments }
I’d like to discuss 2 cases of restructuring layouts of datasets: Case 1 Consider the 7 columns dataset below. For every Doc Number, there are 6 columns – 3 columns for the name of the employee and another 3 columns for the Percent of each employee. Doc Number Emp 1 Emp 1 % Emp 2 […]
Read More →
Imagine a multi column exam invigilation schedule with the following information S. No. Name of staff Designation of staff member Two columns for each day on which there is an exam – one for Morning and another for Afternoon A * under each column if that particular staff member has to be an invigilator during […]
Read More →
The title sounds confusing!!!!. Please bear with me and read on. Here’s a simple dataset Client ID Client Name Resource Project ID Billable amount 1 Alpha David 1000 10 1 Alpha Henry 1001 20 1 Alpha Rakesh 1002 30 1 Alpha Alice 1003 40 2 Beta Alice 1000 50 2 Beta Alicia 1002 60 2 […]
Read More →
{ 9 Comments }
Here is a dataset showing Project wise forecast of open opportunities. Topic is the Project Name Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client Duration is the time (in months) for which the project would run Amount is the total amount […]
Read More →
{ 3 Comments }
Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B. My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18. I’d like to exclude employees who: Did not work on both these days; and Worked on both […]
Read More →
Here is a sample dataset with Item and Number of buckets in the row labels and Year-Month in the column labels. In the value area section are some numbers. Item Number of Buckets 2016-10 2016-11 2016-12 2017-1 2017-2 2017-3 2017-4 2017-5 ABC 8 1500 PQR 12 40 RPS 4 100 CHA 11 30 MUM 12 […]
Read More →
Here’s a simple four column table showing date wise amount spent per project Date Project ID Project Name Total Amount 10-01-2015 P250 Project A 100 15-03-2015 P250 Project A 250 01-08-2015 P250 Project A 175 01-12-2015 P250 Project A 90 16-01-2016 P250 Project A 75 28-02-2016 P250 Project A 105 23-02-2016 P300 Project B 175 […]
Read More →
Imagine a Sales dataset with the following columns – ID, Country, Start date, End date and Tier. Here’s a snapshot of the table: ID Country Start date End date Tier 33948 ES 25-Sep-17 28-Sep-17 3 19820 US 08-Oct-17 17-Oct-17 4 9118 US 27-Oct-17 03-Nov-17 2 1563 ES 02-Sep-17 07-Sep-17 2 11087 US 18-Oct-17 27-Oct-17 1 […]
Read More →
Search for multiple phrases within a cell and extract all those phrases in another column
{ 0 Comments }