Need help in coding

taruna1986 · Dec 18, 2023

Hi Admin,

I have the attached data and I'm trying to find the correct macro coding for creating my journal. I want if the account number & company code subtotal are zero then the macro would display the account number, profit center, and company code with value in other columns for a journal. Please advise.

taruna1986 · Dec 20, 2023

Ezguy4u said:
taruna let's keep the ball rolling, the question I have is, below should be your input and output. Now I believe by your description of what you want, shouldn't company code 300 account number 1110100031 should equal 0 but it's not in your final result. Is this by design or did you miss it? Anyway your problem is now in a usable form so we may get some more help. In the mean time I will try and come up with a macro.

Thank you so much for reviewing the issue@Ezguy4u The original data contains line items 13 and 14 as well for company code 300 account number 1110100031. These line items are preventing the subtotal of the account number and company code from being zero, which is why they are not appearing in the final results.

company code 300 account number 1110100031

Ezguy4u said:
1703022137069.xlsx
A B C D E F G H I
1 Before: Original Data After: Final results
2 Company code Account Number Amount Profit Center Company code Account Number Amount Profit Center
3 100 1110100011 10,000 3000/P1200001 100 1110100011 10,000 3000/P1200001
4 100 1110100011 -10,000 3000/P6300001 100 1110100011 -10,000 3000/P6300001
5 100 1110100011 0 3000/P6700001 100 1110100011 0 3000/P6700001
6 100 1110100031 3,857 3000/P4050001 300 1110100011 6,000 3000/P4200001
7 100 1110100031 4,000 3000/P5000001 300 1110100011 -6,000 3000/P6600001
8 100 1110100031 3,800 3000/P5000023 200 1110100031 6,000 3000/P4150001
9 300 1110100011 6,000 3000/P4200001 200 1110100031 -6,000 3000/P5000002
10 300 1110100011 -6,000 3000/P6600001 200 1110100031 0 3000/P5000200
11 300 1110100031 10,000 3000/P3400001
12 300 1110100031 -10,000 3000/P4200001
13 300 1110100031 6,500 3000/P5000015
14 300 1110100031 3,800 3000/P5000202
15 200 1110100011 3,857 3000/P4150001
16 200 1110100011 4,000 3000/P6310001
17 200 1110100011 3,800 3000/P7000001
18 200 1110100031 6,000 3000/P4150001
19 200 1110100031 -6,000 3000/P5000002
20 200 1110100031 0 3000/P5000200
Data

Ezguy4u · Dec 21, 2023

Ok I think I see it now. I am going to say the Account Number is the controlling factor. Second is the company code. If the amount adds up to zero, then it goes to the results table. Now it's getting late so I don't know if I can come up with anything tonight. I'll bet some of the A+ students might weigh in.

taruna1986 · Dec 21, 2023

Ezguy4u said:
Ok I think I see it now. I am going to say the Account Number is the controlling factor. Second is the company code. If the amount adds up to zero, then it goes to the results table. Now it's getting late so I don't know if I can come up with anything tonight. I'll bet some of the A+ students might weigh in.

I appreciate your clarity on the criteria. No rust at all. Feel free to take your time. Have a great evening.

Joe4 · Dec 21, 2023

Sorry, I have been off-line since yesterday.

Since you are using Excel 365, I was hoping to be able make use of the new FILTER function. I thought maybe we could use SUMIFS in the condition to return just the value where the sum of all the Company/Account Number combinations add up to 0, but I am unable to get it to work.

I was trying to do something like this, but it is returning a #VALUE error for me.

Excel Formula:

=FILTER(A3:D20,SUMIFS($C$3:$C$20,$A$3:$A$20,$A3,$B$3:$B$20,$B3)=0)

Maybe that idea will spur someone else?

Ezguy4u · Dec 21, 2023

I want to post the original problem using XL2BB

23-12-20 rev b.xlsm

A

B

C

D

E

F

G

H

I

1

Before: Original Data

After: Final results

2

Company code

Account Number

Amount

Profit Center

Company code

Account Number

Amount

Profit Center

3

100

1110100011

10,000

3000/P1200001

100

1110100011

10,000

3000/P1200001

4

200

1110100011

3,857

3000/P4150001

100

1110100011

-10,000

3000/P6300001

5

300

1110100011

6,000

3000/P4200001

100

1110100011

0

3000/P6700001

6

100

1110100011

-10,000

3000/P6300001

300

1110100011

6,000

3000/P4200001

7

200

1110100011

4,000

3000/P6310001

300

1110100011

-6,000

3000/P6600001

8

300

1110100011

-6,000

3000/P6600001

200

1110100031

6,000

3000/P4150001

9

100

1110100011

0

3000/P6700001

200

1110100031

-6,000

3000/P5000002

10

200

1110100011

3,800

3000/P7000001

200

1110100031

0

3000/P5000200

11

300

1110100031

10,000

3000/P3400001

12

100

1110100031

3,857

3000/P4050001

13

200

1110100031

6,000

3000/P4150001

14

300

1110100031

-10,000

3000/P4200001

15

100

1110100031

4,000

3000/P5000001

16

200

1110100031

-6,000

3000/P5000002

17

300

1110100031

6,500

3000/P5000015

18

100

1110100031

3,800

3000/P5000023

19

200

1110100031

0

3000/P5000200

20

300

1110100031

3,800

3000/P5000202

Sheet1 (7)

taruna1986 · Dec 21, 2023

Ezguy4u said:
I want to post the original problem using XL2BB

23-12-20 rev b.xlsm
A B C D E F G H I
1 Before: Original Data After: Final results
2 Company code Account Number Amount Profit Center Company code Account Number Amount Profit Center
3 100 1110100011 10,000 3000/P1200001 100 1110100011 10,000 3000/P1200001
4 200 1110100011 3,857 3000/P4150001 100 1110100011 -10,000 3000/P6300001
5 300 1110100011 6,000 3000/P4200001 100 1110100011 0 3000/P6700001
6 100 1110100011 -10,000 3000/P6300001 300 1110100011 6,000 3000/P4200001
7 200 1110100011 4,000 3000/P6310001 300 1110100011 -6,000 3000/P6600001
8 300 1110100011 -6,000 3000/P6600001 200 1110100031 6,000 3000/P4150001
9 100 1110100011 0 3000/P6700001 200 1110100031 -6,000 3000/P5000002
10 200 1110100011 3,800 3000/P7000001 200 1110100031 0 3000/P5000200
11 300 1110100031 10,000 3000/P3400001
12 100 1110100031 3,857 3000/P4050001
13 200 1110100031 6,000 3000/P4150001
14 300 1110100031 -10,000 3000/P4200001
15 100 1110100031 4,000 3000/P5000001
16 200 1110100031 -6,000 3000/P5000002
17 300 1110100031 6,500 3000/P5000015
18 100 1110100031 3,800 3000/P5000023
19 200 1110100031 0 3000/P5000200
20 300 1110100031 3,800 3000/P5000202
Sheet1 (7)

Ezguy4u- Have you just shared the snip of what I shared before or used the macro to get the final results

taruna1986 · Dec 21, 2023

Joe4 said:
Sorry, I have been off-line since yesterday.

Since you are using Excel 365, I was hoping to be able make use of the new FILTER function. I thought maybe we could use SUMIFS in the condition to return just the value where the sum of all the Company/Account Number combinations add up to 0, but I am unable to get it to work.

I was trying to do something like this, but it is returning a #VALUE error for me.

Excel Formula:

=FILTER(A3:D20,SUMIFS($C$3:$C$20,$A$3:$A$20,$A3,$B$3:$B$20,$B3)=0)

View attachment 103903

Maybe that idea will spur someone else?

@Joe4 All good, thanks for taking out time to take a look.

Ezguy4u · Dec 25, 2023

taruna here is my program to solve your problem. My programs tend to be big. Now it should work but if you find a problem let us know.

VBA Code:

Sub Prog06()

Dim LastRow As Long
Dim a As Long
Dim Row2 As Long
Dim Row As Long
Dim Row3 As Long

