Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?
Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?
Are you sure?
I change my example (in post 16#) a bit in the second & third section.
The result is the second & third section are marked with X, but the rest is still mark with WPI & blank.
Excel 2013 32 bit
A B C D E F G H I J K L M 1Q Q Job No. Q Q Q Q Q Q Q Balance in Base Manual 2AA 101 1WPI 3AA 101 1WPI 4AA 101 -3 5AA 101 2 6AA 101 -1 7AA 101 1WPI 8AA 101 2 3 9AA 102 -3X 10AA 102 1X 11AA 102 10X 12AA 102 1X 13AA 102 -1X 14AA 102 -1X 15AA 102 2X 9 16AD 302 3X 17AD 302 1X 18AD 302 10X 19AD 302 1X 20AD 302 -5X 21AD 302 1X 11 22AD 501 2 23AD 501 1 24AD 501 -1WPI 25AD 501 1 26AD 501 -2WPI 27AD 501 3 28AD 501 1 29AD 501 -1WPI 30AD 501 -3 31AD 501 -5 -4
Sheet: Sheet4
I’m not sure but try something like this:
Using my second code, after running the code, move all section with X mark to another sheet, then sort the data by col K ascending (off course sort by Job No still & always the first criteria), run the code again, repeat the step to move all section with X mark then try again sort the data by col K descending.
Yes, Surprisingly.
K 3 -2,26,800.00 4 -2,26,800.00 Sheet5
Worksheet Formulas
Cell Formula A1 Company No. B1 Company Code C1 Job No. D1 Account No. E1 Trans. No. F1 Journal No. G1 Date Posted H1 Entry Date I1 Debits J1 Credits K1 Balance in Base L1 ManualNewVBA M1 SUMIFNewVBA N1 ManualOldVBA O1 SUMIFOldVBA A2 B2 C2 8111789 D2 E2 F2 G2 H2 I2 J2 K2 -291819 L2 X M2 N2 CONTRA O2 A3 B3 C3 8111789 D3 E3 F3 G3 H3 I3 J3 K3 -226800 L3 X M3 N3 CONTRA O3 A4 B4 C4 8111789 D4 E4 F4 G4 H4 I4 J4 K4 -226800 L4 X M4 N4 CONTRA O4 A5 B5 C5 8111789 D5 E5 F5 G5 H5 I5 J5 K5 -226800 L5 X M5 N5 CONTRA O5 A6 B6 C6 8111789 D6 E6 F6 G6 H6 I6 J6 K6 -226800 L6 X M6 N6 CONTRA O6 A7 B7 C7 8111789 D7 E7 F7 G7 H7 I7 J7 K7 -226800 L7 X M7 N7 CONTRA O7 A8 B8 C8 8111789 D8 E8 F8 G8 H8 I8 J8 K8 -226800 L8 X M8 N8 CONTRA O8 A9 B9 C9 8111789 D9 E9 F9 G9 H9 I9 J9 K9 -226800 L9 X M9 N9 CONTRA O9 A10 B10 C10 8111789 D10 E10 F10 G10 H10 I10 J10 K10 -226800 L10 X M10 N10 CONTRA O10 A11 B11 C11 8111789 D11 E11 F11 G11 H11 I11 J11 K11 226800 L11 X M11 N11 CONTRA O11 A12 B12 C12 8111789 D12 E12 F12 G12 H12 I12 J12 K12 226800 L12 X M12 N12 CONTRA O12 A13 B13 C13 8111789 D13 E13 F13 G13 H13 I13 J13 K13 226800 L13 X M13 N13 CONTRA O13 A14 B14 C14 8111789 D14 E14 F14 G14 H14 I14 J14 K14 226800 L14 X M14 N14 CONTRA O14 A15 B15 C15 8111789 D15 E15 F15 G15 H15 I15 J15 K15 226800 L15 X M15 N15 CONTRA O15 A16 B16 C16 8111789 D16 E16 F16 G16 H16 I16 J16 K16 226800 L16 X M16 N16 CONTRA O16 A17 B17 C17 8111789 D17 E17 F17 G17 H17 I17 J17 K17 226800 L17 X M17 N17 CONTRA O17 A18 B18 C18 8111789 D18 E18 F18 G18 H18 I18 J18 K18 226800 L18 X M18 N18 CONTRA O18 A19 B19 C19 8111789 D19 E19 F19 G19 H19 I19 J19 K19 291818 L19 X M19 -1 N19 CONTRA O19 -1
Not sure why that happened.
Then try this, it combine the first & second code.
The X mark will be placed in col N.
Code:Sub a1086996c() 'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html Dim i As Long, j As Long, n As Long Dim x As Long, k As Long, z As Long Dim q As Long Dim va, vb, vc Dim flag As Boolean Application.ScreenUpdating = False n = Range("C" & Rows.count).End(xlUp).Row va = Range("C1:C" & n) vb = Range("K1:K" & n) ReDim vc(1 To n, 1 To 3) Range("L1:N" & n).ClearContents For i = 2 To UBound(va, 1) j = i: x = 0 Do x = x + vb(i, 1) i = i + 1 If i > UBound(va, 1) Then Exit Do Loop While va(i, 1) = va(i - 1, 1) i = i - 1 vc(i, 2) = x If x = 0 Then GoTo skip: If x < 0 Then For k = j To i vb(k, 1) = vb(k, 1) * -1 Next x = x * -1 End If For k = j To i z = 0 For q = j To k z = z + vb(q, 1) Next flag = False If z = x Then vc(k, 1) = 1: vc(k, 1) = "WPI": flag = True: GoTo skip: ElseIf z > x Then vb(k, 1) = 0 Else If vb(k, 1) <= 0 Then vb(k, 1) = 0 Else vc(k, 1) = "WPI" End If End If Next skip: If flag = False Then For k = j To i vc(k, 3) = "X" Next End If Next Range("L1").Resize(UBound(vc, 1), 3) = vc Range("L1") = "Manual" Application.ScreenUpdating = True End Sub
Not trying to disrespect, is there any logic that we are missing in identifying transactions that sum to sumif of job number? At few places it did work as per 1st code.
Could you upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive)?
Then put the link here. Just the data from col B & K would be enough.
I don't understand what you mean.Not trying to disrespect, is there any logic that we are missing in identifying transactions that sum to sumif of job number? At few places it did work as per 1st code.
Here you go:-
https://drive.google.com/open?id=1vd...gSH9xbp233r9rN
I have added desired result in column O for reference. This was plotted manually for remaining transaction where total by Job (SumIF) did not return 0.
Let me know in case you need any inputs.
Like this thread? Share it with others