Splitting Fixed Assets into multiple rows

Tuesday, September 23rd, 2014

A past participant was working on a Fixed Asset Register and one of the tasks she is required to do is to split up the Fixed Assets into multiple rows, based on the quantity. Given below is an example of what has to be done.

a) A00016 Well Management Pte Ltd 10297 AM00000000002685 4.00 APOT-0150


369.48 369.48 0.00
b) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150


92.37 92.37 0.00
c) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150


92.37 92.37 0.00
d) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150


92.37 92.37 0.00
e)A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150


92.37 92.37 0.00

Meaning I need to split item (a) of qty 4 equally into (b) to (e) of qty 1 and cost p/qty in each line.

This would have been quite straight forward if the quantity for all the Fixed Assets are the same. The problem is, they are not. Therefore, we have to use VLOOKUP with approximate match to determine the number of rows required for each line of Fixed Assets and populate them into the relevant rows. The worksheet function COlUMN is used to help us identify the column index number for the VLOOKUP formula. And in this example, I also did a quick demonstration on filling up a range to a specific number almost instantly. Watch the video below to learn the application of each function.

If you have benefitted from the video, we would love to hear from you. Let us know which part of the solution is useful to you and how it has benefitted you.



Moving worksheets with Table

Sunday, April 14th, 2013

I was trying to move a set of worksheets containing tables to a new file and a message pops up telling me that I am not able to do so. This happens to both Excel 2007 and 2010 file. So to resolve this, I guess the alternative is to re-save the file and delete the unwanted worksheets. Another method is to copy  or move the file to the new file one by one.

Excel Tips and Tricks

Friday, March 8th, 2013

Currently working on a 2 hour seminar. In the seminar, I will be covering 30 Tips and Tricks. One of the Tips and Tricks I will be covering is how to complete this challenge in 30 seconds. Please see file in this link Tips and Tricks.

Fill in the blanks in between the rows

Monday, February 11th, 2013

You downloaded a contact list containing names and email addresses from your company system. A simplified example of the list is shown below.

In the list, you discovered that some names are missing from column B. In your folder, you have a backup copy which allows you to find all the missing names. Using VLOOKUP, you can quickly place those names in a separate column. However, that is far from perfect. You would like to merge the names in column D into Column B. Most people would have done it manually. If it is just a few names, you can manually do a few copy and paste and the job would have completed in less than a minute. But if there are hundreds of them,


Find function is not simple after all

Friday, February 1st, 2013

Asked any Excel user whether he/she has used the FIND function before and you probably received a resounding yes. But ask them whether they know about the FIND ALL function and you will probably get a blank stare from them. In fact, the FIND ALL function is so under-utilised that even very experienced Excel users do not know how to use it. In my Tips and Tricks Sessions  yesterday, I did another demo and managed to record it. (more…)

A quick way to copy formula down hundreds of rows.

Friday, December 16th, 2005

If you have 2 columns with 1000 rows each (say column A and B) and you created a formula in the first row (e.g. in column C). In most cases, you would have copied the formula throughout the 1000 rows by dragging the formula down. And one problem you faced is that you would take quite a while to drag to the 1000th row. In addition, you are likely to drag beyond the 1000 rows and have to back track.

Now, there is a simpler way. It just take 2 seconds to copy the formula down 1000 rows, or even 10000 rows. What you have to do is to move your mouse cursor to the bottom right corner of the formula cell in column C until a cross appears. Then double click on your left mouse button. Excel will make reference to the cells to the left of the formula cell (Column B in this case) and copy the formula all the way down to the last row (reference to column B). And you are done.

Enjoy your weekend!