PDA

View Full Version : [SOLVED] Need help to transform one spreadsheet into another form of spreadsheet.



datvu
09-07-2016, 12:36 PM
Hello,
I am new to VBA and I am trying my best to learn from it. So here is my problem. I work as an intern staff accountant and I have to upload a lot of general journal transactions from a excel report into quickbook manually. I figure out I can do this more efficiently if I can transform the excel report into the quickbook format and upload the whole batch into quick book.

So on the attached file there are three tab. The "Transactions" tab is the report I received. I want to transfer this into the format of the "quickbook" tab. So basically I want to transfer the values in Settlement Date, Type, Description and Amount USD from "Transactions" tab to DATE, MEMO, ACCNT, and AMOUNT in "Quickbook" tab. Note that the Description is different from ACCNT name, so I have a chart in tab 3 "Name Chart" where you can link the Description and turn it into according ACCNT name. The value in AMOUNT is times negative 1 of Amount USD.

In short, using the "Transaction" tab and the "Chart Name" tab, how can I use them to generate the "Quick book" tab. It is a little complicate and that is why I need your help. Thank you so much.

Paul_Hossler
09-07-2016, 01:07 PM
I assume that you really want a CSV or some kind of flat file to import into QuickBooks and not a new worksheet?




!TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,CLASS,AMOUNT,DOCNUM,MEMO
!SPL,SPLID,TRNSTYPE,DATE,ACCNT,CLASS,AMOUNT,DOCNUM,MEMO
!ENDTRNS,,,,,,,,
TRNS,,GENERAL JOURNAL,9/12/2016,Investments - Private Equity:J P Morgan - Private Equity:AG OPP Whole Loan Select,,"110,000.00",,Capital Call
SPL,,GENERAL JOURNAL,9/12/2016,Investments - Private Equity:J P Morgan - Private Equity:Cash - Brokerage Q Account,,"-110,000.00",,
ENDTRNS,,,,,,,,
TRNS,,GENERAL JOURNAL,9/1/2016,Investments - Private Equity:J P Morgan - Private Equity:J C Flowers III,,"-22,195.84",,Cash Distribution
SPL,,GENERAL JOURNAL,9/1/2016,Investments - Private Equity:J P Morgan - Private Equity:Cash - Brokerage Q Account,,"22,195.84",,
ENDTRNS,,,,,,,,

datvu
09-07-2016, 01:13 PM
I can save the worksheet with the name ending in .iff under .txt file and quickbook will be able to pick it up as iff file. My question is how can you generate the "quickbook" tab using the "transaction" tab and the "Chart name" tab. The "quickbook" tab is the end result that I want to have using macro.

SamT
09-07-2016, 05:02 PM
Did you know that you have a trailing space in the "Transactions " tab name? This code uses that tgrailing space, so if you fix one, fix the other.

Option Explicit

Sub TansActions2QuickBook()


'Transactions columns
Const SettleDateCol As String = "C"
Const TypeCol As String = "G"
Const DescCol As String = "H"
Const USDAmtCol As String = "U"

'QuickBook Columns
Const DateCol As String = "D"
Const MemoCol As String = "I"
Const AcctCol As String = "E"
Const AmmtCol As String = "G"

Dim TransSht As Worksheet
Dim TransRow As Long

Dim QBSht As Worksheet
Dim QBRow As Long

Set TransSht = Sheets("Transactions ")'<<<<<<<<<<<<<Trailing Space
Set QBSht = Sheets("QuickBook")

QBRow = QBSht.Cells(Rows.Count, DateCol).End(xlUp).Row + 1

With TransSht
For TransRow = 2 To .Cells(Rows.Count, SettleDateCol).End(xlUp).Row

QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol)
QBSht.Cells(QBRow, MemoCol) = .Cells(TransRow, TypeCol)
QBSht.Cells(QBRow, AcctCol) = GetAcct(.Cells(TransRow, DescCol))
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol) * -1

QBRow = QBRow + 1
Next TransRow
End With

End Sub

Private Function GetAcct(Desc As String) As String
Dim WsF As WorksheetFunction
Set WsF = Application.WorksheetFunction

Dim VLTable As Range
Set VLTable = Sheets("Description Name").Range("A:B")

GetAcct = WsF.VLookup(Desc, VLTable, 2)

