rows contains certain text "?" in new document

L

leewalker

New Member
Joined
May 27, 2007
Messages
48
hi

i wonder if anyone can help me i have a spreadsheet with 12 columns

i am looking for a way in which i can search the whole spreadsheet row by row for "?" in the last columns and if found select the whole of the row and copy the whole row to a line of a spreadsheet of set name i require it to leave 1 row under each row it copies. until it get to the end of the spreadsheet around 4000+ lines.

as you can see if i was to do this manually would take hours upon hours.


i hope i have outlined my problem clearly if you have any more questions please do not hesitate to asked and i be happy to explain more best i can

i thank you all in advance.
 
Sort by date Sort by votes
Have you tried recording a macro?
 
Upvote 0
Recording a macro is a place to start. It won't do everything you need, but someone can help you clean it up, and you'll learn something in the process.
 
Upvote 0
If I undertand correctly what you want, try this array formula (use Ctrl+Shift+Enter and not only Enter) in a new sheet (Sheet02 for example):

Code: 
A2-> =IF(MOD(ROWS(A$2:A2),2),"",IFERROR(INDEX(Sheet01!A$2:A$25,SMALL(IF(ISNUMBER(SEARCH("~?",Sheet01!$L$2:$L$25)),
ROW(Sheet01!$L$2:$L$25)-ROW(Sheet01!$L$2)+1),CEILING(ROWS(A$2:A2),2)/2)),""))

Sheet01[TABLE="width: 750"]
<tbody>[TR]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col01
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col02
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col03
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col04
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col05
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col06
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col07
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col08
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col09
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col10
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col11
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Col12
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol012
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol022
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol032
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol042
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol052
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol062
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol072
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol082
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol092
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol102
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol112
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol122
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol013
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol023
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol033
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol043
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol053
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol063
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol073
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol083
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol093
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol103
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol113
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol123?
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol014
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol024
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol034
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol044
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol054
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol064
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol074
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol084
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol094
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol104
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol114
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol124
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol015
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol025
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol035
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol045
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol055
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol065
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol075
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol085
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol095
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol105
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol115
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol125
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol016
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol026
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol036
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol046
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol056
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol066
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol076
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol086
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol096
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol106
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol116
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol126?
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet02[TABLE="width: 805"]
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" span="11"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <tbody>[TR]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col01[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col02[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col03[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col04[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col05[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col06[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col07[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col08[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col09[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col10[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col11[/TD]
[TD="class: xl66, width: 90, bgcolor: transparent"]Col12[/TD]
[TD="class: xl66, width: 73, bgcolor: transparent"]Col13[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"]BlankRow[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]DataCol013[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol023[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol033[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol043[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol053[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol063[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol073[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol083[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol093[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol103[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol113[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol123?[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"]BlankRow[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]DataCol016[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol026[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol036[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol046[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol056[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol066[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol076[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol086[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol096[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol106[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol116[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol126?[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
 
Upvote 0
markmzz said:
If I undertand correctly what you want, try this array formula (use Ctrl+Shift+Enter and not only Enter) in a new sheet (Sheet02 for example):

Code: 
A2-> =IF(MOD(ROWS(A$2:A2),2),"",IFERROR(INDEX(Sheet01!A$2:A$25,SMALL(IF(ISNUMBER(SEARCH("~?",Sheet01!$L$2:$L$25)),
ROW(Sheet01!$L$2:$L$25)-ROW(Sheet01!$L$2)+1),CEILING(ROWS(A$2:A2),2)/2)),""))

Sheet01[TABLE="width: 750"]
<tbody>[TR]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col01
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col02
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col03
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col04
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col05
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col06
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col07
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col08
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col09
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col10
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"]Col11
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Col12
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol012
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol022
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol032
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol042
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol052
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol062
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol072
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol082
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol092
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol102
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol112
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol122
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol013
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol023
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol033
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol043
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol053
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol063
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol073
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol083
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol093
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol103
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol113
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol123?
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol014
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol024
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol034
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol044
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol054
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol064
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol074
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol084
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol094
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol104
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol114
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol124
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol015
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol025
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol035
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol045
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol055
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol065
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol075
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol085
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol095
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol105
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol115
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol125
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DataCol016
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol026
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol036
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol046
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol056
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol066
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol076
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol086
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol096
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol106
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol116
[/TD]
[TD="class: xl65, bgcolor: transparent"]DataCol126?
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet02[TABLE="width: 805"]
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" span="11"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <tbody>[TR]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col01[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col02[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col03[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col04[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col05[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col06[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col07[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col08[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col09[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col10[/TD]
[TD="class: xl66, width: 83, bgcolor: transparent"]Col11[/TD]
[TD="class: xl66, width: 90, bgcolor: transparent"]Col12[/TD]
[TD="class: xl66, width: 73, bgcolor: transparent"]Col13[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"]BlankRow[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]DataCol013[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol023[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol033[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol043[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol053[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol063[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol073[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol083[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol093[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol103[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol113[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol123?[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"]BlankRow[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]DataCol016[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol026[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol036[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol046[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol056[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol066[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol076[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol086[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol096[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol106[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol116[/TD]
[TD="class: xl68, bgcolor: yellow"]DataCol126?[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
Click to expand...

this look like would almost get me results i am after but i have a number of books this need to be run on and it seems this formular would only work on one boox and add it to new sheet rather than new spreadsheet/workbook.
 
Upvote 0
i have the following code work that will select a set range and copy and paste it to a new workbook

Code: 
Sub copyopenpasteclose()
    Dim d As String
    d = Application.ActiveWorkbook.Path
    Range("A1:G1").Copy
    Workbooks.Open Filename:=d & "\copieddata.xlsx"
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

i need help to get it to check line by line rather than at a set range and see if there is a "?" in the last column to copy and past the row it is checking
 
Upvote 0
leewalker said:
this look like would almost get me results i am after but i have a number of books this need to be run on and it seems this formular would only work on one boox and add it to new sheet rather than new spreadsheet/workbook.
Click to expand...

Another way (array formula - use Ctrl+Shift+Enter and not only Enter) in a new sheet (Sheet02 for example) of the source wookbook:


In N2 you have the name of the Source Sheet and in M2 you have the Result Range that you have to copy (only values) to the new workbook.

Code: 
A2-> =IF(MOD(ROWS(A$2:A2),2),"",IFERROR(INDEX(INDIRECT("'"&$N$2&"'!"&CELL("endereço",A$2)&":"&CELL("endereço",A$4000)),
SMALL(IF(ISNUMBER(SEARCH("~?",INDIRECT("'"&$N$2&"'!"&CELL("endereço",$L$2)&":"&CELL("endereço",$L$4000)))),
ROW($L$2:$L$4000)-ROW($L$2)+1),CEILING(ROWS(A$2:A2),2)/2)),""))

M2-> ="A2:L"&COUNTIF(L:L,"?*")*2-1

The Resut:

[TABLE="width: 943"]
<tbody>[TR]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col01
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col02
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col03
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col04
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col05
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col06
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col07
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col08
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col09
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col10
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Col11
[/TD]
[TD="class: xl65, width: 90, bgcolor: transparent"]Col12
[/TD]
[TD="class: xl65, width: 73, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, width: 88, bgcolor: transparent"]Source Sheet
[/TD]
[TD="class: xl65, width: 96, bgcolor: transparent"]Result Range
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]Sheet00
[/TD]
[TD="class: xl64, bgcolor: transparent"]A2:L11
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]DataCol013
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol023
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol033
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol043
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol053
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol063
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol073
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol083
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol093
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol103
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol113
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol123?
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]DataCol017
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol027
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol037
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol047
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol057
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol067
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol077
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol087
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol097
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol107
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol117
[/TD]
[TD="class: xl66, bgcolor: yellow"]Data?Col127
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]DataCol0111
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0211
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0311
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0411
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0511
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0611
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0711
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0811
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0911
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1011
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1111
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1211?
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]DataCol0115
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0215
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0315
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0415
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0515
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0615
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0715
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0815
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0915
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1015
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1115
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1215?
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]DataCol0118
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0218
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0318
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0418
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0518
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0618
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0718
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0818
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol0918
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1018
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1118
[/TD]
[TD="class: xl66, bgcolor: yellow"]DataCol1218?
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


I tested the new formula with 4000 rows and my computer spent, for each sheet, 60 seconds.

I disabled the Automatic calculation and left in Manual calculation and use Shift + F9 to run the formula in the worksheet.

I hope this help you to solve your problem with only formulas.

Markmzz
 
Upvote 0
hi markmzz

i was really looking for solutions in VBA so i could run it once on multi files in multi folders without to much editing of the documents.

i have the following code

Code: 
Sub CopyRows()
    Dim bottomL As Integer
    Dim x As Integer
    bottomL = Sheets("sheet1").Range("L" & Rows.Count).End(xlUp).Row: x = 1
     
    Dim c As Range
    For Each c In Sheets("sheet1").Range("L1:L" & bottomL)
        If c.Value = "?" Then
            c.EntireRow.Copy Worksheets("sheet2").Range("A" & x)
            x = x + 2
        End If
    Next c
     
End Sub

this code copies the data to a new sheet but really i need it to copy to a new workbook for example "copieddata.xls" but having trouble gettin this to work any help would be great.
 
Upvote 0
leewalker said:
hi markmzz

i was really looking for solutions in VBA so i could run it once on multi files in multi folders without to much editing of the documents.

i have the following code

this code copies the data to a new sheet but really i need it to copy to a new workbook for example "copieddata.xls" but having trouble gettin this to work any help would be great.
Click to expand...

Try this small modification in your code:

Code: 
Sub CopyRows()
    Dim bottomL As Long
    Dim x As Long
    Dim WBO, WBN As Workbook
    Dim mySheet1, mySheet2 As Worksheet
    Dim c As Range    
    Set WBO = ActiveWorkbook
    Set mySheet1 = WBO.Sheets("sheet1")
    Set WBN = Workbooks.Add(xlWBATWorksheet)
    Set mySheet2 = WBN.Worksheets(1)
    mySheet2.Name = "sheetcopieddata"
    bottomL = mySheet1.Range("L" & Rows.Count).End(xlUp).Row
    x = 2
    For Each c In mySheet1.Range("L1:L" & bottomL)
        If InStr(c.Value, "?") Then
            c.EntireRow.Copy mySheet2.Range("A" & x)
            x = x + 2
        End If
    Next c
    WBN.SaveAs "copieddata"
End Sub

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Need a way to copy and paste previous rows after a dropdown selection is made on the same sheet
2
Replies
12
Views
280
Candyman8019
C
C
  • Solved
Highlight entire rows in used range where any cell contains searched text
Replies
5
Views
970
CorrieAnn
C
B
  • Solved
VB for Searching for ~ 20 words in a large table of text
Replies
3
Views
287
BobTheSpreadsheetBuilder
B
J
  • Question Question
Excel formula to allow display number of hours in particular periods of time
Replies
1
Views
213
myall_blues
myall_blues
R
  • Solved
Macro to loop through workbooks and find certain text in multiple rows
Replies
6
Views
173
rachel06
R

Share this page

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