Pasting to a Row with a Specific Value in another Workbook

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am quite new to this and I have gotten so far but I can't seem to find a structure to do what I need to do.

Below I have attached Workbook1 and Workbook2. I can move data from both workbooks, but I was to paste to a row/column that has a specific value in Workbook2.

In my code, I can find the rows containing 301 and 302, and then copy them. I do not want to assign a column/row when pasting to the Workbook2. Is there anyway I could read Workbook2, find "Account 301" in column A, skip the subheadings (row below), and search for "- AE" in column E, then paste all additional rows copied from 301 from Workbook1 to this row?

Workbook1.xlsm
ABCDEFGH
1Account: 301
2
3DateDetailsReferenceTypeDebitCreditBalance
4
521/01/2019PURCHASE301Bank665.00-665.00
619/03/2019PURCHASE301Bank120.00-785.00
701/04/2019PURCHASE301Bank384.00-1,169.00
803/05/2019PURCHASE301Bank500.00-1,669.00
910/05/2019PURCHASE301Bank500.00-2,169.00
1010/05/2019PURCHASE301Bank500.00-2,669.00
1117/05/2019PURCHASE301Bank500.00-3,169.00
1223/05/2019PURCHASE301Bank500.00-3,669.00
1329/05/2019PURCHASE301Bank585.00-4,254.00
14
15Account: 302
16
17DateDetailsReferenceTypeDebitCreditBalance
18
1921/01/2019PURCHASE302Bank1,000.00-1,000.00
2018/02/2019PURCHASE302Bank500.00-1,500.00
2101/03/2019PURCHASE302Bank1,000.00-2,500.00
2213/03/2019PURCHASE302Bank1,000.00-3,500.00
2318/04/2019PURCHASE302Bank1,000.00-4,500.00
2431/05/2019PURCHASE302Bank1,000.00-5,500.00
2519/06/2019PURCHASE302Bank1,000.00-6,500.00
2602/07/2019PURCHASE302Bank1,000.00-7,500.00
2726/07/2019PURCHASE302Bank1,000.00-8,500.00
2806/08/2019PURCHASE302Bank1,000.00-9,500.00
Sheet6


Workbook2.xlsx
ABCDEFGH
1Account 301
2Tran No.Bat No.DateRef No.NarrativeDebitCreditBalance
374433131/12/2019ECWages - AE21439.000.0021439.00
477233631/12/2019ECNarrative6580.960.0028019.96
577533731/12/2019ECNarrative19.640.0028039.60
628039.600.0028039.60
7
8Account 302
9Tran No.Bat No.DateRef No.NarrativeDebitCreditBalance
1074533131/12/2019ECD.R- AE16500.000.0016500.00
1177333631/12/2019ECNarrative9953.520.0026453.52
Sheet2


VBA Code:
Sub Test()

Dim x As Workbook 'Determining Workbook
Dim y As Workbook 'Determining Workbook

Set x = Workbooks.Open("Workbook1.xlsm") 'Opens Workbook1
Set y = Workbooks.Open("Workbook2.xlsx") 'Opens Workbook2

Dim rw As Long, Cell As Range

        For Each Cell In x.Sheets("Sheet5").Range("D2:D1000") 'Range of read first workbook
            rw = Cell.Row
            If Cell.Value = "301" Then 'Search for 301
                Cell.EntireRow.Copy 'Copies entire row containing 302
                y.Sheets("Sheet2").Range("A1").Insert xlShiftDown 'Pastes to Workbook2 on a designated line and creates more below it
            End If
            If Cell.Value = "302" Then 'Search for 302
                Cell.EntireRow.Copy 'Copies entire row containing 302
                y.Sheets("Sheet2").Range("A50").Insert xlShiftDown 'Pastes to Workbook2 on a designated line and creates more below it
            End If
        Next
End Sub

Thanks in advance!
 

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
The worksheet 2, some of the accounts do not have that - AE keyword, thus will cause problem. Can the data be pasted on line just after heading
Trans No. Bat No. Date etc?
Hi @Zot

The first code you gave me works perfectly for one instance, but sadly when I tried to loop the through the 001-999 it was unsuccessful.

The - AE isn't always on that line unfortunately. I cannot upload the whole documents, but depending on the account, it will be located either on row below those headings of a different one.

I was sure this would cause issues as they are two different applications and I would need to sort the data somehow to move it to a specific row or column to make it work I guess.

Apologies for the inconvenience and confusion. I thought it would have been easy to place a loop into the first solution you gave but unfortunately it wasn't so simple.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
The worksheet 2, some of the accounts do not have that - AE keyword, thus will cause problem. Can the data be pasted on line just after heading
Trans No. Bat No. Date etc?
I should also mention that there is text after each account number, unfortunately i could not attach any text
 

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
I found a loop that will work, now it will find the data finding the numbers in column D, the only issue now is pasting to the WB2.

It will only paste under the first instance of - AE which the objective is to paste under the Account found (rngFound) with - AE.


VBA Code:
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY As Workbook 'Determining Workbook
Dim ws1 As Worksheet 'Determining Worksheet
Dim ws2 As Worksheet 'Determining Worksheet
Dim rngFound As Range 'Determining Range
Dim rngSearch As Range 'Determining Range
Dim Fname As Variant
Dim i As Double


' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("AccountDetails") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find("Account " & i) 'Search for i
Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for String - AE


Dim rw As Long, Cell As Range

For i = 1 To 999
        For Each Cell In ws1.Range("D2:D123") 'Range of read first workbook
            rw = Cell.Row
            If Cell.Value = i Then 'Search for i
                Cell.EntireRow.Copy 'Copies entire row containing i
                If Not rngFound Is Nothing Then
                    If Not rngSearch Is Nothing Then
                        ws2.Rows(rngSearch.Row).Insert xlShiftDown 'Pastes to Workbook2 on a designated line and creates more below it
                    End If
                End If
            End If
        Next
    Next i
End Sub
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows
Sorry for a bit delay. I was very busy lately. I revised the code early in the morning before going to work ;)

This code works using the latest data sample you provided. It will give sum formula, better alignment on column Debit, Credit, Balance, but only for the account the program worked on.
You need to rename worksheet to your need.

For data with - AE line, it will copt from Workbook1 to that line and delete the - AE line. For account with no - AE, data is written right after the heading.

Your forgram is looping worksheet1 but not looping worksheet2 to find matching. Your rngFound and rngSearch are outside the loop, thus it was looking for unknown account. Note that your rngFound line has statement .Find("Account " & i), but what is i? It is just fix variable since it is outside the looping For i = 1 To 999.

Here is the my code mentioned above:
VBA Code:
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY As Workbook 'Determining Workbook
Dim ws1 As Worksheet 'Determining Worksheet
Dim ws2 As Worksheet 'Determining Worksheet
Dim rngFound As Range 'Determining Range
Dim rngSearch As Range 'Determining Range
Dim Fname As Variant
Dim DictAcc As Object

' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("Sheet5") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set DictAcc = CreateObject("Scripting.Dictionary")

Application.ScreenUpdating = False

Dim rw As Long, lngAcc As Long, Cell As Range
For Each Cell In ws1.Range("C1:C1000") ' Search for all existed Acc #. Store row# (key) and Acc # (value)
    If Cell Like "*Account*" Then
        DictAcc.Add Cell.Row, Format(Right(Cell, Len(Cell) - 9), "000")
        Debug.Print Format(Right(Cell, Len(Cell) - 9), "000")
    End If
Next

Dim iRow As Long, eRow As Long
Dim strAcc As String
Dim key As Variant

For Each key In DictAcc                                                                                                 ' Go through each account number in Dictionary
    strAcc = "Account " & DictAcc(key)
    Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find(strAcc)  ' Find matching account number
    Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for "- AE"
    If Not rngFound Is Nothing Then
        If Not Len(ws1.Range("C" & key + 5)) = 0 Then                                                         ' Check if specific account has more than one line to copy
            ws1.Range("C" & key + 4, "C" & ws1.Range("C" & key + 4).End(xlDown).Row).EntireRow.Copy
        Else
            ws1.Range("C" & key + 4).EntireRow.Copy
        End If
        If Not rngSearch Is Nothing Then
            iRow = rngSearch.Row
            With ws2.Range("A" & rngSearch.Row).EntireRow
                .Insert
                .Delete                                                                                                        ' Delete row "- AE"
            End With
        Else
            iRow = rngFound.Row + 2
            ws2.Range("A" & rngSearch.Row).EntireRow.Insert
        End If
        
        With ws2
            eRow = .Range("F" & iRow).End(xlDown).Row - 1
            With .Range("F" & iRow, "H" & eRow + 1)
                .NumberFormat = "#,##0.00"
                .HorizontalAlignment = xlGeneral
            End With
            .Range("F" & eRow + 1).Formula = "=SUM(F" & iRow & ":F" & eRow & ")"
            .Range("G" & eRow + 1).Formula = "=SUM(G" & iRow & ":G" & eRow & ")"
            .Range("H" & eRow + 1).Formula = "=SUM(H" & iRow & ":H" & eRow & ")"
        End With
    End If
Next

End Sub
 

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry for a bit delay. I was very busy lately. I revised the code early in the morning before going to work ;)

This code works using the latest data sample you provided. It will give sum formula, better alignment on column Debit, Credit, Balance, but only for the account the program worked on.
You need to rename worksheet to your need.

For data with - AE line, it will copt from Workbook1 to that line and delete the - AE line. For account with no - AE, data is written right after the heading.

Your forgram is looping worksheet1 but not looping worksheet2 to find matching. Your rngFound and rngSearch are outside the loop, thus it was looking for unknown account. Note that your rngFound line has statement .Find("Account " & i), but what is i? It is just fix variable since it is outside the looping For i = 1 To 999.

Here is the my code mentioned above:
VBA Code:
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY As Workbook 'Determining Workbook
Dim ws1 As Worksheet 'Determining Worksheet
Dim ws2 As Worksheet 'Determining Worksheet
Dim rngFound As Range 'Determining Range
Dim rngSearch As Range 'Determining Range
Dim Fname As Variant
Dim DictAcc As Object

' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("Sheet5") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set DictAcc = CreateObject("Scripting.Dictionary")

Application.ScreenUpdating = False

