Problem extracting data to another worksheet

**Spellbound** · 08-02-2008, 01:58 PM

Hi everyone

Looking for some help again, having spent the last few hours on this problem without success.

To simplify matters, I have attached an example of what I am trying to do using the two worksheets from a much larger workbook.

The information contained in the MAIN DATA worksheet is shown as values only but is actually the result of many extensive formulas plus a pivot table.

The OUTPUT worksheet shows how I would like the data to appear using the data taken from the MAIN DATA worksheet.

In essence I am looking to transfer the information for a particular loan and customer to the OUTPUT worksheet in a vertical format.

I have managed to make it work artifically but not in the way shown in my example, so that the particular loan starts from the correct date of entry and then continues to list itself until it reaches the current weeks entry.

Not sure how clear I have explained this but hopefully someone will know how best to make this work.

Thanks in advance ...Spellbound

**rylo** · 08-02-2008, 09:47 PM

Hi

1) the data seems to be coming from the row where the week number is >0. Please confirm
2) the data in the output doesn't seem to match the relevant week - it seems to be from the previous week. Again, can you please confirm that this is the right output data for the given input.

rylo

**Spellbound** · 08-04-2008, 09:23 AM

Hi Rylo

Not quite sure that I follow your queries.

Will try to explain the example as follows:

Firstly, I run each workbook on a yearly basis starting from week 1 running thru to week 52 or 53 in some cases.

In the MAIN DATA worksheet, the information to the left of column J is static and as a customer takes on a new loan then this is added accordingly. In the example, loan 672 was inputted week ending 15/03/08 which reflects an opening balance for that week to allow for subsequent payments etc to be deducted from week 12 onwards i.e. 22/03/08.

To confuse matters the company that I work with class the 1st week of payment as week zero not week 1, which may be causing the visual confusion. In other words in terms of payment history; week 12 is week 0 and week 13 is week 1 in this example.

Hope this clarifies the situation better.

Spellbound

PS: Just discovered that there was a missing payment in the original example; so I have corrected that in the attached example ...apologies for that.

**rylo** · 08-04-2008, 06:36 PM

Hi

Have a look at the numbers in columns I:K of the attached. Does this return the correct numbers? They are slightly different from your example results.

rylo

**Spellbound** · 08-05-2008, 08:24 AM

Hi Rylo

Have been playing around with your solution but without success.

Firstly I need to avoid the use of helper cells etc, as the results of this worksheet will need to be printed off and I do need the results to be as shown.

I have attached one of my revised test workbooks, which includes additional data on the MAIN DATA worksheet; which may assist better. The MATCH on just the loan number would be prone to error, so you will see that I have used an INDEX / MATCH array to ensure the correct data is elected.

Whilst I use the formulas in the example to find the first week of the loan; the problems occur as to when the autofill of the cells should stop.

The two main instances are when the current week number is exceeded and when the loan balance is zero. At the moment, my formulas produce error messages in some situations or just carry on beyond the current week.

Currently my workbook is about 30mb and it seems to take ages just to save the data, so I also felt that my method was probably not the cleanest solution and just hoped that there might be a more efficient solution to this problem.

Hope this additional information is useful ....spellbound

**rylo** · 08-05-2008, 06:48 PM

Hi

1) The helper column can be removed in the final solution - it was just there to bring back numbers to check as I didn't get the same results
2) Why is the load number prone to error? From the sample given, it seems to be unique. Why is this number a problem?
3) Cancelling the autofill output and stopping when the output is 0 shouldn't be a problem (famous last words...) so I'll worry about them when bringing back the correct data is resolved.

rylo