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
79)<INDEX(Available,MATCH(E80,Disc,0),1)),MAX((E$3:E79=E80)*D$379)+1,999),1),999)
It works fine for first column but fails for others.
Can anyone help me out in this task?
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
79)<INDEX(Available,MATCH(E80,Disc,0),1)),MAX((E$3:E79=E80)*D$379)+1,999),1),999)It works fine for first column but fails for others.
Can anyone help me out in this task?