End Function



Use this line to transfer the date across as text

QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text

Paul_Hossler
09-07-2016, 05:22 PM
I can save the worksheet with the name ending in .iff under .txt file and quickbook will be able to pick it up as iff file. My question is how can you generate the "quickbook" tab using the "transaction" tab and the "Chart name" tab. The "quickbook" tab is the end result that I want to have using macro.

I was suggesting it'd be easier to just create the .iif file directly without the hassle of making another tab, and then manually or macroly saving that worksheet as a .iif file

datvu
09-12-2016, 07:31 AM
Thank you SamT and Paul_Hossler for all your hep. Due to my schedule I haven't been able to test it out yet. I will try to do it today and let you guys know about the result. Once again I am very appreciate your help.

datvu
09-12-2016, 09:12 AM
So I have try the code and did some twerk to it. I want my end result to be like the "QuickBook Final Result" Tab. However, I am clueless about how to add row 5 and 6 into "Quickbook" tab like in the "QuickBook Final Result" tab. And also, if the "Transaction", "Desription Name", and "Quickbook" are three separate worksheet, how can I code it so that I have the final worksheet? Thank you.

Here is the code I have right now:

Sub TansActions2QuickBook()

'Transactions columns
Const SettleDateCol As String = "C"
Const TypeCol As String = "G"
Const DescCol As String = "H"
Const USDAmtCol As String = "U"

'QuickBook Columns
Const DateCol As String = "D"
Const MemoCol As String = "I"
Const AcctCol As String = "E"
Const AmmtCol As String = "G"
Const TrnstypeCol As String = "C"
Const TrnsCol As String = "A"
Dim TransSht As Worksheet
Dim TransRow As Long

Dim QBSht As Worksheet
Dim QBRow As Long

Set TransSht = Sheets("Transactions")
Set QBSht = Sheets("QuickBook")

QBRow = QBSht.Cells(Rows.Count, DateCol).End(xlUp).Row + 2

With TransSht
For TransRow = 2 To .Cells(Rows.Count, SettleDateCol).End(xlUp).Row
QBSht.Cells(QBRow, TrnsCol).Value = "TRNS"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
QBSht.Cells(QBRow, MemoCol) = .Cells(TransRow, TypeCol)
QBSht.Cells(QBRow, AcctCol) = GetAcct(.Cells(TransRow, DescCol))
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol) * -1



QBRow = QBRow + 3
Next TransRow
End With

End Sub

Private Function GetAcct(Desc As String) As String
Dim WsF As WorksheetFunction
Set WsF = Application.WorksheetFunction

Dim VLTable As Range
Set VLTable = Sheets("Description Name").Range("A:B")

GetAcct = WsF.VLookup(Desc, VLTable, 2)

End Function

SamT
09-12-2016, 09:48 AM
Column A, "TRNS" and ENDTRNS" are esy. Hoow do we kow wht in particular goes in Column C

First add this Row to Sheet Description Name:


JPMorganEquityCash

Investments - Private Equity:J P Morgan - Private Equity:Cash - Brokerage Q Account



Study this Code

With TransSht
For TransRow = 2 To .Cells(Rows.Count, SettleDateCol).End(xlUp).Row

QBSht.Cells(QBRow, TrnsCol).Value = "TRNS"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
QBSht.Cells(QBRow, MemoCol) = .Cells(TransRow, TypeCol)
QBSht.Cells(QBRow, AcctCol) = GetAcct(.Cells(TransRow, DescCol))
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol) * -1

QBRow = QBRow + 1
QBSht.Cells(QBRow, TrnsCol).Value = "SPL"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
'QBSht.Cells(QBRow, MemoCol)
QBSht.Cells(QBRow, AcctCol) = GetAcct("JPMorganEquityCash")
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol)

QBRow = QBRow + 1
QBSht.Cells(QBRow, TrnsCol).Value = "ENDTRNS"

QBRow = QBRow + 1
Next

datvu
09-12-2016, 10:10 AM
Column C will always be "General Journal". Sorry if i did not make that clear at the beginning. Everything work great. Thank you so much for helping me. I am very new to vba so seeing code help me to learn and understand faster. I will try to figure out how to apply this code so that rather than having three tabs under 1 worksheet, I can use these tabs as several individual worksheet to combine and create a final one. Once again thank you so much.

