Automatically letter debits/credits

[tonyjyoo]

Hello,

Random question. Is there any way to automatically put in letters for each pair of debit and credits I have in journal entries within excel?

For example, I would need A, B, C, D, etc. in a red circle.

Thanks,
Tony

 

[Gerald Higgins]

OK I think I've got this.

You'll need a few helper columns, which you can hide later on.

IMPORTANT - there may be better ways of doing this, I don't claim this is the best way !

ASSUMPTION
Your transaction numbers are in column A, starting on row 2.



Put the following formulas in row 2, in the relevant columns, and then copy all of them down as far as required.

In B2
=SUM(A$2:A2)

In C2
=IF(B1=0,+C1+1,+C1)

In B1 and C1 - make sure these are both empty.

In D1
=ROUNDDOWN((C2-1)/26,0)&"."&MOD((C2-1),26)

In E1
=IF(LEFT(D2,1)="0","",CHAR(LEFT(D2,1)+64))&CHAR(RIGHT(D2,LEN(D2)-FIND(".",D2))+65)

Columns B, C, and D can be hidden.

I THINK this should work for up to 676 groups (26 x 26), I've tested it for about 55 groups, and it seems to work OK.

 

[tonyjyoo]

OK I think I've got this.

You'll need a few helper columns, which you can hide later on.

IMPORTANT - there may be better ways of doing this, I don't claim this is the best way !

ASSUMPTION
Your transaction numbers are in column A, starting on row 2.



Put the following formulas in row 2, in the relevant columns, and then copy all of them down as far as required.

In B2
=SUM(A$2:A2)

In C2
=IF(B1=0,+C1+1,+C1)

In B1 and C1 - make sure these are both empty.

In D1
=ROUNDDOWN((C2-1)/26,0)&"."&MOD((C2-1),26)

In E1
=IF(LEFT(D2,1)="0","",CHAR(LEFT(D2,1)+64))&CHAR(RIGHT(D2,LEN(D2)-FIND(".",D2))+65)

Columns B, C, and D can be hidden.

I THINK this should work for up to 676 groups (26 x 26), I've tested it for about 55 groups, and it seems to work OK.

Hey Gerald,

Thanks for your response.

I've entered your formulas but it starts off the lettering with B, not A.

Also, how can I incorporate this so that I can have the letters circled in red? Is this only possible in VBA perhaps?

Thanks again,

Tony

 

[Gerald Higgins]

Making the letters red is trivial, just format the cells as a red font.
Applying circles is more difficult, I'm not sure how to do that.
If there was a font that included letters with circles round them, that would probably be easiest, but I'm not aware of such a font.
That means you probably need to apply a circle as a separate drawing object around each letter, and that will most likely need VBA, and might be messy.
I'm not an expert in VBA.
But my solution should deliver everything except the circles.

For me, the formula results do not start with B, they start with A.

Here's my first few lines of data....

..........Col A......Col B......Col C......Cold D......Col E
Row 2....10.........10..........1............0.0..........A
Row 3...-10..........0..........1............0.0..........A
Row 4....20.........20..........2............0.1..........B
Row 5...-15..........5..........2............0.1..........B
Row 6....-5...........0..........2............0.1..........B
Row 7....10.........10..........3............0.2..........C

What do YOU have in these cells ?

 

[tonyjyoo]

Making the letters red is trivial, just format the cells as a red font.
Applying circles is more difficult, I'm not sure how to do that.
If there was a font that included letters with circles round them, that would probably be easiest, but I'm not aware of such a font.
That means you probably need to apply a circle as a separate drawing object around each letter, and that will most likely need VBA, and might be messy.
I'm not an expert in VBA.
But my solution should deliver everything except the circles.

For me, the formula results do not start with B, they start with A.

Here's my first few lines of data....

..........Col A......Col B......Col C......Cold D......Col E
Row 2....10.........10..........1............0.0..........A
Row 3...-10..........0..........1............0.0..........A
Row 4....20.........20..........2............0.1..........B
Row 5...-15..........5..........2............0.1..........B
Row 6....-5...........0..........2............0.1..........B
Row 7....10.........10..........3............0.2..........C

What do YOU have in these cells ?

I adjusted your formula slightly and it now shows correctly. I must have had a cell off somewhere.

If I write a VBA to put a circle around each first letter, does anyone know how to circle the next?

Also, by the way, thank you Gerald for helping me with this.

 

[Gerald Higgins]

Does this have to be a circle round each letter ?

Or, for example, could it be some character next to each letter, perhaps in a separate column ?
For example a symbol from one of the symbol fonts ?

Also, do you want this symbol to appear on EVERY letter, or just the first example of each letter, for example
A (with circle)
A (no circle)
A (no circle)
B (with circle)
B (no circle)

and so on.

 

[tonyjyoo]

Preferably, if possible, just circled on the first letter, like in your example above.

 

[63falcondude]

Perhaps someone can take advantage of the symbols:

Insert, Symbol, Font: Arial Unicode MS, Subset: Enclosed Alphanumerics

 

[Gerald Higgins]

Ah, 63falcondude, I didn't know about that font.

tonyjyoo - would that be an acceptable solution ?

Note, simply applying this font won't be sufficient IF you only want to circle each FIRST instance of a character (unless you want a VBA solution).
We might be able to do something using two separate columns, with one column showing a circled letter for the first instance of each letter and nothing for every other instance, and for the second column to show nothing for the first instance of each letter and a non-circled letter for every other instance.

 

[Rick Rothstein]

If I understand your layout and what you want from it correctly, then I think this macro will do what you asked for...

[table="width: 500"]
[tr]
	[td]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long
  StartRow = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Intersect(Columns("C"), Range("C" & StartRow & ":C" & Rows.Count)).Clear
  With Cells(StartRow, "C")
    .Font.Name = "Arial Unicode MS"
    .Font.Color = vbRed
    .Value = ChrW(9398)
  End With
  For R = 2 To LastRow
    If Cells(R, "A").Value <> Cells(R - 1, "A").Value Then
      X = X + 1
      With Cells(R, "C")
        .Font.Name = "Arial Unicode MS"
        .Value = Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Font.Color = vbRed
      End With
    End If
  Next
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CircledLetters) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[tonyjyoo]

If I understand your layout and what you want from it correctly, then I think this macro will do what you asked for...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long
  StartRow = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Intersect(Columns("C"), Range("C" & StartRow & ":C" & Rows.Count)).Clear
  With Cells(StartRow, "C")
    .Font.Name = "Arial Unicode MS"
    .Font.Color = vbRed
    .Value = ChrW(9398)
  End With
  For R = 2 To LastRow
    If Cells(R, "A").Value <> Cells(R - 1, "A").Value Then
      X = X + 1
      With Cells(R, "C")
        .Font.Name = "Arial Unicode MS"
        .Value = Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Font.Color = vbRed
      End With
    End If
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CircledLetters) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Rick - I was testing your macro and this is EXACTLY what I need. However, the location of my cells are different than what Gerald set up. My amounts are in column F, and the formula rows are in columns M,N,O, and P - versus A,B,C,D,E like Gerald has. Also, my data starts at row 15, not 1.

Can you help adjust the macro for these criteria??