Row = 3
Row2 = 3
Row3 = 3

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2:D" & LastRow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "A3:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "B3:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A2:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
  Do Until IsEmpty(Cells(Row, 1))

If Cells(Row, 2) = Cells(Row + 1, 2) And Cells(Row, 1) = Cells(Row + 1, 1) Then
    Cells(Row2, 11) = Cells(Row, 1)
    Cells(Row2, 12) = Cells(Row, 2)
    Cells(Row2, 13) = Cells(Row, 3)
    Cells(Row2, 14) = Cells(Row, 4)
Else
    Cells(Row2, 11) = Cells(Row, 1)
    Cells(Row2, 12) = Cells(Row, 2)
    Cells(Row2, 13) = Cells(Row, 3)
    Cells(Row2, 14) = Cells(Row, 4)
    Cells(Row2 + 1, 13).Formula = "=sum(M3:M" & Row2 & ")"
    
If Cells(Row2 + 1, 13) = 0 Then
    Range("K3:N" & Row2).Select
    Selection.Copy
    Range("F" & Row3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K3:N" & Row2 + 1).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    Row3 = Cells(Rows.Count, "F").End(xlUp).Row + 1
    Row2 = 2
Else
    Range("K3:N" & Row2 + 1).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    Row2 = 2
   
   End If
    
    End If
    Row2 = Row2 + 1
    Row = Row + 1
    Range("A2").Select
Loop
    
    
Range("A2").Select

End Sub

Ezguy4u · Dec 25, 2023

I forgot to add the screen updating option

VBA Code:

Sub Prog06()

Dim LastRow As Long
Dim a As Long
Dim Row2 As Long
Dim Row As Long
Dim Row3 As Long

Row = 3
Row2 = 3
Row3 = 3
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2:D" & LastRow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "A3:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "B3:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A2:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
  Do Until IsEmpty(Cells(Row, 1))

If Cells(Row, 2) = Cells(Row + 1, 2) And Cells(Row, 1) = Cells(Row + 1, 1) Then
    Cells(Row2, 11) = Cells(Row, 1)
    Cells(Row2, 12) = Cells(Row, 2)
    Cells(Row2, 13) = Cells(Row, 3)
    Cells(Row2, 14) = Cells(Row, 4)
Else
    Cells(Row2, 11) = Cells(Row, 1)
    Cells(Row2, 12) = Cells(Row, 2)
    Cells(Row2, 13) = Cells(Row, 3)
    Cells(Row2, 14) = Cells(Row, 4)
    Cells(Row2 + 1, 13).Formula = "=sum(M3:M" & Row2 & ")"
    
If Cells(Row2 + 1, 13) = 0 Then
    Range("K3:N" & Row2).Select
    Selection.Copy
    Range("F" & Row3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K3:N" & Row2 + 1).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    Row3 = Cells(Rows.Count, "F").End(xlUp).Row + 1
    Row2 = 2
Else
    Range("K3:N" & Row2 + 1).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    Row2 = 2
   
   End If
    
    End If
    Row2 = Row2 + 1
    Row = Row + 1
    Range("A2").Select
Loop
    
 Application.ScreenUpdating = True
Range("A2").Select

End Sub

Need help in coding

taruna1986

New Member

taruna1986

New Member

Excel Facts

Ezguy4u

Active Member

taruna1986

New Member

Joe4

MrExcel MVP, Junior Admin

Ezguy4u

Active Member

taruna1986

New Member

Ezguy4u- Have you just shared the snip of what I shared before or used the macro to get the final results

taruna1986

New Member

Ezguy4u

Active Member

Ezguy4u

Active Member

Similar threads

Forum statistics

Share this page

Need help in coding

New Member

New Member

Excel Facts

Active Member

New Member

MrExcel MVP, Junior Admin

Active Member

New Member

Ezguy4u- Have you just shared the snip of what I shared before or used the macro to get the final results​

New Member

Active Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

Ezguy4u- Have you just shared the snip of what I shared before or used the macro to get the final results