Sequence Conundrum

Hi All,

In the attached sheet I have 2 columns of data, GL Code and TB Group. I would like to put a formula in a 3rd column that concatinates the TB Group field with the number of unique GL Codes within that group so far as the rows progress. The concept is simple but the formula does not appear to be. This is a small sample of data but I need to apply this to a lrage piece of data.

see attachment for example

Thanks very much for your help

Ciaran

Hi Ciaran,

Try this formula in Column C of your example:

=B2&RIGHT(FIXED(A2,0,TRUE),1)

This formula takes the "TB Group" value and appends it (using "&") with the right-most character (using "RIGHT") of the "GL Code" (after converting the numeric value to a text string (using "FIXED"). The "TRUE" is to eliminate commas and the "1" is a parameter for RIGHT specifying a single character.

I hope that helps...

PS: if, in some cases you need 2 characters instead of 1, this won't work.

Dave

Thanks oeldere, the right character on the GL code field containing the correct numbers is purely cooincidental, nice spot but that wont work

Thanks Dibbins, I have reattached. Hope this makes sense now. Thanks and have a great weekend

Ciaran,

Now you've really confused the h*ll out of me...

From your latest attachment, it appears as if your requirement is as follows:

1) There are two independent and unordered sequences of values in which duplicates can and do occur
2) For each permutation of a pairing between the two columns, a third column needs to be populated with a computed value
3) The computed value in column 3 is a compound value comprising two components in the form XXXX-YY where:
a) XXXX = your "TB Group"
b) YY =an incremented number starting from 01 to reflect the incidence number of a particular "TB Group" pairing with any "GL Code"
4) Once a unique pairing has occurred, any further occurrences of a previously paired combination must be given the same suffix as the initial occurrence

NB: I've catered for a maximum of 99 matches per "TB Group" - if this is inadequate, we may need to change form of column 3 value to XXXX-YYY

Is the above correct interpretation?

Dave

Thanks Dave, I have managed to get it to work, all be in using a bunch of columns, see attached

Col C Counts the number of "GL Codes" within the rows "TB Group". If its greater than 1, its assigned a "DUPE" status.
Col D and E then counts number of "DUPES" so far in the data
Col F then subtracts the duplicates from Col C, leaving the number of unique GL Codes per TB Group so far

Apologies all if I didnt explaing this one vey well before, tricky to get my head around.
The numbers are then used in a vloohup downstream giving me the fisrt, second, third match etc

It would be nice to get the desired result within the same column but given the headache this one caused I may just leave it

Thanks all for help and interest

Ciaran Mc