Splitting Fixed Assets into multiple rows

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

19-Dec-01

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

19-Dec-01

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

19-Dec-01

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

19-Dec-01

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

19-Dec-01

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.

 

 

Fill in the blanks in between the rows

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,

Continue reading "Fill in the blanks in between the rows"

Find function is not simple after all

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. Continue reading "Find function is not simple after all"

A quick way to copy formula down hundreds of rows.

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!