Hi,

I currently have a worksheet with the following columns (Fund, year, acct, and amount). This comes from a huge trial balance that I just copy and paste into excel and excel flows the data through. Every month this trial balance gets updated and there are additions made sometimes to the accounts.

What I want to do is set up some kind of formula to where excel could provide me with the newly added accounts (if any) for a period before I copy and paste over the old trial balance. This way I know exactly what new accounts I need to add in other worksheets in the file.

Any help with a formula with this. I could copy the new trial balance right next to the old one or in another worksheet in the workbook.

TIA

You could do a lookup on the new data to see if the account exists in the old data, any that didn't match could be flagged as Missing.

eg =IF(ISERROR(VLOOKUP(New Account,Old Account,1,FALSE)),"Missing",0)

I gave it a try and my formula looks like this: The H6 has the new account and the $C$2:$C$4116 is the array for the old data.

=IF(ISERROR(VLOOKUP(H6,$C$2:$C$4116,1,FALSE)),"Missing",0)

However, all I am getting is ) 0's but I know for sure that atleast 1 account is in the new data that is not in the old data. Am i doing it wrong?

If you are able to sort the list in the order you want the accounts to show up in your trial balance you could do this..

Add a column in front of the data (Column A)
In the first cell of column..
=1
In the second cell of the column..
=if(I3=I2,0, SUMPRODUCT((A$1:A1>0)*1)+1)
Drag that formula down to the end

Then on your Trial Balance Sheet in Column A just number 1 through 50 or a few more than the previous month's number of accounts..

Column B..
=VLookup(A2,DataSheet!$A$2:$I$50000,9,False)

Then your Trial Balance Sheet would automatically be updated..

Untested, hopefully no major flaws..

CB

What do you mean my trial balance will be automatically updated?

I get each trial balance in a separate spreadsheet which I just copy and paste over the old one. The rest of the spreadsheet is based on Vlookups for the account numbers. If the account number is new then the worksheets do not include them and this is w here my numbers get off.

I just want a way for excel to be able to identify the new accounts (if any).

CB, maybe your suggestion works, I just dont get how to set it up.

Yeah, I may have jumped ahead of what you were actualy asking for..

Attached is a small sample of what I am talking about.. see if it is useful to you..

CB

That will be helpful to me later on but currently I do not even need that much information on the data.

I am attaching a little example of what I need but I need excel to automate the process. Thanks for your help.

That seems to be the same file I uploaded?

CB

Hibbi,

Coldbrain is correct, if you overtype one of his account numbers (say in cell f9) with a new account number (say 9000), then this will automatically update to the summary page.

I have re-attached the spreadsheet with a new tab which demonstrates the formula that I was talking about.

Thanks Andy and Coldbrain. I got it to work using your ideas.

I actually had sort of a follow up question to this:

Now once I identify all the missing accounts is there a way to make a list of them in some separate cells away from the data so that I do not have to go through all the rows looking for "missing"?

TIA

You could use another Vlookup to return the value in the column you have showing "missing" or not.. but it also depends on how you solved the first issue.. What did you end up using?

CB

I used the formula Andy suggested and got it to work for me.

=IF(ISERROR(VLOOKUP(C24,'Current Period TB by FUND-FY'!A:A,1,FALSE)),C24,0)

What is the format of your account numbers?

IE:

111111
111-111

The result of this formula means the account number is repeated or False so you will get many False's for the same new account number if there are many transactions using this new account number?

CB

the accounts are just a 6 digit number 000000

Hmm, I could probably devise a formula but actually the easiest way is probably to set the autofilter on your data

Just highlight everything then go to Data>Filter>AutoFilter

You will then get a drop down and pick False from that column.

CB

Hibbii,

Does the attached give you what you want ?

The range named in the code would need to be adjusted to suit your purposes.

Andy,

I thing that will work, however I am having a hard time getting the ranges to coordinate in the module.

Any way you could show me where to make the changes based on the formula I used to determine the 0 or 1?

=IF(ISERROR(VLOOKUP(C2,'Current Period TB by FUND-FY'!A:A,1,FALSE)),1,0)

I also made a new worksheet names MissingAccounts like you had. C2 contains the new periods accts.

Nevermind that last question I think I got it.

Thanks Again!

In the module, in the line :-

For Each cl In Worksheets("LookupFormula").Range("c1:c250")

Replace the "c1:c250" with the range that contains the VLOOKUP formula, if it is in the column to the right of the new period accts ie column D then use that one.