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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You must log in or register to reply here.

Similar threads

J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
258
Joe4
Joe4
k3yn0t3
  • Solved
Simple Macro to Dupe Sheet and Update Cell Value by +1 [feat. xl2bb mini-sheet]
Replies
8
Views
538
k3yn0t3
k3yn0t3
J
  • Question Question
conditional formatting based on adjacent cells matching
Replies
1
Views
281
etaf
etaf
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
G
  • Question 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
570
glaccounting
G

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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