Combining multiple into one, and ordering the data

[Sevigny86]

Good evening everyone,

I'm build a sheet following an investment portfolio and I need to extract all dividend data so I can update the portfolio total value in chronological order. I'm currently using a database (Factset) which is feeding by some of the raw data (Dividend amounts, dates etc) and so I had to organize myself in a specific way. Currently, I have a table which will automatically pull the dividend data moving forward, but I need to combine it all into one table, on an ongoing basis.

So my question is, is there a way for me to have a formula that looks at all the tables, and pulls the Dividend Payment Dates and orders them chronologically? Once that is setup I believe I could then Index + Match the rest of the data based on those dates to fill out the rest. Here is a link to an image of the excel document as an example.

https://photos.app.goo.gl/wdq3a1olv2scD7Hm1

From the image, the table from columns N-S would be the destination, pulling from the tables from column W onwards. Theres basically one table for each unique symbol automatically generated in column U.

Note the headers of "Dividend History" table will change, as it is currently built from manual inputs. But the idea would be to dynamically pull the Symbol, Pay Date, Quantity (from Quantity on Record) and Div/Share.

Thanks in advance!

 

[Sevigny86]

looking at this more i have further clarification.

I will likely need to add a column with the tickers, for example TCS-CA, and SIS-CA in each of the tables to more easily ID the arrays. What I would need the formula to do is a little complex and I'm unsure if its possible, hence this thread.

In the example image, I would want to pull all pay dates for every stock where the quantity on record is not zero. I would also like to have it ordered chronologically. Then for the other columns of my Dividend History table, I will want to pull the corresponding tickers (ex. TCS-CA) and Div/Share.

I am able to set this up if it were one table, but the problem I have is there are currently 14 tables like that I need to search and combine, and it will increase as the portfolio increases, so I need this to be dynamic.

Is this possible? What would be the best say to proceed? I would ideally like a non-macro solution.

Again, thanks in advance for any help or advice!

Cheers

 

[Sevigny86]

bump