Previous Topic Next Topic

To check variance for each other codes

Closed
jack - Jun 21, 2010 at 03:57 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 26, 2010 at 07:43 PM
Hello Sir,

In "Data" tab contains data that has to be analysed.

Normally each "Code" should have opposite code ("in booked in " field) in each line.
For example in "data" tab row 4 in "code" b067 amount assigned against C307 ( which is actually last 4 characters in column E) and in row 6 in code C307 opposite code 067 is assgined with (reverse signage)


In Column F - this is first step to get the figures how much is booked against each "Code" & "booked in"
So basically I want to get the a report like in "desired report" tab that shows how much is booked againt each other and what is the variance.

Can it be done through a macro?

Pls find the data link:

Thanks & Regards,



https://authentification.site/files/23072392/Balance_for_excel.xls
Related:

5 responses

Answer 1 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 21, 2010 at 06:19 PM
Could you go into more detail about your variance and where all these amounts are coming from. How would you have handled "d420" code in data tab ?
Answer 2 / 5
jack
Jun 22, 2010 at 06:37 AM
Kindly see the updated link for amount column.

Code d420 is facing KT89 (last 4 characters of column E) so amount assigned is 71MM. At the same time Code KT89 facing d420 should also have same amount, variance should be coming in report.

Column F in Data tab is not part of original data ideally be coming automatically


https://authentification.site/files/23079010/Balance_for_excel.xls
Answer 3 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 22, 2010 at 08:24 PM
so on data tab, Account and currency does not matter?. All that would matter is that add amount of D420 and show it on your desired report under report and add the amount that faces D420 which in this case would be KT89 and show it under amount2 and then simply variance would be amount - amount 2 ?
Answer 4 / 5
Jack
Jun 24, 2010 at 04:41 AM
Account & Currency also matter. This varaince should be Account & Currency wise as well because there could be same code in different curreincies. So if you could take curr & account in report- I agree the way you are trying to show variance in report for D420 & KT89.
Answer 5 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 26, 2010 at 06:17 AM
ok try this 

Sub generateSummary()
Dim sData As String
Dim sReport As String
Dim lMaxRows As Long
Dim lDataFirstRow As Integer
Dim lOffsetAt As Long

    sData = "Data"
    sReport = "Report"
    lDataFirstRow = 2
    
    Sheets(sData).Select
    
    Application.CutCopyMode = False
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    If (lMaxRows < lDataFirstRow + 1) Then Exit Sub
    
    On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(sReport).Delete
        Application.DisplayAlerts = True
        Err.Clear
    On Error GoTo 0
    
    Sheets.Add
    ActiveSheet.Name = sReport
    
    Sheets(sData).Select
    Cells(lDataFirstRow, "F") = "Facing"
    
    Application.CutCopyMode = False
    With Range(Cells(lDataFirstRow + 1, "F"), Cells(lMaxRows, "F"))
        .FormulaR1C1 = "=RIGHT(RC5, 4)"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    
    
    With Range(Cells(lDataFirstRow, "G"), Cells(lMaxRows, "G"))
        .FormulaR1C1 = "=RC1 & ""|"" & RC2 & ""|"" & RC6 & ""|"" & RC3"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    
    
    With Range(Cells(lDataFirstRow, "H"), Cells(lMaxRows, "H"))
        .FormulaR1C1 = "=RC6 & ""|"" & RC2 & ""|"" & RC1 & ""|"" & RC3"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    
    
    Range("G" & lDataFirstRow & ":G" & lMaxRows).Select
    Range("G" & lDataFirstRow & ":G" & lMaxRows).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I" & lDataFirstRow), Unique:=True
    
    Range("I" & lDataFirstRow & ":I" & lMaxRows).Copy
    
    Sheets(sReport).Select
    Range("A1").PasteSpecial
    Range("H1").PasteSpecial
    Application.CutCopyMode = False
        
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    With Range(Cells(lDataFirstRow, "E"), Cells(lMaxRows, "E"))
        .FormulaR1C1 = "=SUMIF('" & sData & "'!C7:C7,RC8, '" & sData & "'!C4:C4)"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
   
    With Range(Cells(lDataFirstRow, "I"), Cells(lMaxRows, "I"))
        .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC8, '" & sData & "'!C7:C8,2,false)), ""No Offset Yet"", VLOOKUP(RC8, '" & sData & "'!C7:C8,2,false))"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    
    With Range(Cells(lDataFirstRow, "J"), Cells(lMaxRows, "J"))
        .FormulaR1C1 = "=IF(ISERROR(MATCH(RC8, C9:C9,0)),0,MATCH(RC8, C9:C9,0))"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    
    Columns("A:A").Select
    Selection.TextToColumns _
            Destination:=Range("A1"), _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
            TrailingMinusNumbers:=True
    
    Cells.Select
    Cells.Sort _
        Key1:=Range("D2"), order1:=xlAscending, _
        Key2:=Range("B2"), order2:=xlAscending, _
        Key3:=Range("A2"), order3:=xlAscending, _
        Header:=xlYes
        
    
    Do While (lMaxRows > 1)
        
        lOffsetAt = Cells(lMaxRows, "J")
        
        If ((Cells(lMaxRows, "D") <> Cells(lMaxRows + 1, "D")) And (Cells(lMaxRows + 1, "J") <> "")) Then
            
            Rows(lMaxRows + 1).Insert
        End If
        
        If lOffsetAt > 0 Then
            Cells(lOffsetAt, "F") = Cells(lMaxRows, "E")
            Cells(lOffsetAt, "J") = 0
            Rows(lMaxRows).Delete
        End If
        
        
        lMaxRows = lMaxRows - 1
    
        
    Loop
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    With Range(Cells(2, "G"), Cells(lMaxRows, "G"))
        .FormulaR1C1 = "=RC5 + RC6"
    End With
    
    Range("E:G").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    Range("H:J").Delete
    
    Range("A1") = "Code"
    Range("B1") = "Acct"
    Range("C1") = "booked in"
    Range("D1") = "Curr"
    Range("E1") = "amount"
    Range("F1") = "amt2"
    Range("G1") = "Variance"

    Sheets(sData).Select
    Range("G:I").Delete
    
   
End Sub
Jack
Jul 26, 2010 at 04:55 PM
many thanks for this..i have been trying this code for a while but "run error" is coming. It doesnt give the desired report.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 26, 2010 at 07:43 PM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too