Dim rw As Long, lngAcc As Long, Cell As Range
For Each Cell In ws1.Range("C1:C1000") ' Search for all existed Acc #. Store row# (key) and Acc # (value)
    If Cell Like "*Account*" Then
        DictAcc.Add Cell.Row, Format(Right(Cell, Len(Cell) - 9), "000")
        Debug.Print Format(Right(Cell, Len(Cell) - 9), "000")
    End If
Next

Dim iRow As Long, eRow As Long
Dim strAcc As String
Dim key As Variant

For Each key In DictAcc                                                                                                 ' Go through each account number in Dictionary
    strAcc = "Account " & DictAcc(key)
    Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find(strAcc)  ' Find matching account number
    Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for "- AE"
    If Not rngFound Is Nothing Then
        If Not Len(ws1.Range("C" & key + 5)) = 0 Then                                                         ' Check if specific account has more than one line to copy
            ws1.Range("C" & key + 4, "C" & ws1.Range("C" & key + 4).End(xlDown).Row).EntireRow.Copy
        Else
            ws1.Range("C" & key + 4).EntireRow.Copy
        End If
        If Not rngSearch Is Nothing Then
            iRow = rngSearch.Row
            With ws2.Range("A" & rngSearch.Row).EntireRow
                .Insert
                .Delete                                                                                                        ' Delete row "- AE"
            End With
        Else
            iRow = rngFound.Row + 2
            ws2.Range("A" & rngSearch.Row).EntireRow.Insert
        End If
      
        With ws2
            eRow = .Range("F" & iRow).End(xlDown).Row - 1
            With .Range("F" & iRow, "H" & eRow + 1)
                .NumberFormat = "#,##0.00"
                .HorizontalAlignment = xlGeneral
            End With
            .Range("F" & eRow + 1).Formula = "=SUM(F" & iRow & ":F" & eRow & ")"
            .Range("G" & eRow + 1).Formula = "=SUM(G" & iRow & ":G" & eRow & ")"
            .Range("H" & eRow + 1).Formula = "=SUM(H" & iRow & ":H" & eRow & ")"
        End With
    End If
Next

End Sub
Hi @Zot

Thank you , but unfortunately it doesn't work as there is text after the account headings, I will provide an example.

I have modified the code to this which catches all account numbers and all data needed. Unfortunately, every instance of the loop deletes the - AE rows every time.

Is there a way to copy all rows that have a value and paste it once into WB2 then go to the next value? As the code above loops through each key and deletes the next - AE row throughout WB2.

Sorry to annoy you, you have the patience of a God.

VBA Code:
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY As Workbook 'Determining Workbook
Dim ws1 As Worksheet 'Determining Worksheet
Dim ws2 As Worksheet 'Determining Worksheet
Dim rngFound As Range 'Determining Range
Dim rngSearch As Range 'Determining Range
Dim Fname As Variant
Dim DictAcc As Object
Dim i As Double

' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("AccountDetails") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set DictAcc = CreateObject("Scripting.Dictionary")

Application.ScreenUpdating = False

Dim rw As Long, lngAcc As Long, Cell As Range

For i = 1 To 999
For Each Cell In ws1.Range("D6:D98") ' Search for all existed Acc #. Store row# (key) and Acc # (value)
    If Cell.Value = i Then
        DictAcc.Add Cell.Row, Format(Right(Cell, i), "000")
        Debug.Print Format(Right(Cell, i), "000")
    End If
Next
Next i



Dim iRow As Long, eRow As Long
Dim strAcc As String
Dim key As Variant

For Each key In DictAcc                                                                                                 ' Go through each account number in Dictionary
    strAcc = "Account " & DictAcc(key)
    Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find(strAcc)  ' Find matching account number
    Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for "- AE"
    If Not rngFound Is Nothing Then
        If Not ws1.Range("D" & key) = 0 Then                                                         ' Check if specific account has more than one line to copy
            ws1.Range("D" & key, "D" & ws1.Range("D" & key).End(xlDown).Row).EntireRow.Copy
        Else
            ws1.Range("D" & key).EntireRow.Copy
        End If
        If Not rngSearch Is Nothing Then
            iRow = rngSearch.Row
            With ws2.Range("A" & rngSearch.Row).EntireRow
                .Insert
                .Delete ' Delete row "- AE"
            End With
        Else
            iRow = rngFound.Row
            ws2.Range("A" & rngSearch.Row).EntireRow.Insert
        End If
        
        With ws2
            eRow = .Range("F" & iRow).End(xlDown).Row - 1
            With .Range("F" & iRow, "H" & eRow + 1)
                .NumberFormat = "#,##0.00"
                .HorizontalAlignment = xlGeneral
            End With
            .Range("F" & eRow + 1).Formula = "=SUM(F" & iRow & ":F" & eRow & ")"
            .Range("G" & eRow + 1).Formula = "=SUM(G" & iRow & ":G" & eRow & ")"
            .Range("H" & eRow + 1).Formula = "=SUM(H" & iRow & ":H" & eRow & ")"
        End With
    End If
Next

End Sub

