Tags: ROW

Valuing Closing Stock using FIFO method of Accounting

{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 is from the oldest stock lying in inventory i.e. the inventory left over will be from the recent stock purchased.

The objective is to determine the value of the closing stock of Raw material on FIFO basis.

You may refer to my solution in the attached workbook.

Compare value of one cell with value of next visible cell of a filtered range

{0 Comments}

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 an auto filter is applied to the range.

To compare the value in the current row with the value in the next visible row below, refer to my solution in this workbook.

Count word combinations in individual columns of a multi column database

{6 Comments}

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 is - "To display in the blank cells of the destination grid, total count of row and column heading combinations in each column of the source grid.".  So the counting of row and column heading combinations has to happen in each individual column of the source grid and then all these numbers should be summed up.

The source grid, expected result and formula in individual cells of the destination grid haven been shown in this workbook.

Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook

{8 Comments}

Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions - .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start from cell B3 (first header cell), there can be a variable number of rows and columns from cell B3 onwards.

The task is to consolidate data from sheet1 of all Excel files to multiple worksheets of one Excel file.

The Question and Instructions have been clearly explained in the "Question and Instructions" worksheets of this file.

To see the solution work,

1. Save the following zipped folder on our desktop.  Unzip the folder and place Data Files folder (with all its files) on your desktop; and
2. Change the path in cell B2 of worksheet "Query and file listing" of Destination.xlsx file to the path of your folder saved in step 1 above.
3. Click on the blue button on worksheet "Query and file listing" of Destination.xlsx file.

Sum up diagonal cells in a range

{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 values from cells B6, C7 and D8 and so on.

You may refer to my solution in this workbook.

Performing an iterative lookup to return closest match

{5 Comments}

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 lookup value should be trimmed by one digit/character from the extreme right i.e. the lookup value should now become ASQ4780987.  The search should carry on till an exact match is found.

You may refer to my solution in this workbook.

Analysing customer walkin data by date and service taken

{5 Comments}

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data:

1. For every date, total number of customer walkins and total number of services taken; and
2. For every date, new customer walkins and new services taken; and
3. For every date, repeat customer walkins and repeat services taken

While the first and third questions are fairly straight forward to solve, some deliberation would be required for the second question.  A new service type taken on June 3, 2012 would be one that has not been taken by any customer from June 1 - 2, 2012.  So if cell A8 has Service type E and cell D8 (data for June 3, 2012) has 3 (3 customer took service type E on June 3, 2012), then this service should be counted only if there is no figure in range B8:C8 i.e. no customer took this service on June 1 - June 2, 2012.

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 and the PowerPivot add-in, then a few simple steps and minimal DAX formulas can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in the this workbook.

Compute rent payable over the contact period after factoring in escalations

{0 Comments}

Assume the following data on a worksheet:

1. Cell B3 - Tenure of loan (in years).  This will be a whole number, say 15
2. Cell B5 - Escalation (in percentage), say 10%
3. Cell B6 - Escalation period (in months), This will be a whole number, say 24.  This means that the escalation will happen after every 24 months
4. Cell B7 - Rent per month, say 15,000

In cell B9, one may want to know the total rent payable over the tenure of the contact after factoring in the escalation clause.

You may refer to my solution in this workbook.

Generate a list of all Excel files from a specific folder without using VBA

{56 Comments}

Assume a folder named Test on the desktop.  In this Test folder, there is another folder named Survey reports.  In the Survey reports folder, there are Word documents, PowerPoint files, Excel files, PDF files and all sort of other files types.

In a new workbook, one may want to list down all Excel files from the Survey reports folder (saved on the desktop) without using VBA.  Furthermore, for new Excel files added to the folder, the list should keep updating.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In Power Query, there is a feature to append data from multiple workbooks into one running range.  The detailed process to do so is described below:

1. Go to Power Query > From File > From Folder
2. Choose the Test folder on the desktop and click on OK
3. Filter the extension column on "Contains" .xls
4. Right click on the Name column heading and choose "Remove Other columns"
5. Click on Apply and Close

This should now list down all MS Excel files lying the Test folder.  This is a dynamic list i.e. add or remove Excel files from the folder and just right click on any cell and select Refresh.

Solution for all versions of MS Excel

Try the following procedure to get a list of all Excel files from the folder:

1. In the new workbook, press Ctrl+F3 > New.  In the name box, type FL
2. In the Refer to box, enter =FILES("C:\Users\Ashish\Desktop\Test\Survey Reports\*.xls")&T(NOW()).
3. Click on OK and Close

In cell A3 of sheet1, enter the following formula and copy down

=IF(ISERROR(INDEX(FL,ROW()-2)),"",INDEX(FL,ROW()-2))

If you add another Excel file to the Survey report folder, just press F9 anywhere on sheet1 and the file will appear in the list.

Change C:\Users\Ashish\Desktop\Test\Survey Reports\ to your actual path from where you want to extract the Excel files.

To generate a list of all tab names from in an Excel file, you may refer to the following post.