Find rows and copy them

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
Hello To All,<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Example table1
ABCDEFGHIJKLM
1Daten1n2n3n4n512345EmptyFound Numbers
213/03/2012110254347 10
316/03/201234122350 28
420/03/20121516283046 33
523/03/2012915233150 48
627/03/20122427293436 49
730/03/2012417233646 20
803/04/201218182530 27
906/04/20121120303545 30
1010/04/20122225273637 36
1113/04/2012813263943 43
1217/04/20121028334849 1
1320/04/201236293241 13
1424/04/201289244348 17
1527/04/20122027303643 38
1601/05/201245151941 44
1704/05/2012326394041 5
1808/05/2012321343848 11
1911/05/2012113173844 22
2015/05/2012211132650 34
2118/05/20121329434750 40
2222/05/20121631323741 8
2325/05/20121222354649 15
2429/05/2012815172528 26
2501/06/201224142636 30
2605/06/20121334374749 48
2708/06/2012511223440
2812/06/201281526304817214101

<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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi Tinbendr,<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
Using same criteria there are (2 numbers 20 & 27 out of 50) which are not come up since last 14 draw.<o:p></o:p>
Then there are (3 numbers 1, 38 & 44 out of 50) which are not come up since last 10 draws.<o:p></o:p>
And it way picks rest 2 <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
And then wait for next draw and check out of 5 in celebrated draw how many number are correct within my selected numbers.<o:p></o:p>
<o:p></o:p>
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 1st row, For example If it would have been in G23:K23 than from that row to back draw.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
kishan
 
Upvote 0
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?
 
Upvote 0
Hi Tinbendr,<o:p></o:p>
<o:p></o:p>
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:p></o:p>
For example if search row is entered in G28:K28 (1-2-10-14 and 17) mean <o:p></o:p>
Row 1 will be B28:F28, 1 draw back<o:p></o:p>
Row 2 will be B27:F27, 2 draws back<o:p></o:p>
Row 10 will be B19:F19, 10 draws back<o:p></o:p>
Row 14 will be B15:F15, 14 draws back<o:p></o:p>
Row 10 will be B12:F12, 14 draws back<o:p></o:p>
<o:p></o:p>
I do not know weather I have explained well <o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
kishan<o:p></o:p>
 
Upvote 0
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>

ABCDEFGHIJKL
1Daten1n2n3n4n5Find Last cell in column B and copy back rows belowResult Find Rows For Copy
213/03/2012110254347171028334849
316/03/201234122350142027303643
420/03/2012151628304610113173844
523/03/20129152331502511223440
627/03/201224272934361815263048
730/03/2012417233646
803/04/201218182530
906/04/20121120303545
1010/04/20122225273637
1113/04/2012813263943
1217/04/20121028334849
1320/04/201236293241
1424/04/201289244348
1527/04/20122027303643
1601/05/201245151941
1704/05/2012326394041
1808/05/2012321343848
1911/05/2012113173844
2015/05/2012211132650
2118/05/20121329434750
2222/05/20121631323741
2325/05/20121222354649
2429/05/2012815172528
2501/06/201224142636
2605/06/20121334374749
2708/06/2012511223440
2812/06/2012815263048

<TBODY>
</TBODY>

Please help

Thanks In Advance,
Kishan
<o:p></o:p>
 
Upvote 0
Thank you Haseeb Avarakkan, for you help formula is giving perfect result<o:p></o:p>
<o:p></o:p>
Regards,<o:p></o:p>
Kishan
 
Upvote 0

Forum statistics

Threads
1,212,138
Messages
6,106,172
Members
448,002
Latest member
stig20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top