Workbook1.xlsx
ABCDEFGHI
1
2Client:
3Period End:
4
5Account Details
6
7
8Account: 3 Sales - type 3
9
10BatchDateReferenceDetailsDebitCreditBalanceType
11
121002/07/2019003Details-117.41(117.41)Bank
131017/10/2019003Details-7,162.40(7,279.81)Bank
141003/12/2019003Details-2,951.68(10,231.49)Bank
15
16-10,231.49(10,231.49)
17
18Account: 301 Wages and salaries
19
20BatchDateReferenceDetailsDebitCreditBalanceType
21
22321/01/2019301Details1665.00-665.00Bank
23319/03/2019301Details2120.00-785.00Bank
24301/04/2019301Details3384.00-1,169.00Bank
254703/05/2019301Details4500.00-1,669.00Bank
264710/05/2019301Details5500.00-2,169.00Bank
27410/05/2019301Details6500.00-2,669.00Bank
28417/05/2019301Details7500.00-3,169.00Bank
29423/05/2019301Details8500.00-3,669.00Bank
30729/05/2019301Details9585.00-4,254.00Bank
31430/05/2019301Details10500.00-4,754.00Bank
32831/05/2019301Details11375.00-5,129.00Bank
338404/06/2019301Details121,100.00-6,229.00Bank
34705/06/2019301Details13550.00-6,779.00Bank
35406/06/2019301Details14500.00-7,279.00Bank
36807/06/2019301Details15460.00-7,739.00Bank
37707/06/2019301Details16250.00-7,989.00Bank
38413/06/2019301Details17500.00-8,489.00Bank
39814/06/2019301Details18445.00-8,934.00Bank
40714/06/2019301Details19430.00-9,364.00Bank
416017/06/2019301Details20350.00-9,714.00Bank
428418/06/2019301Details21700.00-10,414.00Bank
43420/06/2019301Details22500.00-10,914.00Bank
446125/06/2019301Details23350.00-11,264.00Bank
45427/06/2019301Details24500.00-11,764.00Bank
463228/06/2019301Details2590.00-11,854.00Bank
47728/06/2019301Details2690.00-11,944.00Bank
486202/07/2019301Details27350.00-12,294.00Bank
49404/07/2019301Details28500.00-12,794.00Bank
506310/07/2019301Details29350.00-13,144.00Bank
51411/07/2019301Details30500.00-13,644.00Bank
52815/07/2019301Details31110.00-13,754.00Bank
53715/07/2019301Details32140.00-13,894.00Bank
54418/07/2019301Details33500.00-14,394.00Bank
559222/07/2019301Details34300.00-14,694.00Bank
56425/07/2019301Details35500.00-15,194.00Bank
57826/07/2019301Details36110.00-15,304.00Bank
58726/07/2019301Details37150.00-15,454.00Bank
59401/08/2019301Details38500.00-15,954.00Bank
60802/08/2019301Details39265.00-16,219.00Bank
61702/08/2019301Details40280.00-16,499.00Bank
62408/08/2019301Details41500.00-16,999.00Bank
63415/08/2019301Details42500.00-17,499.00Bank
64816/08/2019301Details43195.00-17,694.00Bank
655121/08/2019301Details44450.00-18,144.00Bank
66422/08/2019301Details45500.00-18,644.00Bank
67429/08/2019301Details46500.00-19,144.00Bank
68830/08/2019301Details4780.00-19,224.00Bank
69730/08/2019301Details4880.00-19,304.00Bank
70405/09/2019301Details49500.00-19,804.00Bank
71412/09/2019301Details50500.00-20,304.00Bank
72419/09/2019301Details51500.00-20,804.00Bank
738818/10/2019301Details52235.00-21,039.00Bank
748423/10/2019301Details53400.00-21,439.00Bank
75
7621,439.00-21,439.00
77
78Account: 302 Directors remuneration
79
80BatchDateReferenceDetailsDebitCreditBalanceType
81
82221/01/2019302Details11,000.00-1,000.00Bank
83218/02/2019302Details2500.00-1,500.00Bank
84201/03/2019302Details31,000.00-2,500.00Bank
85213/03/2019302Details41,000.00-3,500.00Bank
86218/04/2019302Details51,000.00-4,500.00Bank
87231/05/2019302Details61,000.00-5,500.00Bank
88219/06/2019302Details71,000.00-6,500.00Bank
89202/07/2019302Details81,000.00-7,500.00Bank
90226/07/2019302Details91,000.00-8,500.00Bank
91206/08/2019302Details101,000.00-9,500.00Bank
92203/09/2019302Details111,000.00-10,500.00Bank
93211/09/2019302Details121,000.00-11,500.00Bank
94203/10/2019302Details131,000.00-12,500.00Bank
95229/10/2019302Details141,000.00-13,500.00Bank
96219/11/2019302Details151,000.00-14,500.00Bank
97203/12/2019302Details161,000.00-15,500.00Bank
98211/12/2019302Details171,000.00-16,500.00Bank
99
10016,500.00-16,500.00
101
102Account: 322 Rent payable
103
104BatchDateReferenceDetailsDebitCreditBalanceType
105
1068211/12/2019322Details1,000.00-1,000.00Bank
107
1081,000.00-1,000.00
AccountDetails


