Distribute Receipt# using FIFo Method.

[Haider Ali]

Dear fellow, Please help to distribute receipt# against invoice value based on the Advance amount. A sample file is attached for better understanding.

 

[p45cal]

See formula in cell G6.
If you're using Excel 2013 (which I think you are), you need to select the whole range G6:G98 while committing the formula to the sheet using Ctrl+Shift+Enter rather than plain Enter.
Macros need to be enabled.
It sould be FIFO as long as the dates increase top to bottom.
What version of Excel are you using?

 

[Haider Ali]

See formula in cell G6.
If you're using Excel 2013 (which I think you are), you need to select the whole range G6:G98 while committing the formula to the sheet using Ctrl+Shift+Enter rather than plain Enter.
Macros need to be enabled.
It sould be FIFO as long as the dates increase top to bottom.
What version of Excel are you using?

Excel Version 2013

 

[Haider Ali]

See formula in cell G6.
If you're using Excel 2013 (which I think you are), you need to select the whole range G6:G98 while committing the formula to the sheet using Ctrl+Shift+Enter rather than plain Enter.
Macros need to be enabled.
It sould be FIFO as long as the dates increase top to bottom.
What version of Excel are you using?

Thank you Very Much, Sir. Work perfectly.

 

[Haider Ali]

See formula in cell G6.
If you're using Excel 2013 (which I think you are), you need to select the whole range G6:G98 while committing the formula to the sheet using Ctrl+Shift+Enter rather than plain Enter.
Macros need to be enabled.
It sould be FIFO as long as the dates increase top to bottom.
What version of Excel are you using?

Dear Sir, If there is more than one customer and the receipt# distribution should be distributed on the basis of the customer name. means carry forward amount receipt no should be distributed against that one customer using FIFO.
If it is possible then please help. Thanks

 

[p45cal]

This would take too much time.
Suggest sorting your data by customer name then secondarily by date then putting separate formulae for each company.
See cells F6:F17 and F18:F36 in the attached.

 

[Haider Ali]

This would take too much time.
Suggest sorting your data by customer name then secondarily by date then putting separate formulae for each company.
See cells F6:F17 and F18:F36 in the attached.

appreciatable. Thanks once again

 

[Haider Ali]

This would take too much time.
Suggest sorting your data by customer name then secondarily by date then putting separate formulae for each company.
See cells F6:F17 and F18:F36 in the attached.

Sir, as you helped me for distributing Receipt No. Now Please help me for distributing the Date of that Receipt no. in the very next column. The file is attached to understand what help I needed. thanks

 

[anishms]

Sir, as you helped me for distributing Receipt No. Now Please help me for distributing the Date of that Receipt no. in the very next column. The file is attached to understand what help I needed. thanks

Please find attached the file with UDF for populating the dates

@p45cal - I was just trying to understand the codes but the array codes are still far for me, still I was able to change the references to populate the dates as required by Haider
Thanks

 

[p45cal]

for distributing the Date of that Receipt no. in the very next column

See attached, same method of committing the formula to the sheet but apply the formula to 2 columns at once instead of one. Note that the range that the formula refers to has expanded to include 5 columns.

 

[p45cal]

still I was able to change the references to populate the dates as required by Haider

That's absolutely fine!
Odd that you chose:
ReDim RecpDts(1 To UBound(AllVals), 3 To 3)
instead of:
ReDim RecpDts(1 To UBound(AllVals), 1 To 1)
but they're both 1 column arrays so that's fine!

 

[Haider Ali]

@p45cal
@anishms
Thank you very much. Knowledge is the real power. you make our work life so much easy. May God always bless you with the utmost happiness and prosperity.