Data in a two column database (A3:B13) can be Auto filtered on column A with one or many conditions. Data in column B will be numbers only (positive, negative or 0’s) – no text values. After filtering data in column A, one may want to extract the unique numbers from column B to cell A18 (and then […]
Read More →
{ 60 Comments }
Assume a two column database of approximately 20,000 rows (say from A1:B20237). The first column has vendor names and the second column has Part codes (alphanumeric string). Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1. One vendor supplies multiple parts and therefore there would be repetitions of vendor names […]
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 →
{ 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 →
{ 2 Comments }
In range A7:A15, assume the following values A, S , empty cell, empty cell, R, A, W, A, SD. Starting from cell B17, transpose data to the right (ignoring the blank cells). Therefore, in range B17:F17, the result should be A, S, R, W SD. Solution for MS Excel 2010 and higher versions If you […]
Read More →
Assume a two column database in range C4:D11. In cell C3, type “Cheque No.” (without double quotes) and in cell D3, type “As per Bank Account” (without double quotes). Entries in range C4:C11 are cheque numbers and in range D4:D11 are Dollar values. Let’s refer to this database as “The Bank Dump”. Assume another two column […]
Read More →
Conditionally formatting duplicate values across all versions of MS Excel is quite simple. However, to colour different duplicate values in different colours, refer to this workbook.
Read More →
{ 8 Comments }
Assume there are two columns with data repeated in all both columns. One may wan to extract the unique text values from a specific column based on conditions specified by end-user. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions […]
Read More →
{ 4 Comments }
Given a single column with duplicates appearing, one may want to dynamically extract unique values to another range. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions If you are using the Power Query add-in, then a […]
Read More →
{ 13 Comments }
There is a three column database with month in column A, numbers in column B and names in column C. I want to count unique numbers in column B for a specific month and name provided by the end user. Depending upon the version of MS Excel which you are using, there could be two […]
Read More →
Dynamically extract unique values from a filtered range
{ 0 Comments }