SamT
09-12-2016, 11:14 AM
You are welcome.

I tried to make the code easiest to understand, rather than most efficient.

There are subtle hints in that code that you should think about.

datvu
09-12-2016, 12:41 PM
I just realized this after trying to apply the code into my work. When i put the sample file in here, in the Descriptions column of "Transactions" tab, each cell description has a specific date in it. I forgot that it is the description for this month.

So if I use the transactions report from another month, then the number would change. This would then make me to redo the work in "Description Name" as I have to identify again which description name is linked with which account name.

How do I use the IF command to look up certain words in cell of "Transactions" tab and then replace it the account name in "Description name" tab into the cell of "QuickBook" tab. This mean we have to change the Private function in the previous coding. Sorry for wasting all the work :(

Example: If cell H2 of "Transactions" tab contains "AG OPP" words, then in "QuickBook" tab under ACCNT it would change into "Investments - Private Equity:J P Morgan - Private Equity:AG OPP Whole Loan Select".

Thank you


Current Code that wont work

Sub TansActions2QuickBook()

'Transactions columns
Const SettleDateCol As String = "C"
Const TypeCol As String = "G"
Const DescCol As String = "H"
Const USDAmtCol As String = "U"

'QuickBook Columns
Const DateCol As String = "D"
Const MemoCol As String = "I"
Const AcctCol As String = "E"
Const AmmtCol As String = "G"
Const TrnstypeCol As String = "C"
Const TrnsCol As String = "A"
Dim TransSht As Worksheet
Dim TransRow As Long

Dim QBSht As Worksheet
Dim QBRow As Long

Set TransSht = Sheets("Transactions")
Set QBSht = Sheets("QuickBook")

QBRow = QBSht.Cells(Rows.Count, DateCol).End(xlUp).Row + 2

With TransSht
For TransRow = 2 To .Cells(Rows.Count, SettleDateCol).End(xlUp).Row
QBSht.Cells(QBRow, TrnsCol).Value = "TRNS"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
QBSht.Cells(QBRow, MemoCol) = .Cells(TransRow, TypeCol)
QBSht.Cells(QBRow, AcctCol) = GetAcct(.Cells(TransRow, DescCol))
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol) * -1

QBRow = QBRow + 1
QBSht.Cells(QBRow, TrnsCol).Value = "SPL"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
QBSht.Cells(QBRow, AcctCol).Value = "J P Morgan CashInvestments - Private Equity:J P Morgan - Private Equity:Cash - Brokerage Q Account"
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol)
QBRow = QBRow + 1
QBSht.Cells(QBRow, TrnsCol).Value = "ENDTRNS"

QBRow = QBRow + 1
Next TransRow
End With

End Sub

Private Function GetAcct(Desc As String) As String
Dim WsF As WorksheetFunction
Set WsF = Application.WorksheetFunction

Dim VLTable As Range
Set VLTable = Sheets("Description Name").Range("A:B")

GetAcct = WsF.VLookup(Desc, VLTable, 2)

End Function

SamT
09-12-2016, 01:40 PM
It looks like the Descriptions are
String1 & " - " & Date & String2
And you want to look up the String2 to find the ACCNT.

Is that correct?

However it is done there must be a pattern to the Description and GetAcct can look up a section of the pattern in column A to return the ACCT in Column B.

If you want to try it yourself, here are some hints
Example DESC = "AG OPP - INVESTMENT - 09.12.2016 AG OPPORTUNISTIC WHOLE LOAN SELECT, L.P. ONSHORE"


Dim Both As Variant
Dim First As String
Dim Last As String
Dim TransDate as Date
Dim LookupVal As String

Both = Split(Desc, "-")
First = Trim(Both(1))
Last = Trim(Both(2))
TransDate = CDate(Left(Last, 10))
LookupVal = Trim(mid(Last, 11)))



Both = An Array, First value = "AG OPP - INVESTMENT___ ", Second Value = "_________09.12.2016mmmAG OPPORTUNISTIC WHOLE LOAN SELECT, L.P. ONSHORE". Note leading and trailing spaces present
First = "AG OPP - INVESTMENT". Note that the trailing space has been Trimmed
Last = "9.12.2016 AG OPPORTUNISTIC WHOLE LOAN SELECT, L.P. ONSHORE". Note the leading spaces have been Trimmed
TransDate = Date represented by the first ten characters of Last = 12 Sep, 16
LookupVal = "AG OPPORTUNISTIC WHOLE LOAN SELECT, L.P. ONSHORE". Note leading spaces Trimmed off


