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