wilkisa
Well-known Member
- Joined
- Apr 7, 2002
- Messages
- 657
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
I'm better with formulas than VBA so I'm looking for help. I get a .txt file from our company's bank on the first of each month and use Text to Columns to break it into appropriate, filterable columns and save as .XLSX. I can save as .XLSM, no problem. I also have to identify most (but not all) of the items in COL A and that identity is entered into the same row of COL I. The statement is several thousand pages long so it takes a lot of time to check each item in it. I created a Nested IF statement for Col I and it has worked for some of the identities but it so far has 27 Nested IFs and there are many more to add.
For Example:
COL A
Central Air Vendor #112233
Cable Service Provider
Rental XXXXXX9876 Unit
Central Air Vendor #445566
Central Air Vendor #778899
Rental XXXXXX6543 Unit
Cell Phone Provider
In COL I, I have the following formula that works fine: =If(Left(A2,5)="Cable","Acct 5555-55",If(A2,4)="Cell","Acct 5555-53",If(Right(A2,7)="#112233","AB123",If(Right(A2,7)="#445566","CD456",If(Right(A2,7)="#778899","EF789",If(Mid(A2,8,10)="XXXXXX9876","#951753",If(Mid(A2,8,10)="XXXXXX6543","#258654","")))))))
Obviously this is a very busy formula and I have so much more to add to it. I have shortened what I'm actually using and am also using dummy data but the gist is here. I know I can modify any code you can provide to get me started. If you have more questions or need further clarification, please reach out.
Any help someone can give me will be very appreciated. Thank you in advance.
For Example:
COL A
Central Air Vendor #112233
Cable Service Provider
Rental XXXXXX9876 Unit
Central Air Vendor #445566
Central Air Vendor #778899
Rental XXXXXX6543 Unit
Cell Phone Provider
In COL I, I have the following formula that works fine: =If(Left(A2,5)="Cable","Acct 5555-55",If(A2,4)="Cell","Acct 5555-53",If(Right(A2,7)="#112233","AB123",If(Right(A2,7)="#445566","CD456",If(Right(A2,7)="#778899","EF789",If(Mid(A2,8,10)="XXXXXX9876","#951753",If(Mid(A2,8,10)="XXXXXX6543","#258654","")))))))
Obviously this is a very busy formula and I have so much more to add to it. I have shortened what I'm actually using and am also using dummy data but the gist is here. I know I can modify any code you can provide to get me started. If you have more questions or need further clarification, please reach out.
Any help someone can give me will be very appreciated. Thank you in advance.