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

 

[tonyjyoo]

See if this does what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, EText As Variant
  On Error GoTo SomethingBadHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow - 1, "E"), Cells(LastRow, "E"))
  X = -1
  Application.ScreenUpdating = False
  For R = 2 To UBound(EText)
    If EText(R, 1) <> EText(R - 1, 1) Then
      X = X + 1
      With Cells(R + StartRow - 2, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
      End With
    End If
  Next
SomethingBadHappened:
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Mr. Rothstein, you are a genius.

I just have one additional quick question. If my descriptions are not EXACTLY identical, will this macro letter the group differently, even with the associated amounts? I will have a few cases where the descriptions MIGHT not match exactly, but they are truly "part" of the same group of amounts, if that makes sense.

For the identical cases, however, this works beautifully.

 

[tonyjyoo]

I stated this in post #44 that descriptions can vary but have associated amounts as part of its "group".

 

[Rick Rothstein]

I just have one additional quick question. If my descriptions are not EXACTLY identical, will this macro letter the group differently, even with the associated amounts? I will have a few cases where the descriptions MIGHT not match exactly, but they are truly "part" of the same group of amounts, if that makes sense.

The particular method I used requires the text to be identical which is why I asked this (with emphasis added) in Message #45 before I wrote the code...

Okay, first, let's make sure we are talking about the same thing. The descriptions in Column E are identical for value in Column F belonging to it, correct? In other words, assuming F15 has 100 in it and F16 has -100 in it, I am expecting that both cell E15 and cell E16 contain exactly the same text description (no variations at all between them) and for the next set of related numbers in Column F that the corresponding cells in Column E all have the identical text in them (again, no variations at all between them)

and preceded to write the code after you answered me this way...

Yes, you are correct that the corresponding cells in F have identical text in them in Column E.

We volunteers here ask these kinds of questions beforehand in order to avoid wasting time developing solutions that won't be usable by the questioner. Okay, we are where we are... let me see if I can save the effort I put into the code I have already developed...can you tell me in what ways the text might not match each other? For example, might the text all be the same at the beginning up to, say, some differing text at the end in parentheses?

 

[tonyjyoo]

The particular method I used requires the text to be identical which is why I asked this (with emphasis added) in Message #45 before I wrote the code...

and preceded to write the code after you answered me this way...

We volunteers here ask these kinds of questions beforehand in order to avoid wasting time developing solutions that won't be usable by the questioner. Okay, we are where we are... let me see if I can save the effort I put into the code I have already developed...can you tell me in what ways the text might not match each other? For example, might the text all be the same at the beginning up to, say, some differing text at the end in parentheses?

I'm sorry I must have been confused and overlooked this. I apologize again and appreciate the extra help you are giving me and will try to be as precise as possible in the future, as I am a new member.

That being said, I am looking through all possible variations on my end, and sometimes they are only different by a few characters, sometimes they differ completely... (again, I apologize, I overlooked this).

For example, there CAN be an example that would look like this:

Description	Amount
Check Deposits	500
Check 1	-100
Check 2	-250
Cash	-150

<tbody>
</tbody>

So in this example, I would need one letter (we'll say A) for "Check Deposits", which includes check 1, 2, and cash, all of which total up to 500.

Maybe the code can be adjusted for when the amounts sum to 0 instead? I love how the code works when the text is identical and how it places it next to the description, but I can't possibly think of a way it would be possible if the descriptions aren't matching, or is this possible?

Again, Mr. Rothstein, please forgive me. I thank you again in advance for your help.

 

[Rick Rothstein]

Maybe the code can be adjusted for when the amounts sum to 0 instead?

This will require me to change the heart of my code, but should be doable IF what you said in Message #20 was accurate, namely...

Data will always be sorted with positives first in a group (either 1 or multiple), followed by its corresponding negatives, summing to 0.

Let me see what I can come up with.

 

[tonyjyoo]

This will require me to change the heart of my code, but should be doable IF what you said in Message #20 was accurate, namely...

Let me see what I can come up with.

Yes, my statement in message #20 will always hold true.

 

[Rick Rothstein]

Yes, my statement in message #20 will always hold true.

Okay, I think this works...

[table="width: 500"]
[tr]
	[td]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, Total As Long, EText As Variant, FNumbers As Variant
  On Error GoTo SomethingBadMustHaveHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow, "E"), Cells(LastRow, "E"))
  FNumbers = Range(Cells(StartRow, "F"), Cells(LastRow, "F"))
  X = -1
  Application.ScreenUpdating = False
  For R = 1 To UBound(FNumbers) - 1
    If Total = 0 Then
      X = X + 1
      Total = 0
      With Cells(R + StartRow - 1, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
      End With
    End If
    Total = Total + FNumbers(R, 1)
  Next
SomethingBadMustHaveHappened:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[tonyjyoo]

Okay, I think this works...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, Total As Long, EText As Variant, FNumbers As Variant
  On Error GoTo SomethingBadMustHaveHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow, "E"), Cells(LastRow, "E"))
  FNumbers = Range(Cells(StartRow, "F"), Cells(LastRow, "F"))
  X = -1
  Application.ScreenUpdating = False
  For R = 1 To UBound(FNumbers) - 1
    If Total = 0 Then
      X = X + 1
      Total = 0
      With Cells(R + StartRow - 1, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
      End With
    End If
    Total = Total + FNumbers(R, 1)
  Next
SomethingBadMustHaveHappened:
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

It worked up until the point where descriptions changed, then it stopped lettering.

 

[tonyjyoo]

Is there any chance you can make the letters bolded as well?

 

[Rick Rothstein]

It worked up until the point where descriptions changed, then it stopped lettering.

I am not sure what you mean by that, especially given I tested the code where every cell in Column E had different text in it. Can you show and example of the section of your data (both Column E and F values) plus a few rows before and after the grouping so I can see what you are dealing with?

Is there any chance you can make the letters bolded as well?

I can, but because the circle is so close to the letter contained in it, the extra line thickness due to the bolding makes the enclosed letter less readable. I'll add the line of code to bold the red circled letter and you can comment it out to see the difference... but I won't do that until we straighten out the apparent problem you pointed out above.