If the DESCs and ACCNTs are subject to change, consider what creates the most consistent Patterns and should you organize the Lookup Table(s) by First, Last, or by Date

datvu
09-14-2016, 07:46 AM
While some of them started wit String1 & "-" & Date & String2, other just end after Date. I was thinking about how to look up the first part of the Description and then use those to linked with the same words/ phrase to get ACCNT name. Lets me give it a try using your hint. If I cannot figure it out, then once again I am in need of your help. Thank you SamT

datvu
09-14-2016, 09:27 AM
I am getting stuck here as I try to look up a way so that I can look up a certain words in a cell and then change the whole cell with a whole new value that corresponded with that selected words.

Example in her should be I want to select "AG OPP" from "AG OPP - INVESTMENT - 09.12.2016 AG OPPORTUNISTIC WHOLE LOAN SELECT, L.P. ONSHORE" then change the whole thing into "Investments - Private Equity:J P Morgan - Private Equity:AG OPP Whole Loan Select".

I think I can create a Lookup Table based on the certain words and their ACCNT name. Could you help me with this?
17082

SamT
09-14-2016, 11:29 AM
Oops! I messed up. Sometimes the DESC has two dashes (-)
Both = An Array, First value = "AG OPP ", Second Value = " INVESTMENT___", Third Value = "_________09.12.2016mmmAG OPPORTUNISTIC WHOLE LOAN SELECT, L.P. ONSHORE".

ISArray(Both) = False Means Both only contains one String and is not an Array
UBound(Both) = 1 means two values in the array
UBound(Both) = 2 means three values in the array


----------------------------------------------------------------------------------------------------


Attach a sheet containing:
In Column A: the list of DESCs containing the Term. Ex: "AG OPP - INVESTMENT - 09.12.2016 AG OPPORT . . ."
In Column B: The Terms to look up for each DESC. Ex: "AG OPP"

In Column D: The Terms
In Column C: The List of lookups

C & D should look like A & B in your first screenshot above.


------------------------------------------------


Maybe we can see a Pattern where you don't.

See next post

SamT
09-14-2016, 11:38 AM
Right now, I am thinking something like.


dim Terms as range
set Terms = list of terms column

dim Cel As range

for each Cel in Terms
if Cel = "" then goto NextCel
if instr(DESC, Cel) > 0 then ACCNT = Cel.offset(, 1)

NextCel:
next

datvu
09-14-2016, 11:55 AM
You mean something like this ? It is really hard to decide a pattern based on the first X number of characters in side each of Description cell because some are long while some are short. I think we have to list out each one with a code say change this "selected term" into the corresponding Account Name.

SamT
09-14-2016, 01:23 PM
Exactly like that. But, as I understand it there will be a lot of empty spaces in Column F TERMS, and Column G ACCNT will have many more items


I think we have to list out each one with a code say change this "selected term" into the corresponding Account Name.

As long as the TERMs are unique, a function something like the last code I posted can work quickly.

Something like

ACCNT = GetAcct(DESC)


Private Function GetAcct(DESC AS String) As String
GetAcct = Something like my code from above
End Function


Although if speed is a factor, then setting the TERMS + ACCNT ranges to an Array is faster

If LOOKUPS is the array, GetAccts partial code is

For i = Lbound(LOOKUPS) to UBOUND(LOOKUPS)
If LOOKUPS(i, 1) = "" Then GoTo NextI

If InStr(DESC, LOOKUPS(i, 1)) > Then
GetAcct = LOOKUPS(i, 2)
Exit Function
End If

NextI:
Next i

I think that speed will only be a factor if the number of Transactions times the number of Rows in the ACCNT lookup table is greater than a few thousand

datvu
09-16-2016, 12:18 PM
We normally have less than a thousand transactions for a month so I think the normal code is working fine. However, I will also try to look at the Array and learn it. Thank you for your help for the past two weeks. At this point I believe my request is solved. Once again thank you so much. This mean a lot for me as an intern who is able to put some value for the place I am working right now.