Tags: VLOOKUP

Determine the most recent status after satisfying certain conditions

{ 0 Comments }

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 →

Determine number of learners who have completed different stages of multiple online courses

{ 4 Comments }

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation: Learner Stage completed Course Bill Stage 1 Public Speaking Bill Stage 2 Public Speaking Bill Stage 3 Public Speaking Susan Stage 1 Effective Communication Bob Stage 1 Public Speaking Bob Stage 2 Public Speaking Sheila […]

Read More →

Return best possible fit, to manually entered dimensions, with the intent to minimise wastage

{ 5 Comments }

Refer to a simple 5 column representative inventory dataset of a Glass manufacturer: Model Length (MM) Wide (MM) Thk (MM) CAT HX9-G-ARD 1071 273 3.5 A MYP-G-3RD 580 535 3.2 B EPO-G-3RD 580 535 3.2 A MYG-G-3R 966 350 3.2 A MYN-G-3RD 649 530 3.2 A GM SPIN-G-3FD 882 395 3.2 A MY8-G-AR 880 400 3.5 B […]

Read More →

Speeding up a lookup task on a large database

{ 0 Comments }

Performing calculations or writing formulas on large databases pose the following major problems: 1. Time taken to process is unduly long; and 2. File size increases disproportionately A typical problem is fetching data from another worksheet or workbook via the VLOOKUP() function.  Quite often, when one confirms the VLOOKUP() formula in a single cell with the Enter […]

Read More →

Create a Pivot Table from multiple individual ranges without using ancillary columns

{ 16 Comments }

Assume that there are three separate tables showing the following information: 1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times 2. Fee per visit in different cites 3. Region in which each city lies The question is to analyse the three tables above via […]

Read More →

Remove duplicates after satisfying additional conditions

{ 3 Comments }

Assume a two column database of patient ID’s and service availed.  One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed.  For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows. […]

Read More →

VLOOKUP() function to work only on visible cells of filtered range

{ 12 Comments }

The VLOOKUP() function returns data from a lookup_array irrespective of the filter setting of the lookup_array.  To make the lookup_array of the VLOOKUP() function work only on the visible cells of a filtered range, refer to this workbook.

Read More →

Extract data from unknown lookup range

{ 14 Comments }

The VLOOKUP() function accepts a definite lookup array (second input in the VLOOKUP()) specified by the end-user.  In a situation where the lookup array is unknown, a function may have to cycle through various lookup ranges to cull out the required value. You may refer to my solution in this workbook.

Read More →