How to select a Dynamic Range (start and stop) based when contains specific word

[aroy061987]

I have a portfolio holding statement in PDF. Which I extracted to excel. Now the table that it has generated looks like below: 

AND INVESTMENTS IN MUTUAL FUNDS FOR THE PERIOD FROM 01-07-2022 TO 13-07-2022 Summary of Investments (on the basis of PAN of first holder)
Holding Statement as 13 Jul 2022
	ISIN	Security	Current Bal	Frozen Bal	Pledge Bal	Pledge Setup Bal	Free Bal	Market Price / Face Value	Value (`)
	ABCD123456	Test	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
	WXYZ123456	Test2	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
	Holding Statement as 13 Jul 2022 (Cont.)
	ISIN	Security	Current Bal	Frozen Bal	Pledge Bal	Pledge Setup Bal	Free Bal	Market Price / Face Value	Value (`)
	PQRS09876	Test3	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
	Portfolio Value XXXXXXX
Holding Statement as 13 Jul 2022
	ISIN	Security	Current Bal	Frozen Bal	Pledge Bal	Pledge Setup Bal	Free Bal	Market Price / Face Value	Value (`)
	ABCD123456	Test	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
	WXYZ123456	Test2	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
	Holding Statement as 13 Jul 2022 (Cont.)
	ISIN	Security	Current Bal	Frozen Bal	Pledge Bal	Pledge Setup Bal	Free Bal	Market Price / Face Value	Value (`)
	PQRS09876	Test3	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
	Portfolio Value XXXXXXX

I want to grab the range of Holding Statements. Range to start if first column contains "Holding statement" and stop if the second column contains "Portfolio value".

Note: Section for the "Holding statements" are dynamic and can appear multiple times based on the number of demat account holding of the user.

It will be very helpful if anyone can help me with any quick way to select the range as specified above dynamically.

[Qiu]

@aroy061987 
I am sorry but I was not able to get your intention here.
Can you give a sample output?

[jbichachi003]

Hi @aroy061987,

I don't fully understand your note about the Holding statements being dynamic and appearing multiple times. Can they appear multiple times within a single grouping, or they might appear multiple times, but each time represents a new group?

Because of this ambiguity, I only looked at the Portfolio Value in the second column. In the attached workflow, I added a RecordID to maintain the original order integrity, sorted on RecordID by descending (to flip the order), and then I used the Multi-Row Formula to with the following expression:

IF Contains([Field2], 'portfolio value')
THEN [Row-1:Group] + 1
ELSE [Row-1:Group]
ENDIF

Group is a newly created field, where 0 is assigned to any value that doesn't exist.

After that, I used a sort to order on RecordID ascending (to sort back to the original order).

This solution still gives you some of the header/footer rows, but those should be easy to sort out. This solution may work better if you looked at Holding Statement in Field1, but I didn't do that because I didn't understand your note.

Good luck!

[AdamSt]

Hi @aroy061987 ,

My solution was very similar to @jbichachi003 but contained a few more steps so in the end you just have detailed records  with two new columns which contain the Holding Statement value and the Portfolio Value and removes all of the records I'm assuming you wouldn't want in your final results (like header records, Cont records, and null records.  Also at the end I added a dynamic rename to add the fields names to your detailed results.

In my example I didn't list all of your fields, but adding the additional fields should still work in the final results.

[aroy061987]

I want the output like this.

ISIN	Security	Current Bal	Frozen Bal	Pledge Bal	Pledge Setup Bal	Free Bal	Market Price / Face Value	Value (`)
ABCD123456	Test	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
WXYZ123456	Test2	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
PQRS09876	Test3	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
ABCD123456	Test	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
WXYZ123456	Test2	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
PQRS09876	Test3	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX

[jbichachi003]

You should be able to just use a couple of filters to clear out what you don't need. See attached.

[AdamSt]

Hi @aroy061987 

I added the other columns from your example and just unselected the two extra columns I had created and now the results match your expected results

[grazitti_sapna]

@aroy061987, give this a try.

Thanks!

[aroy061987]

In my country, we have a concept of consolidated holding statement. So basically, if I am holding demat account with multiple brokerage houses then in my consolidated holding statement all my holdings with different brokerage houses will be grouped together.

So if I am having 5 demat accounts then the group will appear in my statement 5 times with different holdings with that particular brokerage house. It will not only consist of Holding Statement but it will also consist

1) Transaction Statement of the different brokerage house for the period. Transaction statement is not in the same format as holding statement.

2) Mutual Fund Transaction Statement and Holding Statement.

So I was trying to understand at least to pick one range so that I can pick the other ranges dynamically. I am yet to see the solutions. But thought of giving a bit more insights if that is helpful. PDF Format comes like below

|---------------------------------------------------------------------------------------------------------------------------|

Letter Head

Period

User Details

Details of brokerage houses registered

<Table>

Details of Mutual Funds registered

<Table>

|----------------------------------------------------------------------------------------------------------------------------|

Transaction Statement with Brokerage House 1

<Table>

|----------------------------------------------------------------------------------------------------------------------------|

Holding Statement with Brokerage House 1

<Table>

|----------------------------------------------------------------------------------------------------------------------------|

(Transactions and holding statement groups repeats based on number of brokerage houses)

Mutual Fund Transaction Statement

<Table>

|----------------------------------------------------------------------------------------------------------------------------|

Mutual Fund Held

<Table>

|----------------------------------------------------------------------------------------------------------------------------|

(Mutual Fund group also repeats occasionally for different depositories.)

So basically when we convert the above formatted pdfs into excel, we get the standard output format (as shared with the original post). I just shared the same for Holding Statement. I want to grab the information from different holding statement and put it into one single tab.

Once I achieve the same I will repeat this job for Mutual Funds and Transaction Statements also.

Thanks a lot for helping me out on this! Really appreciate.