In range G13:G17, assume the following entries – VG:25, NS:50, NS:20, #DIV/0! and VG:150. To sum up the values where VG appears, you may refer to my solution in this workbook.
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 →
Dynamically transpose data after ignoring blank cells
{ 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 →
Extract number from an alphanumeric string
{ 72 Comments }
Given an alphanumeric string, one may want to perform the following Extract phone number from the string Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself. These numbers may be mobile numbers and/or mobile numbers. Furthermore, PIN codes may also be mentioned in the address string. One may want […]
Read More →
A user inputs raw material purchase data in a master sheet. This data is entered by date and vendor. One may want to view individual vendor sheets at a monthly level. In the attached file, i have presented two solutions – a macro based one and a formula based one. The data layout and instructions […]
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 →
Hi, Assume the following alphanumeric strings in range A3:A6 A-2222 Mohan 12-4 #$% Singh$%&*** **** Sanchit #$% Pal$%&*** $^%$^$ Sudhir #$% Si$$$ngh$%&*** #@234 Krishan #$% Oman$%&*** In range B3:B6, one may want to extract only the text values i.e. drop special characters and numbers from the values in range A3:A6. In range B3:B6, the result […]
Read More →
Sum visible cells of a filtered range ignoring errors
{ 4 Comments }
In a multi column database, assume a filter has been applied on some columns. Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns. The objective is to sum numbers in a numeric column. The usual SUM() function would not work because the range of sum function should be error free. Furthmore, […]
Read More →
Compute revenue with progressive discounting
{ 37 Comments }
Assume a two column database with airline names in column A and number of passenger seats in column B, one may want to know the total revenue. Given a full rate of US$ 12.0 per seat, discounts are offered on this full rate depending upon the passenger seats booked. So for example, for the first 500,000 […]
Read More →
Extract uncoloured cells per article to another worksheet
{ 0 Comments }
Given a database where only some waybill numbers per client are coloured, one may want to query the database to fetch to another worksheet only those waybill numbers which are uncoloured. You may refer to my solution in this workbook.
Read More →
Sum a range of alphanumeric entries ignoring errors
{ 4 Comments }