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

#### emz07

##### New Member
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
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
248.7 KB · Views: 3

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Replies
0
Views
199
Replies
0
Views
152
Replies
0
Views
445
Replies
0
Views
124
Replies
13
Views
384

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

### 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.

### Which adblocker are you using?

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

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