Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all. In this workbook, I have shown the technique to count unique values with conditions on a large database 1. Using PowerPivot – Will only work in […]
Read More →
{ 0 Comments }
A number is entered in cell A1 of every sheet in a multi sheet workbook. The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]
Read More →
{ 0 Comments }
Assume a “Labour utilisation” report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names). Each employee can perform multiple tasks on the same day and same task on multiple days. Given a list of all employees, one may want to answer […]
Read More →
{ 12 Comments }
Assume a three column table arranged as follows: Circle, Date of Fault and ID. Dates in the date range span one week – November 26, 2012 to December 2, 2012. A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week. Data for one week […]
Read More →
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 →
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 →
{ 0 Comments }
Assume there are numbers typed in range B3:G8. In row 10, one may want to sum up diagonal cells i.e. in cell B10, one would want to get the value from cell B8, in cell C10, one may want to sum values from cells B7 and C8. in cell D10, one may want to sum […]
Read More →
Assume a column of data with blanks appearing in a few random cells. One may want to use this range of cells for creating a Validation drop down in a specified cell. After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]
Read More →
Having created a graph from a certain source data, if columns get added later to the source, they do not automatically append to the graph. While this is easy enough to accomplish in Excel 2007 and higher versions, doing the same in Excel 2003 is a different procedure altogether. Instructions are very clearly mentioned in […]
Read More →
{ 2 Comments }
One may want to create interactive graphs by allowing users to select indicators and years to be plotted (via check boxes and scroll bars). I have presented three cases in this workbook.
Read More →
Count unique values with conditions on large databases
{ 8 Comments }