formula help

[TANUSREE]

Hello there,



THERE IS TWO TABLE..''NUMBER TABLE & LAST DRAWN " .

IN NUMBER TABLE I HIGHLIGHTED A NUMER " 17 " WHICH YOU SEE APPEARS ON 30th, 24th, 23rd & 19th April respectively.

NOW IN THE "LAST DRAWN " TABLE, I HAVE PICKED THE NUMBER 17 THAT APPEARS FROM THE TABLE ( 30TH - 24TH APRIL) AND HIGHLIGHTED IT MANUALLY TO SHOW IT APPEARED 6 DAYS EARLIER ( HIGHLIGHTED IN YELLOW) THEN I HIGHLIGHTED THE NEXT NUM,BER 1 ( 24TH-23RD) APRIL. & SO ON...

The attached xls sheet is an illustration, is there a formula to generate the "last drawn" table for all numbers in the row instead of doing it manually?

Looking forward to hearing from you soon,

Regards


Tanusree

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Number							Last Drawn
2	30-Apr	16	17	20	42	47	49		-	6	3	1	5	7
3	29-Apr	15	23	30	31	32	42		-	4	8	1	1	7
4	28-Apr	4	8	18	24	31	32		9	-	3	-	-	-
5	27-Apr	20	27	28	37	38	39		6	-	1	-	-	-
6	26-Apr	9	10	21	28	35	43		-	-	2	-	4	2
7	25-Apr	2	5	18	23	29	47		-	1	-	3	1	4
8	24-Apr	5	17	21	29	41	43		-	1	3	-	3	-
9	23-Apr	12	14	17	36	44	49		3	4	4	-	1	3
10	22-Apr	3	23	26	35	42	44		-	-	1	-	-	-
11	21-Apr	20	21	26	30	47	41		-	-	1	-	2	-
12	20-Apr	6	11	12	26	48	49		-	-	-	1	-	-
13	19-Apr	4	14	17	26	45	47		-	-	-	-	-	-
Sheet5

Cell Formulas
Range	Formula
I2:N13	I2	=IFERROR(AGGREGATE(15,6,ROW(A3:A$25)/(MMULT(--($B3:$G25=B2),{1;1;1;1;1;1})>0),1)-ROW(A2),"-")