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

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: 3

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,147,452
Messages
5,741,209
Members
423,649
Latest member
steel1968

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
Top