EXCEL VBA or Formula: Split sets £ enteries based on criteria amount or close to it

E

emz07

New Member
Joined
Oct 28, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello all,

I'm not sure is this is possible, everything I have looked at seems like it needs to be exact amount.

I'm hoping someone can help. Please see image attached.

In column B is the list of amounts I want to seperate into C D E F G columns. The total of £189,733.00 is divide by K6 (=SUM(C6:G6)/5) £37,946.60,

On row 6 is the amounts that is closest to it.

The list on the B column cannot be split evenly between the days. I need a formula that fill C:G based on the amounts on B.

For example: Supplier Hozelock gets paid on 27th of Sept, because all the other suppliers on prior, have already filled the criteria of about £37k which you can see on C6. The amounts on C:G can just be values. My aim is to not touch B (and hide it when printing). and from it copy and paste or just automatically fill in the next column along. If you see E51:E58 adds up to £39,704.83, then from LLOYD & Jones onwards ,it has moved to F59 ....etc... and so on.

Apologies in advance about the uber long formula in column B if someone can shorten it that would be great but not the priority. HAHA.

If you anyone needs more explanation. Let me know.


Bill Payments 20.09.2021 1.xlsm
ABCDEFGHIJK
1Bill Payments List22 September 2021
2
3
4Supplier22.09.202124.09.202127.09.202128.09.202129.09.202130.09.2021Paid On ExchequerOn ExchequerNotes
5
6£39,247.00£36,052.65£39,704.83£40,296.11£34,432.41Total:£189,733.00£189,733.0037,946.60
46HOZ001, Hozelock Limited2025.242025.2427.09.2021x
47IND001, Industrial Ancillaries Ltd462.20462.20 
48INT007, Intrico Products Ltd14.7014.70 
49JEN001, Jenkinsons Office Supplies401.03401.03 
50JOH003, John Roberts Garage214.85214.85 
51JSP001, JSP Limited3672.003672.00 
52KEE001, Kee Connections Ltd13126.3713126.37 
53KHP001, K.H Packaging793.80793.80 
54KOB001, Kobold Instruments Limited15840.0015840.00 
55LED001, LEDCO Limited1198.411198.41 
56LEEN01, Leengate Valves339.09339.09 
57LIN002, Link Engineering & Welding334.80334.80 
58LIN004, Link Powder Coating & Metal Finishing4400.364400.36 
59LLO001, Lloyd & Jones Engineers Limited143.40143.4029.09.2021x
60LYA001, Lyan Packaging Supplies Ltd1028.701028.70 
BP
Cell Formulas
RangeFormula
B4B4=K1
C6:G6D6=SUM(D7:D208)
J6J6=I6
K6K6=I6/5
D46:D50D46=SUM(IF(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(A46,'Outstanding Amounts'!K:K,0),8)="Add Invoice",SUM((INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A46,'Outstanding Amounts'!$K$1:$K$113,0),2)+(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A46,'Outstanding Amounts'!$K$1:$K$113,0),9)))*$L$1),SUM((IF(AND(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A46,'Supplier List'!G:G,0),6)="60 Days",A46="WAL001, Walter Frank & Sons Limited",'Outstanding Amounts'!Q54="Match"),INDEX('Outstanding Amounts'!$N$109:$S$123,MATCH(A46,'Outstanding Amounts'!$O$109:$O$123,0),5),IF(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A46,'Supplier List'!G:G,0),6)="60 Days",INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),3),IF(INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),2)=INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),6),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),2),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),6)))))*$L$1)))
E51:E58E51=SUM(IF(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(A51,'Outstanding Amounts'!K:K,0),8)="Add Invoice",SUM((INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A51,'Outstanding Amounts'!$K$1:$K$113,0),2)+(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A51,'Outstanding Amounts'!$K$1:$K$113,0),9)))*$L$1),SUM((IF(AND(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A51,'Supplier List'!G:G,0),6)="60 Days",A51="WAL001, Walter Frank & Sons Limited",'Outstanding Amounts'!Q59="Match"),INDEX('Outstanding Amounts'!$N$109:$S$123,MATCH(A51,'Outstanding Amounts'!$O$109:$O$123,0),5),IF(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A51,'Supplier List'!G:G,0),6)="60 Days",INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A51,'Outstanding Amounts'!K:K,0),3),IF(INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A51,'Outstanding Amounts'!K:K,0),2)=INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A51,'Outstanding Amounts'!K:K,0),6),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A51,'Outstanding Amounts'!K:K,0),2),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A51,'Outstanding Amounts'!K:K,0),6)))))*$L$1)))
B46:B60B46=IFNA(SUM(IF(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(A46,'Outstanding Amounts'!K:K,0),8)="Add Invoice",SUM((INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A46,'Outstanding Amounts'!$K$1:$K$113,0),2)+(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A46,'Outstanding Amounts'!$K$1:$K$113,0),9)))*$L$1),SUM((IF(AND(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A46,'Supplier List'!G:G,0),6)="60 Days",A46="WAL001, Walter Frank & Sons Limited",'Outstanding Amounts'!Q54="Match"),INDEX('Outstanding Amounts'!$N$109:$S$123,MATCH(A46,'Outstanding Amounts'!$O$109:$O$123,0),5),IF(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A46,'Supplier List'!G:G,0),6)="60 Days",INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),3),IF(INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),2)=INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),6),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),2),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A46,'Outstanding Amounts'!K:K,0),6)))))*$L$1)))," ")
F59:F60F59=SUM(IF(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(A59,'Outstanding Amounts'!K:K,0),8)="Add Invoice",SUM((INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A59,'Outstanding Amounts'!$K$1:$K$113,0),2)+(INDEX('Outstanding Amounts'!$E$1:$M$113,MATCH(BP!A59,'Outstanding Amounts'!$K$1:$K$113,0),9)))*$L$1),SUM((IF(AND(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A59,'Supplier List'!G:G,0),6)="60 Days",A59="WAL001, Walter Frank & Sons Limited",'Outstanding Amounts'!Q67="Match"),INDEX('Outstanding Amounts'!$N$109:$S$123,MATCH(A59,'Outstanding Amounts'!$O$109:$O$123,0),5),IF(INDEX('Supplier List'!$A$1:$G$1360,MATCH(A59,'Supplier List'!G:G,0),6)="60 Days",INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A59,'Outstanding Amounts'!K:K,0),3),IF(INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A59,'Outstanding Amounts'!K:K,0),2)=INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A59,'Outstanding Amounts'!K:K,0),6),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A59,'Outstanding Amounts'!K:K,0),2),INDEX('Outstanding Amounts'!$E$1:$K$113,MATCH(A59,'Outstanding Amounts'!K:K,0),6)))))*$L$1)))
I6I6=SUM(B6:H6)
I46:I60I46=IF(AND(IF(C46>0,$C$4,IF(D46>0,$D$4,IF(E46>0,$E$4,IF(F46>0,$F$4,IF(G46>0,$G$4," "))))),J46="X"),(IF(C46>0,$C$4,IF(D46>0,$D$4,IF(E46>0,$E$4,IF(F46>0,$F$4,IF(G46>0,$G$4," ")))))),"")
Named Ranges
NameRefers ToCells
'Supplier List'!_FilterDatabase='Supplier List'!$A$1:$F$1360B46:B60, F59:F60, E51:E58, D46:D50
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J4Expression=AND(LEN(TRIM(J4))>0,$G$4=$I4)textNO
J4Expression=AND(LEN(TRIM(J4))>0,$F$4=$I4)textNO
J4Expression=AND(LEN(TRIM(J4))>0,$E$4=$I4)textNO
J4Expression=AND(LEN(TRIM(J4))>0,$D$4=$I4)textNO
J4Expression=AND(LEN(TRIM(J4))>0,$B$4=$I4)textNO
A:KExpression=AND(LEN(TRIM(A1))>0,$G$4=$I1)textNO
A:KExpression=AND(LEN(TRIM(A1))>0,$F$4=$I1)textNO
A:KExpression=AND(LEN(TRIM(A1))>0,$E$4=$I1)textNO
A:KExpression=AND(LEN(TRIM(A1))>0,$D$4=$I1)textNO
A:KExpression=AND(LEN(TRIM(A1))>0,$C$4=$I1)textNO
 

Attachments

  • Bill payment.JPG
    Bill payment.JPG
    248.7 KB · Views: 10

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You must log in or register to reply here.

Similar threads

k3yn0t3
  • Solved
Simple Macro to Dupe Sheet and Update Cell Value by +1 [feat. xl2bb mini-sheet]
Replies
8
Views
292
k3yn0t3
k3yn0t3
G
  • Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
208
glaccounting
G
k3yn0t3
  • Question
Simple Macro to Copy/Paste Cells onto Another Sheet
Replies
2
Views
209
kevin9999
K
B
  • Question
Sumifs based on criteria in line with value to be summed
Replies
9
Views
544
Peter_SSs
Peter_SSs
J
  • Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
591
jennifer7i884
J

Forum statistics

Threads
1,214,588
Messages
6,120,409
Members
448,959
Latest member
camelliaCase

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top