need between data only

[sksanjeev786]

Hi Team,

I need between Statement from Less than $500 to $5,000 or so between data will will be Total and Ending will be Dont know
output I need in P column and input are in M column and statement number will varies but Total and Dont know are standard

9.12.24 CreditCard_Cons prospect with rejection 1.xlsx
	L	M	N	O	P
12	195	Total			Less than $500
13	198	Less than $500			$500 - $999
14	201	$500 - $999			$1,000 - $1,499
15	204	$1,000 - $1,499			$1,500 - $1,999
16	207	$1,500 - $1,999			$2,000 - $2,499
17	210	$2,000 - $2,499			$2,500 - $2,999
18	213	$2,500 - $2,999			$3,000 - $4,999
19	216	$3,000 - $4,999			$5,000 or more
20	219	$5,000 or more			Don't Know
21	222	Don't Know			TOP 2 BOX (NET)
22	225	Total			I am currently looking around for the best offer on a credit card
23	228	TOP 2 BOX (NET)			I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet
24	231	I am currently looking around for the best offer on a credit card			I have no plans to get a new credit card right now or within the next 6 months
25	234	I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet			High school graduate or less
26	237	I have no plans to get a new credit card right now or within the next 6 months			Some college
27	240	Total			Graduated from a 4 year college
UMHS

Cell Formulas
Range	Formula
L12	L12	=M10
M12:M27	M12	=INDIRECT("B"&L12)
L13:L27	L13	=L12+3
P12:P45	P12	=FILTER(M12:M50, (M12:M50<> "Total") * (M12:M50<> "None"), "No results")
Dynamic array formulas.

 

[kvsrinivasamurthy]

Try in P12

=LET(rn,M12:M200,ra,MATCH("Total",rn,0)+1,rb,MATCH("Don't Know",rn,0)-1,z,INDEX(rn,ra):INDEX(rn,rb),z)

 

[sksanjeev786]

Thanks for the feedback. One more question: within your example it seems that the chain is Total (M12) - Don't Know (M21) - Total (M22) - Total (M27). The question is which "Total", i.e. either M22 or M27, is the one where the next extraction should start - what is the criterium?

Hey Sofia,

Always we will consider first Total M12 and ignore other total if and end will be Dont know so what ever statement are coming in between i need those in only and we can ignore other Total which are coming after Dont know

Regards
Sanjeev

 

[hagia_sofia]

So, M12 to M21 are the boundaries of the first output, which is clear, and then M22 to the next "Don't Know" (let us say M30) ignoring the fact that that there is another Total in M27?

 

[hagia_sofia]

Hello, test this:

=LET(
a,M12:M27,
b,SEQUENCE(ROWS(a)),
c_1,FILTER(a,(a="Total")+(a="Don't Know")),
c_2,FILTER(b,(a="Total")+(a="Don't Know")),
c_3,DROP(c_1,1)=DROP(c_1,-1),
c_4,VSTACK(FALSE,c_3),
c_5,FILTER(c_2,c_4=FALSE),
c_6,IF(ISODD(ROWS(c_5)),DROP(c_5,-1),c_5),
c,WRAPROWS(c_6,2),
d_1,INDEX(c,,1)+1,
d_2,INDEX(c,,2)-1,
d_3,d_2-d_1+1,
d,DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(x,y,VSTACK(x,SEQUENCE(INDEX(d_3,y,1),,INDEX(d_1,y,1))))),1),
XLOOKUP(d,b,a))

 

[sksanjeev786]

Try in P12

=LET(rn,M12:M200,ra,MATCH("Total",rn,0)+1,rb,MATCH("Don't Know",rn,0)-1,z,INDEX(rn,ra):INDEX(rn,rb),z)

Perfect!! thank you so much... for your support and help...

 

[sksanjeev786]

Hello, test this:

=LET(
a,M12:M27,
b,SEQUENCE(ROWS(a)),
c_1,FILTER(a,(a="Total")+(a="Don't Know")),
c_2,FILTER(b,(a="Total")+(a="Don't Know")),
c_3,DROP(c_1,1)=DROP(c_1,-1),
c_4,VSTACK(FALSE,c_3),
c_5,FILTER(c_2,c_4=FALSE),
c_6,IF(ISODD(ROWS(c_5)),DROP(c_5,-1),c_5),
c,WRAPROWS(c_6,2),
d_1,INDEX(c,,1)+1,
d_2,INDEX(c,,2)-1,
d_3,d_2-d_1+1,
d,DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(x,y,VSTACK(x,SEQUENCE(INDEX(d_3,y,1),,INDEX(d_1,y,1))))),1),
XLOOKUP(d,b,a))

Thank you so much Sofia for your time on this.. This works very well and that's what I was looking for...

Thank you Team for your support and hard work on this

 

[hagia_sofia]

Thanks for the feedback, the most important thing is that the question has been answered.

 

[Joe4]

sksanjeev786,

When marking a post as the solution, please mark the original post containing the solution you actually used; not your own post acknowledging that some other post is the solution.
I am not sure which of the two you went with, so please mark the appropriate one.

 

[sksanjeev786]

sksanjeev786,

When marking a post as the solution, please mark the original post containing the solution you actually used; not your own post acknowledging that some other post is the solution.
I am not sure which of the two you went with, so please mark the appropriate one.

Hey Joe,

Thanks for checking on this, but I just wanted to let you know that I am getting perfect results from both threads. Do we have any option where I can mark both the solution tickmark?

Regards
Sanjeev

 

[MARK858]

Do we have any option where I can mark both the solution tickmark?

You can only mark one reply as a solution, it is best to mark the one you actually chose to use as a solution (and let any others know theirs work in a reply, which you have already done).
It is only an indication for anyone looking at the thread.