hibbii
Jan 4 2007, 11:20 AM
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
Thorin
Jan 4 2007, 11:31 AM
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)
hibbii
Jan 4 2007, 12:07 PM
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?
Coldbrain
Jan 4 2007, 01:44 PM
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
hibbii
Jan 4 2007, 02:58 PM
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.
Coldbrain
Jan 4 2007, 03:23 PM
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
hibbii
Jan 4 2007, 03:50 PM
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.
Coldbrain
Jan 4 2007, 04:42 PM
That seems to be the same file I uploaded?
CB
Thorin
Jan 5 2007, 03:54 AM
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.
hibbii
Jan 5 2007, 09:27 AM
Thanks Andy and Coldbrain. I got it to work using your ideas.
hibbii
Jan 5 2007, 09:39 AM
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
Coldbrain
Jan 5 2007, 09:45 AM
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
hibbii
Jan 5 2007, 09:50 AM
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)
Coldbrain
Jan 5 2007, 09:55 AM
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
hibbii
Jan 5 2007, 09:59 AM
the accounts are just a 6 digit number 000000
Coldbrain
Jan 5 2007, 10:08 AM
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
Thorin
Jan 5 2007, 10:08 AM
Hibbii,
Does the attached give you what you want ?
The range named in the code would need to be adjusted to suit your purposes.
hibbii
Jan 5 2007, 10:48 AM
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.
hibbii
Jan 5 2007, 10:54 AM
Nevermind that last question I think I got it.
Thanks Again!
Thorin
Jan 5 2007, 10:56 AM
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.
hibbii
Jan 8 2007, 11:44 AM
Sorry guys, I am back again with another wrinkle.
I just realized in my trial balance that 2 other columns (Fund, Fiscal year) might also have instances where they are new. These 2 columns go hand in hand so I wanted to find a way to be able to add this into my code so that I can see if there is a fund/fiscal year combo that is new as well as the acct (which we have already determined).
Would I need to make a separate macro or could I just add this feature in the following code that I have.
Thanks again for all the help, I dont know where I would be without it.
CODE
Sub MissingAccountsList()
Dim iRow As Integer
Dim cl As Range
iRow = 1
Sheets("MissingAccounts").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
For Each cl In Worksheets("Drop TB here").Range("F2:F5000")
If cl.Value = 1 Then
Set acode = cl.Offset(0, -3)
Cells(iRow, 1).Value = acode
End If
iRow = iRow + 1
Next
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.