Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching records and posting them separately.
I have data ,columnwise, as follows:
A: Date, B: ItemName, C:Purchase or Sale, D:Qty, E:Per Unit Rate, F:Value ( Rate* Qty). Using FIFO (First-in-First-Out) method I want to match Sales against corresponding ( item wise) purchases. A simple sort could have solved the problem. But,it is not enough for the following reasons: i) Purchases and sales may not always match, as quantity purchased and qty sold could differ ( sold will be lower). For the quantity sold, I want to arrive at profit or loss and carry forward the balance for comparison with future sales. ii) Carried forward balances should remain distinct for matching with sales of equal qty in future. If fresh purchases were there, they should be kept separately as for profit or loss I do not want to mix one lot of purchase with the other. iii) Against Sales, which should be the basis for matching, more than one lot of purchase could be matched ( but shown separately in one row below the other), but if the last lot of purchase would take the total of purchase to more than the quantity of sales, only the difference should be shown and the balance should be carried forward. If data on one sheet and results based on the above are shown on another sheet, it will be better. I do not want to cut or remove any of the data from the original database. I tried several formulae, but failed to solve it. Doing this manually after sorting the data itemwise, purchase, sale wise and then manually comparing and arriving at the difference is very laborious. Is there a short cut ? What kind of functions or programme I can use. Can any one help ? I shall be very thankful for the help or even an attempt to help. -- esbee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching records | Excel Discussion (Misc queries) | |||
Adding data to matching records... | Excel Worksheet Functions | |||
Unique records of matching pairs | Excel Worksheet Functions | |||
Find Matching Records in Two Worksheets | Excel Discussion (Misc queries) | |||
Find matching records in two worksheets | Excel Discussion (Misc queries) |