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]

Yes, I think that's helpful, in that case I think I'm right in saying that if we use some kind of cumulative sum function, then each time it turns to zero, it indicates the end of a group.

This is doable with helper columns.

For example, let's say your values are in the range A2:A100.
In B2, put something like this

=a2+b1

and copy this down to B100.

This formula should show a zero from time to time, and specifically on the LAST entry in each group.

We can then use this to work out when your identification letters need to change.

Try what I've described here - is it working like this ?

 

[tonyjyoo]

Yes, I think that's helpful, in that case I think I'm right in saying that if we use some kind of cumulative sum function, then each time it turns to zero, it indicates the end of a group.

This is doable with helper columns.

For example, let's say your values are in the range A2:A100.
In B2, put something like this

=a2+b1

and copy this down to B100.

This formula should show a zero from time to time, and specifically on the LAST entry in each group.

We can then use this to work out when your identification letters need to change.

Try what I've described here - is it working like this ?

Yes - I see the logic going on here. How can I now incorporate this to letter each zero starting from A, B, C, etc.? If at all possible, I would want them as red circled.

 

[dUBBINS]

You could use SUMIFS. Although if you want it to circle it in red after it zeroes out, you are wanting a VBA answer. Are you familiar with coding? Or are you just wanting a formula? You could perhaps use Conditional Formatting to shade the cell rather than circle it, and this would be a much simpler answer to give you.

 

[tonyjyoo]

You could use SUMIFS. Although if you want it to circle it in red after it zeroes out, you are wanting a VBA answer. Are you familiar with coding? Or are you just wanting a formula? You could perhaps use Conditional Formatting to shade the cell rather than circle it, and this would be a much simpler answer to give you.

I am familiar with VBA but don't know how I can write this. And I need circled lettering for this purpose.

 

[Gerald Higgins]

Are there more than 26 groups ?

If yes, how should the lettering work for the 27th group, and so on ?

 

[tonyjyoo]

Are there more than 26 groups ?

If yes, how should the lettering work for the 27th group, and so on ?

Great question.

There can be more than 26 groups. In cases like this, I would litter the 27th group as "AA", followed by "AB", etc.

 

[tonyjyoo]

Great question.

There can be more than 26 groups. In cases like this, I would litter the 27th group as "AA", followed by "AB", etc.

So basically what I need is automatic lettering for each of these "groups".

 

[tonyjyoo]

Are there more than 26 groups ?

If yes, how should the lettering work for the 27th group, and so on ?

Is there a way to configure this with VBA?

 

[Gerald Higgins]

I think I can get this to work for up to 26 groups, but need to do a bit more thinking to get it to work for more than 26 groups.

What is the maximum possible number of groups - hundreds ? thousands ? millions ?

 

[tonyjyoo]

I think I can get this to work for up to 26 groups, but need to do a bit more thinking to get it to work for more than 26 groups.

What is the maximum possible number of groups - hundreds ? thousands ? millions ?

It usually is up to I'd say... 50 groups. And it won't necessarily be up to 26 groups for each case either. I would just want some type of threshold so it can account for if it has up to lets say 50.