• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Analysis.

S

Sarfraz Khan

Member
Dear Sir,

This excel sheet has got two tabs List A is old one and List B new one and I want to do the following.

1) I want to compare both In voices eg: I want to check whatever Invoices has been paid from List A should be deleted using Excel or VBA.


2) Whatever Invoices are new in List B should be highlighted using Excel or VBA.



3) I want data on separate sheet Alphabetically eg: please visit w sheet to see the format in which I want data.


Please help.
 

Attachments

Hi:

I have written a formula to highlight new invoices using conditional formatting and have coded for splitting the customers in to various tabs. I am not sure on how you decide whether a invoice is paid or not could you please elaborate a bit more on this. Find the attached.

Thanks
 

Attachments

dear sir,

thanks for your reply, but When click on split button error is coming.

Also note, when I will compare two list Invoices which available in both sheet are unpaid, invoices available in LIST A but not available in LIST B is paid, and Invoices are Available in LIST B but not available in LIST A are the new Invoices.
 
Hi:

What error you are going to getting, the macro is running fine at my end .

In the List B tab the flag column will give you the comparison, rows with numbers are available in both List A & B and the rows with flag "New" is available only in List B. If you can replicate this formula in List A tab you can identify the paid invoices as well. Let me know with questions.

Thanks
 
Hi:

What does the error say, without knowing what the error is I won't be able to give you a solution. I am using excl 2010 and it is running fine at my end.

Thanks
 
Hi:

Try executing the code step-by-step, by pressing function key F8 in VBA editor and identify the line which gives you the error, this way you will be more clear where the error is. My first thought is that the error is because of the changes in the versions of excel considering it is running perfectly well at my end.

Thanks
 
Hi:

Try the following code and let me know.

Code: 
Sub SplitCus()

Application.ScreenUpdating = False
Dim i As Long
For i = 2 To Sheet2.Cells(Rows.Count, "Q").End(xlUp).Row
    With Sheet2
        .Range("A1:J1").AutoFilter
        .Range("A1:J1").AutoFilter Field:=5, Criteria1:=.Range("Q" & i)
        .Range("A1:I" & Sheet2.Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        Sheets.Add(After:=Sheet2).Name = .Range("Q" & i)
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End With
Next
Sheet2.ShowAllData
Application.ScreenUpdating = True

End Sub

Thanks
 
dear sir,

just one change in the sheet i wanted, whatever invoices are available in list A and coming new in list B we treate it as new, " but whatever Invoices was available in LIST A and now its not there in LIST B will be TREATED AS APID, what would be the formula.
 

Attachments

You must log in or register to reply here.
Back
Top