MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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