NEED FORMULA TO IDENTIFY NUMBERS IN A ROW
Posted by YANECKC on April 19, 2001 2:27 PM
I HAVE A SPREADSHEET WITH VENDOR NUMBERS THAT I WOULD
LIKE TO CREATE A FORMULA IN COLUMN B (AVAILABLE)THAT TELLS ME HOW MANY VENDOR
NUMBERS ARE IN A ROW. SEE BELOW
VENDOR # AVAILABLE
9300140 1
9300335 1
9300482 1
9300490 1
9300511 3
9300512
9300513
9300901 5
9300902
9300903
9300904
9300905
9300990 1
9301090 1
9301123 2
9301124
9301160 3
9301161
9301162
GRAND TOTAL 19
Posted by Mark W. on April 19, 2001 4:08 PM
Enter the following array formula into cell B2:
{=(SUM((A2+ROW(A3:$A$20)-ROW(A2)=A3:$A$20)+0)+1)*(A2-N(A1)<>1)}
...and copy down to cell B20. Please remember that
array formulas must by entered using Control+Shift+Enter.
Finally, apply the following custom format to column B:
0;;;
Posted by Mark W. on April 19, 2001 4:31 PM
If your vendor numbers are text...
...use the following array formula:
{=(SUM((A2+ROW(A3:$A$20)-ROW(A2)=A3:$A$20+0)+0)+1)*IF(ROW()=ROW($A$2),1,A2-A1<>1)}
...instead of my earlier formula. Enter the following array formula into cell B2: {=(SUM((A2+ROW(A3:$A$20)-ROW(A2)=A3:$A$20)+0)+1)*(A2-N(A1)<>1)} ...and copy down to cell B20. Please remember that
Posted by yANECKC on April 19, 2001 5:16 PM
Re: If your vendor numbers are text...
{=(SUM((A2+ROW(A3:$A$20)-ROW(A2)=A3:$A$20+0)+0)+1)*IF(ROW()=ROW($A$2),1,A2-A1<>1)} ...instead of my earlier formula. : Enter the following array formula into cell B2
MARK
EXCELLENT! THAT WAS EXACTLY WHAT I WAS LOOKING FOR!
ONCE AGAIN KEEP UP THE GREAT JOB POSTING!
THANKS AGAIN!
YANECKC
