Appending letters for every change in Invoice No + Month Ending

[DashboardNovice]

In the attached file, I have invoice numbers (column F) that can repeat because they span more than one month (column G).

I can't have repeating invoice numbers, so I just want to append "A, B or C" to the end of each invoice number for each month.

Column H in yellow, shows what the desired results are.

Can anybody offer any suggestions?

 

[Villalobos]

Hi,

Is it OK for you?

 

[DashboardNovice]

Hi,

Is it OK for you?

Very nice my friend. I totally forgot about the CHAR function. In essence you are keeping a running total of the count, row by row, and you are doing this by using an expanding range.

I hope I can remember the idea behind using an expanding range in other situations.

Thank you.

 

[bosco_yip]

1] The CHAR() only give "A - Z" 26 nos of index.

If your index more than 26 nos, try to use :

=F4&SUBSTITUTE(ADDRESS(1,COUNTIF($F$4:F4,F4),4),1,)

2] In Excel 2003 or below, the above formula can obtain index : A - Z, AA - IV (totally 256 nos.)

3] In Excel 2007or above, the above formula can obtain index : A - Z, AA - ZZ and AAA - XFD ( totally 16384 nos.)

Regards
Bosco

 

[DashboardNovice]

In the attached file, I have invoice numbers (column F) that can repeat because they span more than one month (column G).

I can't have repeating invoice numbers, so I just want to append "A, B or C" to the end of each invoice number for each month.

Column H in yellow, shows what the desired results are.

Can anybody offer any suggestions?

I have modified my original file. The revised version is attached. I should have said each invoice number may appear more than once within any given month. If these cases, each invoice number should have a letter appended to it for each unique month. For example, invoice 44867 appears 12 times. It appears across 4 months, therefore the New Invoice number in column I would be 44867A appearing 3 times, then 44867B appearing 3 times, etc.

Column I shows what I am trying to get.

I figured the best way to do this was to concatenate G&H, then for every unique occurrence in column H, append the next letter in sequence, starting all over with A for each new invoice number.

 

[bosco_yip]

Try,

=F4&CHAR(64+SUMPRODUCT((F$4:F4=F4)/COUNTIFS(F$4:F4,F$4:F4,G$4:G4,G$4:G4,H$4:H4,H$4:H4)))

or,

=F4&SUBSTITUTE(ADDRESS(1,SUMPRODUCT((F$4:F4=F4)/COUNTIFS(F$4:F4,F$4:F4,G$4:G4,G$4:G4,H$4:H4,H$4:H4)),4),1,)

Regards
Bosco

 

[DashboardNovice]

Hi,

Is it OK for you?

Can you use an array formula as a conditional formatting formula?

 

[Villalobos]

Hi,

Could you please describe it in details?