Find rows and copy them

[Kishan]

Hello To All,<o></o>
<o></o>
My data’s are in cells B2:F28, from which I want to copy back rows those are in cells G28:K28, and copy them in column M as shown in example table1<o></o>
<o></o>
Example table1

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	n1	n2	n3	n4	n5	1	2	3	4	5	Empty	Found Numbers
2	13/03/2012	1	10	25	43	47							10
3	16/03/2012	3	4	12	23	50							28
4	20/03/2012	15	16	28	30	46							33
5	23/03/2012	9	15	23	31	50							48
6	27/03/2012	24	27	29	34	36							49
7	30/03/2012	4	17	23	36	46							20
8	03/04/2012	1	8	18	25	30							27
9	06/04/2012	11	20	30	35	45							30
10	10/04/2012	22	25	27	36	37							36
11	13/04/2012	8	13	26	39	43							43
12	17/04/2012	10	28	33	48	49							1
13	20/04/2012	3	6	29	32	41							13
14	24/04/2012	8	9	24	43	48							17
15	27/04/2012	20	27	30	36	43							38
16	01/05/2012	4	5	15	19	41							44
17	04/05/2012	3	26	39	40	41							5
18	08/05/2012	3	21	34	38	48							11
19	11/05/2012	1	13	17	38	44							22
20	15/05/2012	2	11	13	26	50							34
21	18/05/2012	13	29	43	47	50							40
22	22/05/2012	16	31	32	37	41							8
23	25/05/2012	12	22	35	46	49							15
24	29/05/2012	8	15	17	25	28							26
25	01/06/2012	2	4	14	26	36							30
26	05/06/2012	13	34	37	47	49							48
27	08/06/2012	5	11	22	34	40
28	12/06/2012	8	15	26	30	48	17	2	14	10	1

<TBODY>
</TBODY>

Please help to find and copy them by VBA solution if it could be possible</SPAN>

Thanks In Advance,</SPAN></SPAN>
Kishan</SPAN></SPAN>
</SPAN>

 

[Tinbendr]

How are you 'finding' the data?

 

[Kishan]

Hi Tinbendr,<o></o>
<o></o>
Finding data’s criteria is based on how much delay is getting each number (1 to 50) form their last occurrence. For that I go maximum 18 draws back.<o></o>
<o></o>
In the given example you will see there are (2 numbers 10 & 33 out of 50) which are not come up since last 17 draw.<o></o>
Using same criteria there are (2 numbers 20 & 27 out of 50) which are not come up since last 14 draw.<o></o>
Then there are (3 numbers 1, 38 & 44 out of 50) which are not come up since last 10 draws.<o></o>
And it way picks rest 2 <o></o>
<o></o>
This way I will get 25 numbers and then I will remove duplicates in my example there are 2 numbers duplicates 30 and 48 after removing them will remain 23 total. <o></o>
<o></o>
And then wait for next draw and check out of 5 in celebrated draw how many number are correct within my selected numbers.<o></o>
<o></o>
To do try with past draw using same method, if I input value in column G:K then from that row back, data must extract from column B:F, in my example I have input value in Cells G28:K28 that will be consider 1^st row, For example If it would have been in G23:K23 than from that row to back draw.<o></o>
<o></o>
<o></o>
Thanks,<o></o>
kishan

 

[Tinbendr]

Sorry, I meant, how are the values singled out?

Will they be color coded green as your example. Or will you multi-select each value that meets your criteria?

 

[Kishan]

Hi Tinbendr,<o></o>
<o></o>
In the example I have coloured rows green just to show these rows, which I need to copy, are (1-2-10-14 and 17) are shown in cells G28:K28 and paste them in column M as shown. <o></o>
For example if search row is entered in G28:K28 (1-2-10-14 and 17) mean <o></o>
Row 1 will be B28:F28, 1 draw back<o></o>
Row 2 will be B27:F27, 2 draws back<o></o>
Row 10 will be B19:F19, 10 draws back<o></o>
Row 14 will be B15:F15, 14 draws back<o></o>
Row 10 will be B12:F12, 14 draws back<o></o>
<o></o>
I do not know weather I have explained well <o></o>
<o></o>
Thanks,<o></o>
kishan<o></o>

 

[Kishan]

Hello To All,</SPAN></SPAN>

if I change layout as shown below will it be possible to copy back rows of column B:F counting first row from last used cell in column B. </SPAN></SPAN>

Back rows will be copied are shown in column cells G2:G6</SPAN></SPAN>

And copy rows result will be pasted in cells H2:L6</SPAN>

	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	n1	n2	n3	n4	n5	Find Last cell in column B and copy back rows below	Result Find Rows For Copy
2	13/03/2012	1	10	25	43	47	17	10	28	33	48	49
3	16/03/2012	3	4	12	23	50	14	20	27	30	36	43
4	20/03/2012	15	16	28	30	46	10	1	13	17	38	44
5	23/03/2012	9	15	23	31	50	2	5	11	22	34	40
6	27/03/2012	24	27	29	34	36	1	8	15	26	30	48
7	30/03/2012	4	17	23	36	46
8	03/04/2012	1	8	18	25	30
9	06/04/2012	11	20	30	35	45
10	10/04/2012	22	25	27	36	37
11	13/04/2012	8	13	26	39	43
12	17/04/2012	10	28	33	48	49
13	20/04/2012	3	6	29	32	41
14	24/04/2012	8	9	24	43	48
15	27/04/2012	20	27	30	36	43
16	01/05/2012	4	5	15	19	41
17	04/05/2012	3	26	39	40	41
18	08/05/2012	3	21	34	38	48
19	11/05/2012	1	13	17	38	44
20	15/05/2012	2	11	13	26	50
21	18/05/2012	13	29	43	47	50
22	22/05/2012	16	31	32	37	41
23	25/05/2012	12	22	35	46	49
24	29/05/2012	8	15	17	25	28
25	01/06/2012	2	4	14	26	36
26	05/06/2012	13	34	37	47	49
27	08/06/2012	5	11	22	34	40
28	12/06/2012	8	15	26	30	48

<TBODY>
</TBODY>

Please help

Thanks In Advance,
Kishan<o></o>

 

[Haseeb Avarakkan]

Try in H2, then copy down & across.

=INDEX(B:B,MATCH(9E300,$B:$B)-$G2+1)

 

[Kishan]

Thank you Haseeb Avarakkan, for you help formula is giving perfect result<o></o>
<o></o>
Regards,<o></o>
Kishan