Workbook2.xlsx
ABCDEFGHIJKLMNOP
1Nominal Account Details
2Client:
3Year End:
4
5
6Account 001 Contracting Sales
7Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
846331824/01/2019Details10.00881.06-881.06SAI711NN€0.00N
946431824/01/2019Details20.001233.48-2114.54SAI711NN€0.00N
1046531829/01/2019Details30.009647.58-11762.12SAI711NN€0.00N
1146631829/01/2019Details40.002378.85-14140.97SAI711NN€0.00N
1246731804/02/2019Details50.00625.55-14766.52SAI711NN€0.00N
1346831908/03/2019Details60.006607.93-21374.45SAI711NN€0.00N
1446931926/03/2019Details70.00651.98-22026.43SAI711NN€0.00N
1547031902/04/2019Details80.00704.85-22731.28SAI711NN€0.00N
1647132406/05/2019Details90.002881.06-25612.34SAI711NN€0.00N
1747232415/05/2019Details100.003083.70-28696.04SAI711NN€0.00N
1847332422/05/2019Details110.00792.95-29488.99SAI711NN€0.00N
1947432422/05/2019Details120.002114.54-31603.53SAI711NN€0.00N
2047532422/05/2019Details130.007048.46-38651.99SAI711NN€0.00N
2147632422/05/2019Details140.004405.29-43057.28SAI711NN€0.00N
2247732422/05/2019Details150.007048.46-50105.74SAI711NN€0.00N
2347832405/06/2019Details160.005726.87-55832.61SAI711NN€0.00N
2447932410/06/2019Details170.002114.54-57947.15SAI711NN€0.00N
2548032410/06/2019Details180.008810.57-66757.72SAI711NN€0.00N
2648132424/06/2019Details190.006167.40-72925.12SAI711NN€0.00N
2748232426/06/2019Details200.003524.23-76449.35SAI711NN€0.00N
2848332426/06/2019Details210.00154.19-76603.54SAI711NN€0.00N
2948432428/06/2019Details220.004405.29-81008.83SAI711NN€0.00N
3048632604/07/2019Details230.0014096.92-95105.75SAI711NN€0.00N
3148732605/07/2019Details240.0016299.56-111405.31SAI711NN€0.00N
3248832616/07/2019Details250.00440.53-111845.84SAI711NN€0.00N
3348932629/07/2019Details260.00748.90-112594.74SAI711NN€0.00N
3449032629/07/2019Details270.001585.90-114180.64SAI711NN€0.00N
3549132607/08/2019Details280.004405.29-118585.93SAI711NN€0.00N
3649332607/08/2019Details290.006167.40-124753.33SAI711NN€0.00N
3749432609/08/2019Details300.005947.14-130700.47SAI711NN€0.00N
3849532613/08/2019Details310.002026.43-132726.90SAI711NN€0.00N
3949632613/08/2019Details320.006951.54-139678.44SAI711NN€0.00N
4049732619/08/2019Details330.004405.29-144083.73SAI711NN€0.00N
4149832619/08/2019Details340.00792.95-144876.68SAI711NN€0.00N
4249932619/08/2019Details350.00992.95-145869.63SAI711NN€0.00N
4350032619/08/2019Details360.001207.05-147076.68SAI711NN€0.00N
4450132626/08/2019Details370.002995.60-150072.28SAI711NN€0.00N
4550232704/09/2019Details380.002643.17-152715.45SAI711NN€0.00N
4650332710/09/2019Details390.007048.46-159763.91SAI711NN€0.00N
4750432705/10/2019Details400.004140.97-163904.88SAI711NN€0.00N
4850532704/10/2019Details410.008722.47-172627.35SAI711NN€0.00N
4950632717/10/2019Details420.00881.06-173508.41SAI711NN€0.00N
5050732724/10/2019Details430.004030.84-177539.25SAI711NN€0.00N
5150832729/10/2019Details440.001145.37-178684.62SAI711NN€0.00N
5250933006/11/2019Details450.00969.16-179653.78SAI711NN€0.00N
5351033006/11/2019Details460.00149.78-179803.56SAI711NN€0.00N
5451133011/11/2019Details470.00704.85-180508.41SAI711NN€0.00N
5551233011/11/2019Details480.00440.53-180948.94SAI711NN€0.00N
5651333006/12/2019Details490.005101.32-186050.26SAI711NN€0.00N
5751433009/12/2019Details500.006105.73-192155.99SAI711NN€0.00N
5851533011/12/2019Details510.00951.54-193107.53SAI711NN€0.00N
5951633011/12/2019Details520.001162.96-194270.49SAI711NN€0.00N
6051733011/12/2019Details530.007929.51-202200.00SAI711NN€0.00N
6151833011/12/2019Details540.00704.85-202904.85SAI711NN€0.00N
6251933012/12/2019Details550.002643.17-205548.02SAI711NN€0.00N
6352033020/12/2019Details560.002466.96-208014.98SAI711NN€0.00N
6452133020/12/2019Details570.005726.87-213741.85SAI711NN€0.00N
6578033631/12/2019Details580.0020264.32-234006.17JNLNN€0.00N
6678533631/12/2019Details5912026.430.00-221979.74JNLNN€0.00N
67194932805/10/2019Details6020.480.00-221959.26PAI812NN€0.00N
6812046.91234006.17-221959.26
69
70Account 002 Barley Sales
71Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
7252234331/12/2019Details10.008005.87-8005.87SAI711NN€0.00N
7377833731/12/2019Details20.009620.01-17625.88JNL999ANN€0.00N
740.0017625.88-17625.88
75
76Account 003 Premia
77Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
7874333131/12/2019Details- AE0.0010231.49-10231.49JNLNN€0.00N
790.0010231.49-10231.49
80
81Account 004 Grass/Silage/Straw
82Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
8349232607/08/2019Details0.006000.00-6000.00SAI711NN€0.00N
840.006000.00-6000.00
85
86Account 111 Materials
87Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
88200533331/12/2019Details277.680.00277.68PAI812NN€0.00N
89277.680.00277.68
90
91Account 112A Fertiliser Purchases
92Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
93200033331/12/2019Details11425.000.001425.00PAI812NN€0.00N
94200233331/12/2019Details247.400.001472.40PAI812NN€0.00N
95200833331/12/2019Details33280.000.004752.40PAI812NN€0.00N
964752.400.004752.40
97
98Account 112C Seeds & Sprays
99Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
100189932231/05/2019Details1112.000.00112.00PAI812NN€0.00N
101189932231/05/2019Details21552.040.001664.04PAI812NN€0.00N
102194332526/07/2019Details328.000.001692.04PAI812NN€0.00N
103194332526/07/2019Details4223.580.001915.62PAI812NN€0.00N
104200933331/12/2019Details5147.500.002063.12PAI812NN€0.00N
105201033331/12/2019Details61475.000.003538.12PAI812NN€0.00N
106201133331/12/2019Details7742.500.004280.62PAI812NN€0.00N
107201233331/12/2019Details81230.000.005510.62PAI812NN€0.00N
108201333331/12/2019Details91626.000.007136.62PAI812NN€0.00N
109201733431/12/2019Details100.0088.507048.12PAC812NN€0.00N
1107136.6288.507048.12
111
112Account 301 Wages and salaries
113Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
114
11574433131/12/2019Details - AE21439.000.0021439.00JNLNN€0.00N
11677233631/12/2019Details16580.960.0028019.96JNLNN€0.00N
11777533731/12/2019Details219.640.0028039.60JNL381NN€0.00N
11828039.600.0028039.60
119
120Account 302 Directors remuneration
121Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
12274533131/12/2019Details- AE16500.000.0016500.00JNLNN€0.00N
12377333631/12/2019Details19953.520.0026453.52JNLNN€0.00N
12426453.520.0026453.52
125
126Account 322 Rent payable
127Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
12874633131/12/2019Details- AE1000.000.001000.00JNLNN€0.00N
1291000.000.001000.00
Sheet1
 

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
I think the easiest option is to ignore the - AE until the end. As long as the data is under the right Account, we can delete the rows containing - AE when all is copied over.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I think the easiest option is to ignore the - AE until the end. As long as the data is under the right Account, we can delete the rows containing - AE when all is copied over.
Not sure I understood. You said you wanted to delete - AE row if existed in the accounts. It does not matter if it is deleted after data for each account is deleted or after all was done, right? Was deleting -AE after copying data to each account created problem?

