Extract cells to a different sheet

[sooshil]

Hello,

Here is my problem. I need a formula solution for this. NO VBA.

I have a single column (A:A) large (around 70000 rows, distinct) data.
From that data, I need to extract some of the rows to another sheet based upon the starting and ending cell values.

Here is the example for problem.
In a sheet (Sheet1) I have data something like this in Column A.

ABC123
PQR2456
BDP75-2
AQPR2
VWTWD
T-4PQCTD
WEFFF9P
SQOO5Y

I will set the starting and ending point in another sheet (Sheet2).
Suppose,
Starting Point = BDP75-2 (Suppose in Cell C2)
Ending Point = WEFFF9P (Suppose in Cell C3)

When I enter Ending point, I want all the values from starting point to ending point (including both) to be copied to the column A of Sheet2.
So the column A of Sheet2 looks like:

BDP75-2
AQPR2
VWTWD
T-4PQCTD
WEFFF9P

Any suggestions are highly appreciated. But, again NO VBA please.

 

[RasGhul]

Hi Sooshil,

There's maybe an easier way to accomplish this but see if this suits, note the helper columns for your data page;

Book1
	A	B	C
1	Rows	Data	TRUE
2	1	ABC123
3	2	PQR2456
4	3	BDP75-2	1
5	4	AQPR2	1
6	5	VWTWD	1
7	6	T-4PQCTD	1
8	7	WEFFF9P	1
9	8	SQOO5Y
Sheet1

Cell Formulas
Range	Formula
A2	=ROWS($A$2:A2)
C2	=IF(AND(A2>=Sheet2!$C$2,A2<=Sheet2!$C$3),1,"")

Book1
	A	B	C
1		Selection	Postion
2	Start	BDP75-2	3
3	End	WEFFF9P	7
4
5
6	Data
7	BDP75-2
8	AQPR2
9	VWTWD
10	T-4PQCTD
11	WEFFF9P
Sheet2

Cell Formulas
Range	Formula
C2	=INDEX(Sheet1!$A$2:$A$9,MATCH(Sheet2!B2,Sheet1!$B$2:$B$9,0))
C3	=INDEX(Sheet1!$A$2:$A$9,MATCH(Sheet2!B3,Sheet1!$B$2:$B$9,0))
A7	{=IF(ROWS(Sheet2!$A$7:A7)>($C$3-$C$2)+1,"",INDEX(Sheet1!$B$2:$B$9,SMALL(IF(Sheet1!$C$2:$C$9=1,ROW(Sheet1!$C$2:$C$9)-ROW(Sheet1!$C$2)+1),ROWS(Sheet2!$A$7:A7))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sooshil]

Hi RasGhul

Thank you so much for your answer.
That was working as questioned. But, my situations is not something like that. Still, Thank you so much.

I too tried a lot and somehow managed to get the result I wanted.
I used, OFFSET and MATCH. But, I got problem with #N/A for the empty result cells.
Please help me to eliminate that #N/A error in my sheet.
For the sake of easiness, I did my work in same sheet, but I am very much aware to move that result in a different sheet as required.
Here is my file.

https://drive.google.com/file/d/1M-YJjuZW5ENCi138nAquayxohoX7Pr1C/view?usp=sharing

Thank you.

 

[RasGhul]

I can't seem to get around the #N/A even with IFNA or IFERROR because the offset array is constant and includes the empty cells in column E.

This type of OFFSET array will potentially make this sheet very slow.


I managed to get the following to work without the helper columns;

=IF(ROWS($G$2:G2)>(MATCH($D$3,$A$1:$A$8,0)-MATCH($D$2,$A$1:$A$8,0)+1),"",INDEX($A$1:$A$8,SMALL(IF(ROW($A$1:$A$8)-ROW($A$1)+1>=MATCH($D$2,$A$1:$A$8,0),ROW($A$1:$A$8)-ROW($A$1)+1),ROWS($G$2:G2))))

https://docs.google.com/spreadsheets/d/1mMQoxymI11HmIIToPXPNRqj_5-vl_OZmDCNYnTytDOU/edit?usp=sharing

 

[sooshil]

Hi RasGhul

That's the perfect solution. Thank you so much for your time. I really appreciate this.

 

[RasGhul]

Hi RasGhul

That's the perfect solution. Thank you so much for your time. I really appreciate this.

You're welcome, thanks for the feedback.