Finding contra entries using amount & text in vba

[venkat5652]

Hi all,

need vba code for one scenario for contra entries with concatenation of text amt. Pls help with your ideas ASAP

scenario input :

Amt text
-250,000 REVERSAL PROVISION
250,000 REVERSAL PROVISION
-250,000 REVERSAL PROVISION
-250,000 REVERSAL PROVISION
-150,000 PROVISION REVERSAL
150,000 PROVISION REVERSAL
150,000 PROVISION REVERSAL
-150,000 PROVISION REVERSAL
150,000 PROVISION REVERSAL

Expected output:

Amt text
-250,000 REVERSAL PROVISION
250,000 REVERSAL PROVISION
-150,000 PROVISION REVERSAL
150,000 PROVISION REVERSAL
150,000 PROVISION REVERSAL
-150,000 PROVISION REVERSAL

 

[Derek McGill]

Can you expand on the logic which gives you expected output ?

 

[Marc L]

Hi !

I found the logic but I didn't see any sample attachment as per forum rules
neither a crystal clear explanation for the destination output area !
​

 

[venkat5652]

I had attached the data in the file input/output file. i am in need of data that matched entries as i said earlier in one Sheet and unmatched in one sheet.

consider T(amount in local currency) and U(text) columns combination

 

[Marc L]

Your attachment lost me !
I can't understand why row #75 must be in output worksheet …
If it is not an error, so I did not catch the logic !
​

 

[venkat5652]

Sorry Marc,

attached updated file, pls help me. trying the logic for two weeks to find out but failed.

 

[Marc L]

Ok, it was an error, I catched the child level logic !​

Sub Demo()
         Sheet2.UsedRange.Offset(1).Clear
    With Sheet1.UsedRange.Rows
            If .Parent.FilterMode Then .Parent.ShowAllData
        For R& = 2 To .Count
            If .Cells(R, 20).Value + .Cells(R + 1, 20).Value = 0 Then
                L& = L& + 2
                .Item(R).Resize(2).Copy Sheet2.Cells(L, 1)
                R = R + 1
            End If
        Next
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[venkat5652]

Hi Marc,

your code is working upto 90 %. thanks a lot. but in the attached file highlighted rows are error, it is showing in sheet2. I mean that there are different data in "Text" column(row 1 & row 2). can you pls tweak the code? and also i want to remove those rows in sheet1

 

[Marc L]

Try this :​

Sub Demo()
     Sheet2.UsedRange.Offset(1).Clear
     Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
        If .Parent.FilterMode Then .Parent.ShowAllData
        R& = 2
    Do Until .Cells(R, 1).Value = ""
        If .Cells(R, 20).Value + .Cells(R + 1, 20).Value = 0 Then
            If .Cells(R, 22).Value = .Cells(R + 1, 22).Value Then
                L& = L& + 2
               .Item(R).Resize(2).Copy Sheet2.Cells(L, 1)
               .Parent.Rows(R).Resize(2).Delete
            Else
                R = R + 1
            End If
        Else
            R = R + 1
        End If
    Loop
End With
     Application.ScreenUpdating = True
End Sub

You may Like it !
​

 

[venkat5652]

Hi Marc,

thanks a lot. Need help in one more scenario. i want to select n-number of visible rows in a filtered data. can you pls help me?

 

[Marc L]

Maybe with a crystal clear explanation …
As a good code does not need to select anything !

See SpecialCells method in VBA inner help.
​

 

[venkat5652]

Mean that i want to paste n-number of visible rows to another sheet in a filtered range

 

[Marc L]

As Copy method takes only visible cells, you can use the global range …​

 

[venkat5652]

Hi Marc,
Need help again.

need to convert string value to date.

from "Wednesday, Jan, 06, 2016" to - 01/06/2016(mm/dd/yyyy) format

in vba code or excel formula, anything is fine.

 

[Derek McGill]

If they are actually true dates, just change the cell format and the display should change.

 

[venkat5652]

Hi team,

little bit urgent pls

need excel formula to extract the data from "Cotton - UT" sheet to "Data" sheet based on respective date and count and time from "Cotton - UT" sheet.

attached the file for your reference