Serial number allocation - Array formula

S

Space Ape

I have a worksheet data of size 1000x26 as shown below.

A B C D E F G H ----(columns)
-----------------------------------------------+
1 A 1 e 1 i 1 m...(upto 26 columns) |
1 b 1 f 1 j 1 n...(upto 26 columns) |
2 A 3 A 1 k 1 o...(upto 26 columns) |
1 d 1 h 4 A 1 p...(upto 26 columns) |
: : : : : : : : |
: : : : : : : : ('A's at random) |
(upto 1000 rows) |
-----------------------------------------------+

I need to:

1. Assign serial numbers to the characters(data) in the adjacent cell
on left side.
2. Assign the serial numbers in ascending order of columns.
that is first column B then column D then column F...
3. Maintain the series. Example: See series of 'A's in data.

I am struggling with following array formula.

=IF(B80=999,IF(COUNTIF(E$3:E79,E80)>0,IF(AND(INDEX(Allot,MATCH(E80,E$3:E79,0),COLUMN(D80)-1)<>999,MAX((E$3:E79=E80)*D$3:D79)<INDEX(Available,MATCH(E80,Disc,0),1)),MAX((E$3:E79=E80)*D$3:D79)+1,999),1),999)

It works fine for first column but fails for others.

Can anyone help me out in this task?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top