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.

 

[hagia_sofia]

Hello, could you please clarify what is that you need to return as I am not sure based on the description: either a) values between M12 and M21 or b) the output as shown in column P? If a) is correctm then are there supposedto be blank rows or just those categories?

 

[kvsrinivasamurthy]

For365 version
In P12
=Filter(M12:M50,M12:M50<>"Total")

 

[sksanjeev786]

Hello, could you please clarify what is that you need to return as I am not sure based on the description: either a) values between M12 and M21 or b) the output as shown in column P? If a) is correctm then are there supposedto be blank rows or just those categories?

Hi Sofia,

Thank you for looking into it.

I need only statements between Total and Dontknow from Column M to Column P.

Just to simplify this I need data from Column A to Column D (output)
so what ever I have a statements under Total and Dontknow I need all the statements (A3 to A10)

book1
	A	B	C	D
1	Input			Output
2	Total			Less than $500
3	Less than $500			$500 - $999
4	$500 - $999			$1,000 - $1,499
5	$1,000 - $1,499			$1,500 - $1,999
6	$1,500 - $1,999			$2,000 - $2,499
7	$2,000 - $2,499			$2,500 - $2,999
8	$2,500 - $2,999			$3,000 - $4,999
9	$3,000 - $4,999			$5,000 or more
10	$5,000 or more
11	Don't Know
12	Total
13	TOP 2 BOX (NET)
14	I am currently looking around for the best offer on a credit card
15	I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet
Sheet1

 

[sksanjeev786]

For365 version
In P12
=Filter(M12:M50,M12:M50<>"Total")

Hi,

I just check your FS but look like it is taking all statements except Total but I need between Total and Dontknow Statments

Please refer Post #4

 

[hagia_sofia]

Hello, many thanks for the clarification. Not sure what is in the whole data set so please test:

=LET(
a,A1:A15,
b,IFERROR(SEARCH("$",a),0),
FILTER(a,((b=1)+(b=11))))

 

[kvsrinivasamurthy]

But in expected result It is shown Dont Know (P20). Try

=Filter(M12:M50,(M12:M50<>"Total")+(M12:M50<>"Don't Know"))

 

[sksanjeev786]

H

Hello, many thanks for the clarification. Not sure what is in the whole data set so please test:

=LET(
a,A1:A15,
b,IFERROR(SEARCH("$",a),0),
FILTER(a,((b=1)+(b=11))))

Hey Sofia,

Just tried the Formula and looks like it is only take data from B3 to B11 but my data is like dynamic end point can be any row
for example my Dont know is coming in Row 20 so my out will be from B3 to B19 since the end point of Dont know change but for sure we will have Dont know stub at any position

book1
	A	B	C	D
1	Input			Output
2	Total			Less than $500
3	Less than $500			$500 - $999
4	$500 - $999			$1,000 - $1,499
5	$1,000 - $1,499			$1,500 - $1,999
6	$1,500 - $1,999			$2,000 - $2,499
7	$2,000 - $2,499			$2,500 - $2,999
8	$2,500 - $2,999			$3,000 - $4,999
9	$3,000 - $4,999			$5,000 or more
10	$5,000 or more			$5,000 or more
11	$5,000 or more			TOP 2 BOX (NET)
12	TOP 2 BOX (NET)			I am currently looking around for the best offer on a credit card
13	I am currently looking around for the best offer on a credit card			I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet
14	I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet			I have no plans to get a new credit card right now or within the next 6 months
15	I have no plans to get a new credit card right now or within the next 6 months			Total
16	Total			High school graduate or less
17	High school graduate or less			Some college
18	Some college			Graduated from a 4 year college
19	Graduated from a 4 year college
20	Don't Know
21	Doctoral Degree
22	Prefer not to answer
Sheet1

 

[sksanjeev786]

But in expected result It is shown Dont Know (P20). Try

=Filter(M12:M50,(M12:M50<>"Total")+(M12:M50<>"Don't Know"))

Hi Srinivas,

Thank you so much for your time on this
I have tried your FS but looks like i am getting more stubs after Dont know as my Starting point is Total and ending will be Dont know but with this formula I am getting below statements which are below Dont know

could you please ref the below table

9.12.24 CreditCard_Cons prospect with rejection 1.xlsx
	M	N	O	P
12	Total			Total
13	Less than $500			Less than $500
14	$500 - $999			$500 - $999
15	$1,000 - $1,499			$1,000 - $1,499
16	$1,500 - $1,999			$1,500 - $1,999
17	$2,000 - $2,499			$2,000 - $2,499
18	$2,500 - $2,999			$2,500 - $2,999
19	$3,000 - $4,999			$3,000 - $4,999
20	$5,000 or more			$5,000 or more
21	Don't Know			Don't Know
22	Total			Total
23	TOP 2 BOX (NET)			TOP 2 BOX (NET)
24	I am currently looking around for the best offer on a credit card			I am currently looking around for the best offer on a credit card
25	I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet			I am thinking about opening a new credit card within the next 6 months, but haven’t looked into it or done anything yet
26	I have no plans to get a new credit card right now or within the next 6 months			I have no plans to get a new credit card right now or within the next 6 months
27	Total			Total
28	High school graduate or less			High school graduate or less
29	Some college			Some college
30	Graduated from a 4 year college			Graduated from a 4 year college
31	Master’s Degree			Master’s Degree
32	Doctoral Degree			Doctoral Degree
33	Prefer not to answer			Prefer not to answer
34	Technical School or Vocational Training			Technical School or Vocational Training
35	Total			Total
36	Single (never married)			Single (never married)
UMHS

Cell Formulas
Range	Formula
P12:P50	P12	=FILTER(M12:M50,(M12:M50<>"Total")+(M12:M50<>"Don't Know"))
M12:M36	M12	=INDIRECT("B"&L12)
Dynamic array formulas.

 

[hagia_sofia]

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?