Comparing two Excel Files - Copying Matching Data to new Worksheet

[ctackett6407]

Greetings,

I had been working on trying to get this idea to work and I woke up this morning and my computer crashed and I lost the file and the website I was looking at to learn how to do it, so I'm comping here for advice.

Each morning I run a report and it lists all the accounts owed and then later in the day I run the same report and if any payments were made during the day (The paid accounts will not be on the 2nd report) then I have to go through and remove them from the prior report and then note how many accounts paid in that time frame.

Is there a way I can just generate my report in the morning and then compare it to the generated report in the evening and on a new worksheet it show all the accounts that are not paid (show up on the 2nd report) and then have a line that tells me how many accounts have paid between the two reports?

Any help is great.

I had a site that I found that didn't have exactly this, but I had something going, but woke up and my computer crashed or reported or something.. and I lost it.

 

[ctackett6407]

Greetings

I tried to edit this post but was unable to.

Here is an example of what I'm trying to achieve.

I looked at match and vlookup but keep getting an error stating that what I'm trying to do looks like a formula (which it is) and then shows = and - at the start of my formula, which I thought it should be =..

Anyway,

I have one excel that has 8 headers and a lot of data in each column.
I have a second excel that has the same headers and a lot of data in each column.

I am trying to compare the two excel sheets and if the account number shows up in column A1 of both Sheet 1 and Sheet 2 .. then that means that person hasn't paid their bill and I want that entire row to be copied to Sheet 3.

People pay their bills daily and this will let me see the people who owned from the start of the day and who paid throughout the day, and the sheet 3 will show me whose left to pay their bills.

Any thoughts would be great! I'm not sure if this is a Macro or if Excel can do this with a formula.

 

[vcoolio]

Hello CTackett,

See if the following code does the task for you:-

Sub Test()

Dim lr As long
Dim fValue As Range
Dim c As Range

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Sheet3.UsedRange.Offset(1).ClearContents

For Each c In Sheet1.Range("A2:A" & lr)
Set fValue = Sheet2.Columns("A:A").Find(c.Value)
           If fValue Is Nothing Then GoTo NextC
           If c.Value = fValue.Value Then
           c.EntireRow.Copy
           Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
     End If
NextC:
Next c

Sheet3.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to a mock-up sample file that I've prepared for you:-

http://ge.tt/4UNctpp2

Click on the "RUN" button to see it work. You should be able to run the code from any sheet.

I hope that this helps.

Cheerio,
vcoolio.

 

[ctackett6407]

Thank you for this!

I added another line to this so it would copy the formatting from the original tab, which is very helpful!

I then went through each line because I only ran into one problem. I now export my data and ensure that the headers match on all the spreadsheets. When I run this it leaves a blank first line where the headers are and pushes all the formatting down 1 row so it's one row below the line that had the original formatting.

I went through each line of the coding and tried to see if something in there is pushing it down but I couldn't see it. I did learn that the End(3)(2) .. the (2) is spacing.. and the higher the number the more Row spacing is included.

Anyway, here is what I added and it seems to work, but I noticed the formatting is off a row.

Sub CompareColumns()


Dim lr As Integer
Dim fValue As Range
Dim c As Range


lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row


Application.ScreenUpdating = False


Sheet3.UsedRange.Offset(1).ClearContents


For Each c In Sheet1.Range("A2:A" & lr)
Set fValue = Sheet2.Columns("A:A").Find(c.Value)
           If fValue Is Nothing Then GoTo NextC
           If c.Value = fValue.Value Then
           c.EntireRow.Copy
           Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
           Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlFormats
     End If


NextC:
Next c


Sheet3.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

 

[vcoolio]

Hello CT,

In the following line of code:-

Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlFormats

Remove the (2). This should adjust the row problem for you.

Another method to try would be to change the following two lines:-

Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlFormats

to the following single line:-

Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll

This will paste everything to the 3rd sheet: values, formats, formulae etc..

Create a copy of your workbook and test all methods first before deciding which best suits your actual workbook.

I hope that this helps.

Cheerio,
vcoolio.

 

[ctackett6407]

Oops this was intended to be a message, but I'll update it and fix that.
.. I need to update this with a question.