I have one sheet with original data. I have to compare the account numbers in
this sheet with 8 other sheets(within the same file) to see how the accounts
are performing. For instance,for the first quarter(or the second sheet) if
the account is past due I want that information carried to the 1st sheet and
if there are no changes do nothing. I have to compare them for 7 quarters so
I have to compare 8 sheets. Ulitmately if I started with 5000 accounts,
regardless if 1000 paid off I want my first sheet to show me the history for
the 8 quarters with the most recent history for that particular account. A
kink in this is there are columns in the first sheet that I don't want to
change, account number, origination date, etc. About 13 columns. Where do I
begin!! I have done vlookup before but would prefer to have a macro in
visual basic that would be quick and efficient. Is this possible?

Yes, a Macro would be most efficient I would say. I think you will need to
use a find method in the code, to lookup the account numbers, then paste the
information from the 7 other sheets back to the first. I don't think it
would be too difficult at all. I could send you a sample if you want to get
you started.

Kou

If I understand correctly, you simply want all your overdue accounts for
each of the past 8 quarters to be summarized on one sheet? Are you able
to provide more detail on how each of the sheets are set up?

Marie wrote:
I have one sheet with original data. I have to compare the account numbers in
this sheet with 8 other sheets(within the same file) to see how the accounts
are performing. For instance,for the first quarter(or the second sheet) if
the account is past due I want that information carried to the 1st sheet and
if there are no changes do nothing. I have to compare them for 7 quarters so
I have to compare 8 sheets. Ulitmately if I started with 5000 accounts,
regardless if 1000 paid off I want my first sheet to show me the history for
the 8 quarters with the most recent history for that particular account. A
kink in this is there are columns in the first sheet that I don't want to
change, account number, origination date, etc. About 13 columns. Where do I
begin!! I have done vlookup before but would prefer to have a macro in
visual basic that would be quick and efficient. Is this possible?

I would love it if you could send me a sample. This probably sounds confusing
on what I need. I will try to explain a little bit better. The first quarter
my company buys accounts receivable and scores thiem by risk. There are
numerous fields that identify the criteria. To test to see if we are scoring
them correctly we would like to look back and see why we made the decision to
buy it and if it was a good decision. So in my first sheet, as I said, if I
started with 5000 accounts after 8 quarters I still want to have 5000
accounts, regardless of whether they were paid off. So I can look at them and
see how we scored them. All information from column A-AK will never change,
columns AL-BA would have information, times past due- etc that would change
during the life of the account. (It's a pretty big file - which is one reason
I need the macro)The common field in every quarter (or sheet) would be the
note number, which I have in column B, starting in row 2 - which will be the
same column and row on every sheet . I want it to find this number and if
it is still on the newest sheet copy the data (from AL-BA, or if there is a
better way to see if something has changed??)and carry the new information to
the first sheet if not do nothing. So every quarter I can download the newest
quarter, run the macro and it would update the account. Like I said something
I am having trouble with is I have to make sure if the original month started
with 5000 accounts at the end of the 8th quarter I still have to have 5000
accounts. Make sense??
Is this possible?

"Kou Vang" wrote:

Yes, a Macro would be most efficient I would say. I think you will need to
use a find method in the code, to lookup the account numbers, then paste the
information from the 7 other sheets back to the first. I don't think it
would be too difficult at all. I could send you a sample if you want to get
you started.

Kou

I have thought about your question for a while. I believe the easiest route
to go would be to write a macro that just simply copies and pastes a Vlookup
formula into cells AL:BA into the sheet every quarter. That way, you are
guaranteed to have 5,000 records before and after, and not have to do any
copying or writing of formulas by hand. IT should be pretty simple. Let me
know if you need help with this.

Kou

exp: (Similiar to this code)

With Worksheets("Original").activate
range("al2").forumla= "=vlookup(b2,'quarter!'al2:'quarter!'ba2,1,fal se)
range("al2").select
selection.copy
range("am2:ba2").pastespecial xlpasteformulas
Application.cutcopymode = false
range("al2:ba2").select
selection.autofill destination:=range("al2:ba5000"), type:=xlfilldefault
end with