Yes, the code will not work since you have text string in account name after account number. You should have created dummy account that way ahead. However, that is not my main concern here. Was my code worked for the sample worksheets you provided without the text after account number? If not, what was not right.
 

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Not sure I understood. You said you wanted to delete - AE row if existed in the accounts. It does not matter if it is deleted after data for each account is deleted or after all was done, right? Was deleting -AE after copying data to each account created problem?

Yes, the code will not work since you have text string in account name after account number. You should have created dummy account that way ahead. However, that is not my main concern here. Was my code worked for the sample worksheets you provided without the text after account number? If not, what was not right.
Apologies for the confusion, being new to this is hard to explain Or even understand sometimes.

when I ran your code it was copying the whole line from workbook1 eg. Account 003 Primea, so I manipulated the code a little but it loops through finding the keys. When the keys are recorded, it would copy and paste them to the designated line. Then it would copy the bottom two lines of (eg 003) and paste them to the next - AE value found (301). Then the last row of the value 003 for example, and paste that to the next instance of - AE (302).

this happened before I manipulated the code too.

I’m wondering if we could find the account number in workbook1 paste those rows to workbook2 throughout and then delete any rows containing - AE, then sum up the total?

again apologies for the confusion and any difficulties caused.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows
For about the account name first. After copying the Account 003 from Sheet1, how the Account 003 on Sheet2 supposed to look like?. This probably clear thing out. ;)
 

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
For about the account name first. After copying the Account 003 from Sheet1, how the Account 003 on Sheet2 supposed to look like?. This probably clear thing out. ;)
From the previous worksheets I have posted, I need the end result to look like this

