Suppose a worksheet name is Code_details. The following values lie in range A2:A14 of this sheet: A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123, ASDFG1234567890 and ASDFG123456789 Suppose there is another worksheet named Summary where the following values lie in range B5:B7: ASQ47809876,ASW675458 and QWERT Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet. If this exact value is not found there, then the […]
Read More →
{ 8 Comments }
Assume a two column database in range A3:B6. Headings are in A2:B2. In range A3:A6 are numbers, say 60,30,20 and 10. In range B3:B6 are some names, say Ashish, Sameer, Gautam and Sanjay. In cell A7, enter any name, say Sameer. The task is to extract the corresponding number from the range (A3:B6) into cell B7. […]
Read More →
{ 10 Comments }
Assume a matrix like data layout where Site’s are mentioned in range C2:C15 and first day of every month from January 2011 till April 2012 are mentioned in range D1:S1. In range D2:S15, the numbers represent the value of the electricity bill received in that month. Some cells can be blank representing bills not received in that […]
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 →
{ 6 Comments }
Assume a five column database with the first column containing codes, second one containing a separator, third one containing some alpha values, fourth one is a combination of the first three columns and the last column has response times. One may want to create as many charts as there are codes in column A. The charts […]
Read More →
{ 206 Comments }
Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell). Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays. You may refer to my solution in this […]
Read More →
{ 2 Comments }
Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey. To extract the most frequently occurring item, one may use the following formula =INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0))) To extract the second most frequently occurring item, try the following: 1. Enter 2 in cell C16: 2. In cell D16, enter the […]
Read More →
Assume a worksheet which has scores (1-10) on four future dates on nine questions. Each row represents responses for one question. Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet. The question and solution are more clearly explained in the attached […]
Read More →
{ 252 Comments }
Assume an MS Excel file has 4 worksheets – Sheet1, Sheet2, Sheet3 and Sheet4. Insert a sheet before Sheet1 and name that tab as Summary. On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards. Furthermore, the sheet names so generated, should be dynamic for the following changes: 1. Sheets […]
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 →
Performing an iterative lookup to return closest match
{ 5 Comments }