When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell. Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell. Furthermore, when splitting data into multiple cells, it should […]
Read More →
{ 24 Comments }
Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices. On another sheet, the closing stock of each Raw material type is entered. In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued […]
Read More →
Assume data in range B4:B18 (heading is in cell B3). To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down =B4=B5 True’s indicate matching values with the value in the next row. However, the above formula would fail when […]
Read More →
{ 0 Comments }
Assume data in range A3:D8 (headings are in A3:D3). One may apply an auto filter on any column(s). The task is to count the blank cells in visible rows of range C4:C8 and D4:D8. You may refer to the data set and my solution in this workbook.
Read More →
Assume two workbooks named Book1.xls and Book2.xls. In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb. In both the worksheets – Jan and Feb, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of […]
Read More →
{ 328 Comments }
Assume a workbook with two worksheets – Jan and Feb. In both the worksheets, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of headings on both worksheets As you can observe in this workbook, all three aspects mentioned above […]
Read More →
Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]
Read More →
Assume the following dates in range B4:B8 B4 – 29/09/2011 B5 – 04/10/2012 B6 – 05/10/2012 B7 – 07/10/2014 B8 – 15/07/2015 One may want to count dates which are within 365 days of each other. The answer should be four. The four dates are: 1. 04/10/2012 2. 05/10/2012 3. 07/10/2014 4. 15/07/2015 The formula […]
Read More →
{ 0 Comments }
In range B3:V3, one will record Purchase price of various Assets purchased in different months. In the next two rows one specifies whether the Assets purchased in that month have to be amortised or not and the months over which the Assets has to be amortised respectively. The task is to determine the amount to […]
Read More →
Assume a database of five rows and four columns. Each cell of the database has some text value – let’s call this the source grid. In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings – let’s call this the destination grid. The task […]
Read More →
Extract specific number of characters from an alphanumeric string without breaking any word
{ 4 Comments }