Workbook2.xlsx
ABCDEFGHI
1Nominal Account Details
2Client:
3Year End:
4
5
6Account 001 Contracting Sales
7Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
846331824/01/2019Details10.00881.06-881.06SAI
946431824/01/2019Details20.001233.48-2114.54SAI
1046531829/01/2019Details30.009647.58-11762.12SAI
1146631829/01/2019Details40.002378.85-14140.97SAI
1246731804/02/2019Details50.00625.55-14766.52SAI
1346831908/03/2019Details60.006607.93-21374.45SAI
1446931926/03/2019Details70.00651.98-22026.43SAI
1547031902/04/2019Details80.00704.85-22731.28SAI
1647132406/05/2019Details90.002881.06-25612.34SAI
1747232415/05/2019Details100.003083.70-28696.04SAI
1847332422/05/2019Details110.00792.95-29488.99SAI
1947432422/05/2019Details120.002114.54-31603.53SAI
2047532422/05/2019Details130.007048.46-38651.99SAI
2147632422/05/2019Details140.004405.29-43057.28SAI
2247732422/05/2019Details150.007048.46-50105.74SAI
2347832405/06/2019Details160.005726.87-55832.61SAI
2447932410/06/2019Details170.002114.54-57947.15SAI
2548032410/06/2019Details180.008810.57-66757.72SAI
2648132424/06/2019Details190.006167.40-72925.12SAI
2748232426/06/2019Details200.003524.23-76449.35SAI
2848332426/06/2019Details210.00154.19-76603.54SAI
2948432428/06/2019Details220.004405.29-81008.83SAI
3048632604/07/2019Details230.0014096.92-95105.75SAI
3148732605/07/2019Details240.0016299.56-111405.31SAI
3248832616/07/2019Details250.00440.53-111845.84SAI
3348932629/07/2019Details260.00748.90-112594.74SAI
3449032629/07/2019Details270.001585.90-114180.64SAI
3549132607/08/2019Details280.004405.29-118585.93SAI
3649332607/08/2019Details290.006167.40-124753.33SAI
3749432609/08/2019Details300.005947.14-130700.47SAI
3849532613/08/2019Details310.002026.43-132726.90SAI
3949632613/08/2019Details320.006951.54-139678.44SAI
4049732619/08/2019Details330.004405.29-144083.73SAI
4149832619/08/2019Details340.00792.95-144876.68SAI
4249932619/08/2019Details350.00992.95-145869.63SAI
4350032619/08/2019Details360.001207.05-147076.68SAI
4450132626/08/2019Details370.002995.60-150072.28SAI
4550232704/09/2019Details380.002643.17-152715.45SAI
4650332710/09/2019Details390.007048.46-159763.91SAI
4750432705/10/2019Details400.004140.97-163904.88SAI
4850532704/10/2019Details410.008722.47-172627.35SAI
4950632717/10/2019Details420.00881.06-173508.41SAI
5050732724/10/2019Details430.004030.84-177539.25SAI
5150832729/10/2019Details440.001145.37-178684.62SAI
5250933006/11/2019Details450.00969.16-179653.78SAI
5351033006/11/2019Details460.00149.78-179803.56SAI
5451133011/11/2019Details470.00704.85-180508.41SAI
5551233011/11/2019Details480.00440.53-180948.94SAI
5651333006/12/2019Details490.005101.32-186050.26SAI
5751433009/12/2019Details500.006105.73-192155.99SAI
5851533011/12/2019Details510.00951.54-193107.53SAI
5951633011/12/2019Details520.001162.96-194270.49SAI
6051733011/12/2019Details530.007929.51-202200.00SAI
6151833011/12/2019Details540.00704.85-202904.85SAI
6251933012/12/2019Details550.002643.17-205548.02SAI
6352033020/12/2019Details560.002466.96-208014.98SAI
6452133020/12/2019Details570.005726.87-213741.85SAI
6578033631/12/2019Details580.0020264.32-234006.17JNL
6678533631/12/2019Details5912026.430.00-221979.74JNL
67194932805/10/2019Details6020.480.00-221959.26PAI
6812046.91234006.17-221959.26
69
70Account 002 Barley Sales
71Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
7252234331/12/2019Details10.008005.87-8005.87SAI
7377833731/12/2019Details20.009620.01-17625.88JNL
740.0017625.88-17625.88
75
76Account 003 Premia
77Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
781002/07/2019003Details-117.41(117.41)Bank
791017/10/2019003Details-7,162.40(7,279.81)Bank
801003/12/2019003Details-2,951.68(10,231.49)Bank
810.0010231.49-17628.71
82
83Account 004 Grass/Silage/Straw
84Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
8549232607/08/2019Details0.006000.00-6000.00SAI
860.006000.00-6000.00
87
88Account 111 Materials
89Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
90200533331/12/2019Details277.680.00277.68PAI
91277.680.00277.68
92
93Account 112A Fertiliser Purchases
94Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
95200033331/12/2019Details11425.000.001425.00PAI
96200233331/12/2019Details247.400.001472.40PAI
97200833331/12/2019Details33280.000.004752.40PAI
984752.400.004752.40
99
100Account 112C Seeds & Sprays
101Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
102189932231/05/2019Details1112.000.00112.00PAI
103189932231/05/2019Details21552.040.001664.04PAI
104194332526/07/2019Details328.000.001692.04PAI
105194332526/07/2019Details4223.580.001915.62PAI
106200933331/12/2019Details5147.500.002063.12PAI
107201033331/12/2019Details61475.000.003538.12PAI
108201133331/12/2019Details7742.500.004280.62PAI
109201233331/12/2019Details81230.000.005510.62PAI
110201333331/12/2019Details91626.000.007136.62PAI
111201733431/12/2019Details100.0088.507048.12PAC
1127136.6288.5034960.92
113
114Account 301 Wages and salaries
115Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
116
117321/01/2019301Details1665.00-665.00Bank
118319/03/2019301Details2120.00-785.00Bank
119301/04/2019301Details3384.00-1,169.00Bank
1204703/05/2019301Details4500.00-1,669.00Bank
1214710/05/2019301Details5500.00-2,169.00Bank
122410/05/2019301Details6500.00-2,669.00Bank
123417/05/2019301Details7500.00-3,169.00Bank
124423/05/2019301Details8500.00-3,669.00Bank
125729/05/2019301Details9585.00-4,254.00Bank
126430/05/2019301Details10500.00-4,754.00Bank
127831/05/2019301Details11375.00-5,129.00Bank
1288404/06/2019301Details121,100.00-6,229.00Bank
129705/06/2019301Details13550.00-6,779.00Bank
130406/06/2019301Details14500.00-7,279.00Bank
131807/06/2019301Details15460.00-7,739.00Bank
132707/06/2019301Details16250.00-7,989.00Bank
133413/06/2019301Details17500.00-8,489.00Bank
134814/06/2019301Details18445.00-8,934.00Bank
135714/06/2019301Details19430.00-9,364.00Bank
1366017/06/2019301Details20350.00-9,714.00Bank
1378418/06/2019301Details21700.00-10,414.00Bank
138420/06/2019301Details22500.00-10,914.00Bank
1396125/06/2019301Details23350.00-11,264.00Bank
140427/06/2019301Details24500.00-11,764.00Bank
1413228/06/2019301Details2590.00-11,854.00Bank
142728/06/2019301Details2690.00-11,944.00Bank
1436202/07/2019301Details27350.00-12,294.00Bank
144404/07/2019301Details28500.00-12,794.00Bank
1456310/07/2019301Details29350.00-13,144.00Bank
146411/07/2019301Details30500.00-13,644.00Bank
147815/07/2019301Details31110.00-13,754.00Bank
148715/07/2019301Details32140.00-13,894.00Bank
149418/07/2019301Details33500.00-14,394.00Bank
1509222/07/2019301Details34300.00-14,694.00Bank
151425/07/2019301Details35500.00-15,194.00Bank
152826/07/2019301Details36110.00-15,304.00Bank
153726/07/2019301Details37150.00-15,454.00Bank
154401/08/2019301Details38500.00-15,954.00Bank
155802/08/2019301Details39265.00-16,219.00Bank
156702/08/2019301Details40280.00-16,499.00Bank
157408/08/2019301Details41500.00-16,999.00Bank
158415/08/2019301Details42500.00-17,499.00Bank
159816/08/2019301Details43195.00-17,694.00Bank
1605121/08/2019301Details44450.00-18,144.00Bank
161422/08/2019301Details45500.00-18,644.00Bank
162429/08/2019301Details46500.00-19,144.00Bank
163830/08/2019301Details4780.00-19,224.00Bank
164730/08/2019301Details4880.00-19,304.00Bank
165405/09/2019301Details49500.00-19,804.00Bank
166412/09/2019301Details50500.00-20,304.00Bank
167419/09/2019301Details51500.00-20,804.00Bank
1688818/10/2019301Details52235.00-21,039.00Bank
1698423/10/2019301Details53400.00-21,439.00Bank
17077233631/12/2019Details16580.960.0028019.96JNL
17177533731/12/2019Details219.640.0028039.60JNL
17228039.600.00680108.56
173
174Account 302 Directors remuneration
175Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
176221/01/2019302Details11,000.00-1,000.00Bank
177218/02/2019302Details2500.00-1,500.00Bank
178201/03/2019302Details31,000.00-2,500.00Bank
179213/03/2019302Details41,000.00-3,500.00Bank
180218/04/2019302Details51,000.00-4,500.00Bank
181231/05/2019302Details61,000.00-5,500.00Bank
182219/06/2019302Details71,000.00-6,500.00Bank
183202/07/2019302Details81,000.00-7,500.00Bank
184226/07/2019302Details91,000.00-8,500.00Bank
185206/08/2019302Details101,000.00-9,500.00Bank
186203/09/2019302Details111,000.00-10,500.00Bank
187211/09/2019302Details121,000.00-11,500.00Bank
188203/10/2019302Details131,000.00-12,500.00Bank
189229/10/2019302Details141,000.00-13,500.00Bank
190219/11/2019302Details151,000.00-14,500.00Bank
191203/12/2019302Details161,000.00-15,500.00Bank
192211/12/2019302Details171,000.00-16,500.00Bank
19377333631/12/2019Details19953.520.0026453.52JNL
19426453.520.00171453.52
195
196Account 322 Rent payable
197Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
1988211/12/2019322Details1,000.00-1,000.00Bank
1991000.000.001000.00
Sheet1
Cell Formulas
RangeFormula
G81:H81G81=SUM(G78:G80)
F112:H112F112=SUM(F102:F111)
F172,H172F172=SUM(F117:F171)
F194,H194F194=SUM(F176:F193)
F199,H199F199=SUM(F198)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,688
Members
416,040
Latest member
patriciocabello

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