Reducing values based on value ranking

[OgliBeta]

I wasn't exactly sure what to title the thread...
I have a dataset that I basically need to replicate, except that I need to reduce the values based on the amount in the Deposit column. The deposit gets applied to the smallest values first and on up. There doesn't have to be any kind of lookup to find the correct rows because the result table will be out to the right of the original one with the exact same columns and rows.

The sample file should give a pretty good idea of what I'm after.

 

[vletm]

OgliBeta
How did You get C11 & D11 -values?
Press [ Do It ] to get almost same ...
( I copied the original data to rows 16..19 )

 

[OgliBeta]

Thanks vletm. I was traveling on vacation the past few weeks so am just getting back to this...
Your VBA achieves the exact result I was looking for. D11 was simply a manual typing error on my part.
I'm having a little issue though when I move it over to my actual workbook - the macro runs and runs (haven't had the patience to see how long it would run before stopping) and doesn't do anything. There's got to be an issue somehow in my translation of the VBA code to my actual worksheet. I've included a second tab in the sample file that shows the actual layout as it is in my working file so you can see where I might have gone wrong in my adjustment of the VBA code. The yellow highlighted area is what I want the VBA coding to work on.

 

[vletm]

OgliBeta
Is there any clear reason why there are 'some' changes in code
eg: b = 3 to ae = 26 or
.cells(b,2) > 0 to .cells(ae,31) > 0 and so on?

It would be more than miracle if that would run as original!
Do You have clear idea, how those 'changes' would effect ... or affect?

If a sample file is as close as possible with real file
then there won't need to make normally huge challenges ...
but if eg layout is even 'a little' different then
... it could be 50/60 ( hmm ... actually 1%) possibility,
how would it work (or work at all or work forever)!
Even some 'minor' differences would mean 'delete all'
and do everything again ... from zero!

Ideas? Questions? Answers?

Question: Should I see some similarity between Sheet1 and Sheet2? ... but can I?

 

[OgliBeta]

vletm, I see what you're saying... I could explain my reasoning behind the changes I made, however, though I have written a fair amount of VBA I'm not a VBA pro. So, let me just clarify the similarity between sheet2 and sheet1:

Deposit = Sheet1: column B, Sheet2: column AE
Charges = Sheet1: columns D to W, Sheet2: columns AF to AX

The charge columns need to be decreased by the deposit amount starting with the smallest charge amounts first. This is precisely how your VBA works on Sheet1. All the other data outside of columns AE to AX can be ignored.

Does that help clarify anything?

 

[vletm]

OgliBeta
oh yeah!
Have You tried to run that step-by-step?
Have You checked which cell (x) it will 'empty' after find the smallest?
The 1st value 450 (AE27) seems to empty (AB27) ... hmm?
left side from AE27 ... hmm? ... and that's it!
(AE27) never goes to zero ... it'll be always the smallest '>0'.
Where/how did You set x = 26 + Worksh... ? That 26?
Do modified code work as mine?

Still rule works ... one hour coding ... tens hours checking ... after any change ... check again ... and so on ...

 

[OgliBeta]

I hadn't noticed that it was emptying AB27. That's odd.
Changing the 26 in "x = 26 + Worksh..." to 3 or ae takes care of the the AB27 problem and clears out AE27 and AE28, but doesn't decrease/zero any of the cells out to the right...

 

[Haz]

Check the uploaded file.

EDIT: fixed for possible duplicates.

 

[vletm]

OgliBeta
I checked more ...
There were few more challenges ...
1) that 26 is not good value
2) some ranges were not correct
3) original file didn't have any zeros (-) ... challenge with min(range) always 0

I marked changes to code and took 'zeros' away ... test now.
It shows it 'step-by-step' ...

 

[OgliBeta]

@vletm and @Haz - both of these solutions are brilliant and work like a charm! Thank you so much!