Comparing info between worksheets

[LeeBrown.PineGrove]

I could really use some help with a macro. It has been several years
since I did any macro programming and I feel completely lost.

I have recently volunteered my services to the local waterboard to
help them with their books. They are really a mess. One Excel file
has a worksheet with all the original water accounts then a second
worksheet that has all the current billing information, this account
is always updated. The problem is that no one ever updated the Main
Accounts worksheet or otherwise known as the Original accounts
worksheet.


So here is the problem:

Worksheet that is named "Main accounts"
Columns A: Person's name, Column C: Account number Column E:
Address

Worksheet that is named "Current accounts"
Column A: Person's name, Column D: Account numbers Column G:
Address

What I was trying to do unsuccessfully was create a macro that would
go through the worksheet named "Main accounts" check the account
numbers in Column C (Account number column) and look at the name that
is in Column A for that account number.

It would then compare the name that is assigned to the account number
with the name in the worksheet "Current accounts" and if the name was
different it would then put the current name for that account number
on the Main accounts worksheet.

One extra part if possible would be to add the correct address from
the worksheet named "Current accounts" to the address column on the
"Main accounts" worksheet in column E.

One of my fears is that someone might have added accounts to the
Current worksheet and not include it on the Main worksheet. I would
really appreciate some help with this.

Also if you could recommend a website or book that would help me brush
up on my programming skills I would truly appreciate it.

Thank you
LeeLee

 

[Dave Peterson]

I don't think you need a macro to do this. I think you could use some helper
columns and some additional formulas.

For instance....

I'd do this:
In the Main Accounts worksheet

insert two new columns to the right of the name (new column B and C).

Put this in B1: Name From Current
Put this in C1: Name to Use
(they're just headers)

In B2:
=INDEX('Current Accounts'!A:A,MATCH(E2,'Current Accounts'!D,0))
(all one line)

This will return the name that matches that account number that's in E2 (shifted
to columns to the right, remember?). Or it will return an error if there is no
match.

Then in C2:
=if(iserror(b2),a2,b2)

If an error was returned, then use the old name (in A2). Otherwise, use the
current name that's in B2--even if it matches (or not!) what's in A2.

Then copy those formulas down as many rows as you need.

======
And the same thing for the addresses.
Insert a new column H and I (since things are shifting to the right!)
In H1: Address From Current
In I1: Address to Use

In H2:
=INDEX('Current Accounts'!g:g,MATCH(E2,'Current Accounts'!D,0))
In I2:
=if(iserror(h2),g2,h2)

And copy those down as far as you want.

Then I'd convert those formulas to values
Select B:C
edit|Copy
edit|Paste special|Values

And same with H:I

=========
Another thing you may want to check (if you're curious) is if the names (and/or
addresses) changed.

Just put another formula that compares A2:C2 (assuming that column B wasn't
deleted)
=A2=C2
(this'll return true if they match, False if they don't.)

============
If I were doing this as a macro, I'd essentially do the same steps.

 

[Guest]

the code coul;d be simplified a little bit. You don't need to test the
account names are different. Just copy the account name from Current Account
to Main Accounts.

Sub checkaccounts()

Sheets("Current accounts").Activate
LastRowCurrent = Sheets("Current accounts").Cells(Rows.Count, "D"). _
End(xlUp).Row

Set searchrange = Sheets("Current accounts"). _
Range(Cells(1, "D"), Cells(LastRowCurrent, "D"))

LastRowMain = Sheets("Main accounts").Cells(Rows.Count, "C"). _
End(xlUp).Row
Sheets("Main accounts").Activate
Set MainRange = Sheets("Main accounts"). _
Range(Cells(1, "C"), Cells(LastRowCurrent, "C"))

For Each cell In MainRange

Set c = searchrange.Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then

If Sheets("Main accounts").Cells(cell.Row, "A") <> _
Sheets("Current accounts").Cells(c.Row, "A") Then

Sheets("Main accounts").Cells(cell.Row, "A") = _
Sheets("Current accounts").Cells(c.Row, "A")

End If
Sheets("Main accounts").Cells(cell.Row, "E") = _
Sheets("Current accounts").Cells(c.Row, "G")

End If


Next cell