I have a template for journal entries that I need to get into a different format. Right now I'm writing formulas that are working, but I cannot drag down so I'm thinking there might be a vba solution.
There are 6 columns, B:G, account, center, debit, credit, company, plant.
account and center must be 10 characters long (accounts are 5 characters but I add spaces to make up the difference), debit and credit are 17 characters long, and comany and plant are 3 characters each.
If there is an amount in the credit column I need it to be changed to the number and - at the end (384- for example.)
If debit AND credit are both blank or 0 I want them to be excluded.
If possible, I would like a blank line between every 4 entries.
So what I'm trying to do is extract the account with the spaces in one cell (so if the account is 10100 the cell would contain "10100 " without the quotes), lets say column I, the center in column J, and debit or credit in K, with the company and plant concatenated under the center with leading zeros.
Something like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]acct[/TD]
[TD]center[/TD]
[TD]dr[/TD]
[TD]cr[/TD]
[TD]co[/TD]
[TD]plant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]4200.6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]4200.6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10100[/TD]
[TD]53102[/TD]
[TD]19142.82[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]001001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]62252[/TD]
[TD]51204[/TD]
[TD][/TD]
[TD]3413.62[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]10100[/TD]
[TD]53102[/TD]
[TD]19142.82[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]22582[/TD]
[TD]23502[/TD]
[TD]677.19[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]62252[/TD]
[TD]51204[/TD]
[TD]3413.62-[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]2614.23[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]001007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]53102[/TD]
[TD]51201[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]47[/TD]
[TD][/TD]
[TD]22582[/TD]
[TD]23502[/TD]
[TD]677.19[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]22100[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]432.20[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]001008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]22170[/TD]
[TD]1[/TD]
[TD]300.88[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]22165[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]611.31[/TD]
[TD]1[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]2614.23[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22100[/TD]
[TD]1[/TD]
[TD]432.20-[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001050[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22170[/TD]
[TD]1[/TD]
[TD]300.88[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22165[/TD]
[TD]1[/TD]
[TD]611.31-[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001051[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
And so on... right now I have the following formulas
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]I2[/TD]
[TD]=B2&REPT(" ",10-LEN(B2))[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=C2&REPT(" ",10-LEN(C2))[/TD]
[/TR]
[TR]
[TD]K2[/TD]
[TD]=IF(D2>0,D2&REPT(" ",17-LEN(D2)),E2&"-"&REPT(" ",16-LEN(E2)))[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[TD]=REPT("0",3-LEN(F2))&F2&REPT("0",3-LEN(G2))&G2[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be appreciated!
Thanks!
There are 6 columns, B:G, account, center, debit, credit, company, plant.
account and center must be 10 characters long (accounts are 5 characters but I add spaces to make up the difference), debit and credit are 17 characters long, and comany and plant are 3 characters each.
If there is an amount in the credit column I need it to be changed to the number and - at the end (384- for example.)
If debit AND credit are both blank or 0 I want them to be excluded.
If possible, I would like a blank line between every 4 entries.
So what I'm trying to do is extract the account with the spaces in one cell (so if the account is 10100 the cell would contain "10100 " without the quotes), lets say column I, the center in column J, and debit or credit in K, with the company and plant concatenated under the center with leading zeros.
Something like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]acct[/TD]
[TD]center[/TD]
[TD]dr[/TD]
[TD]cr[/TD]
[TD]co[/TD]
[TD]plant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]4200.6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]4200.6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10100[/TD]
[TD]53102[/TD]
[TD]19142.82[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]001001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]62252[/TD]
[TD]51204[/TD]
[TD][/TD]
[TD]3413.62[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]10100[/TD]
[TD]53102[/TD]
[TD]19142.82[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]22582[/TD]
[TD]23502[/TD]
[TD]677.19[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]62252[/TD]
[TD]51204[/TD]
[TD]3413.62-[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]2614.23[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]001007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]53102[/TD]
[TD]51201[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]47[/TD]
[TD][/TD]
[TD]22582[/TD]
[TD]23502[/TD]
[TD]677.19[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]22100[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]432.20[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]001008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]22170[/TD]
[TD]1[/TD]
[TD]300.88[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]22165[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]611.31[/TD]
[TD]1[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]11000[/TD]
[TD]7[/TD]
[TD]2614.23[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22100[/TD]
[TD]1[/TD]
[TD]432.20-[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001050[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22170[/TD]
[TD]1[/TD]
[TD]300.88[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22165[/TD]
[TD]1[/TD]
[TD]611.31-[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]001051[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
And so on... right now I have the following formulas
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]I2[/TD]
[TD]=B2&REPT(" ",10-LEN(B2))[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=C2&REPT(" ",10-LEN(C2))[/TD]
[/TR]
[TR]
[TD]K2[/TD]
[TD]=IF(D2>0,D2&REPT(" ",17-LEN(D2)),E2&"-"&REPT(" ",16-LEN(E2)))[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[TD]=REPT("0",3-LEN(F2))&F2&REPT("0",3-LEN(G2))&G2[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be appreciated!
Thanks!
Last edited: