RECONCILING DATA USING VB SCRIPT

[abdul.malik]

My Hello to all the respected members once again,

Attached exceL contanis Output & input sheets input sheet is my data sheets i wanted to generate the output sheet based on the input sheet by running a macro.

Thanks in advance to people who will help.

[enrand22]

mmmmmmm, i think it is easy but let me understand first....

you have a database where you extract the information and copy it in the INPUT SHEET

you want a copy paste macro to put that very same information in the OUTPUT SHEET or is there a condition??? would yo be more especific????

and last, if you want only the new records, we should use column b or g?

[abdul.malik]

HI,

My Condition is Column in B & C , Column G in my previous attachment was the combination of Column B & C

[abdul.malik]

Hi - First of all thanks for your quick reply.

Attachment in my above reply is self explanatory and i am sure it contains all the required information.

If you need further information please let me know.

[enrand22]

i am 99% sure this attached file is what you want, i have some minor problems for example, in the invoice column the values are not always equal so i desregard it.

please see if the attached file works for you.

[enrand22]

uuuuh oh... i dont know why doesnt let me attach the file so here is the vba:

Sub abdul()

Dim lastrow As String

lastrow = Cells(Rows.Count, 2).End(xlUp).Row

Sheets("OUTPUT FILE").Cells.ClearContents
Range("A1:F4").Copy
Sheets("OUTPUT FILE").Select
Range("A1").Select
ActiveSheet.Paste

Sheets("INPUT FILE ").Select

Range("A4:F" & lastrow).AutoFilter
ActiveSheet.Range("$A$4:$F$" & lastrow).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd

Range("A4:C" & lastrow + 1).SpecialCells(xlCellTypeVisible).Copy
Sheets("OUTPUT FILE").Select
Range("A4").Select
ActiveSheet.Paste

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Range("D5").FormulaR1C1 = "=SUMIF('INPUT FILE '!C2,RC[-2],'INPUT FILE '!C4)"
Range("E5").FormulaR1C1 = "=SUMIF('INPUT FILE '!C2,RC[-3],'INPUT FILE '!C5)"
Range("F5").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("G5").FormulaR1C1 = "=COUNTIF(R4C2:RC[-5],RC[-5])"
Range("D5:g5").AutoFill Destination:=Range("D5:g" & lastrow), Type:=xlFillDefault

Range("A4:g" & lastrow).AutoFilter
ActiveSheet.Range("$A$4:$G$" & lastrow).AutoFilter Field:=7, Criteria1:="2"
Rows("5:" & lastrow).Delete Shift:=xlUp
Rows("5:" & lastrow).AutoFilter

Columns("G:G").ClearContents
Columns("A:F").EntireColumn.AutoFit

Sheets("INPUT FILE ").Range("a1").AutoFilter

End Sub

[enrand22]

Attach:

[abdul.malik]

Hi Amat,

Thanks for your help the code is correct and it is working fine but what i need is if suppose we have just an input file the code should be in such a way that it shoud insert a new excel sheet with the name as output file in the same workbook and than it should reconcile the data available in input file and should give the result in output file.

I am sorry i am just a beginner and knowing very less about the coding but can understand the given code.

Thanks again for your help

[abdul.malik]

Hi Amrat look the attachment.

I have used your code adding something new but getting the run time error 1004.

If the error can be rectified and if the code will work i will achieve what i am expecting to achiveve

Thanks

[snb]

crosspost:

http://www.ozgrid.com/forum/showthread.php?t=176737&p=656032#post656032

[enrand22]

Hi Abdul... i had some problems finding why so many errors until i noticed you didnt create a Module to insert the SUB, i already created it, and it is working perfectly, with my macro and yours.

some other minor bug fixes and it makes what you want.

[abdul.malik]

HI Amrat,

Its done thanks for all your help.