Merge two lists (which both have label and constituents) into one list maintain hierarchy

I am new to the forum and this is my first post. I have been battling with Excel the past couple weeks with an issue. I have multiple lists (all merged into one spreadsheet) that I would like to be able to dynamically merge, depending on which two lists I select. Each list is described by the letter in the First column next to the dates specified (in this spreadsheet lists would be X, Y, Z, A, B, C, D...etc). What I am trying to accomplish is have some way that I can form one inclusive list (with both label & descriptor - columns A & B) depending on which two lists I select. For example, if I want to join "X" & "Y" into one list, while maintaining the group hierarchy, how would I do this? I'm hoping to be able to do it in such a way that I can enter the two List letters (in this case "X" & "Y") and have that list populate on a separate spreadsheet. The next step would then be to match the weight & return of X & Y to the label & descriptor for each time period. I'm assuming that this would go alongside the joined list, with X having the weight & return next to the list and then the same for Y. There will be plenty of instances where items in X do not exist in Y, and vice versa. I want these items to remain in the consolidated list, but just show "0" for ones where they do not have a corresponding instance. I have attached the sample spreadsheet I am working with, and would appreciate any guidance on how to complete this. Is it better to create a list through name manager for each list that is on the spreadsheet? Would it be better to do each dataset on a different tab?

Thank you!

Hi Alexmues,
based on your description I guess you want one consolidated view where you will see all companies and sectors with their Weight and Return.
My first suggestion will be to keep your table in tabular form (I mean for every company have sector in same row) than you can use SUMIFS function to make consolidated view.

If you plan to create consolidated table from all companies you have in your table than I suggest you to use advanced filter to keep unique records only.
You can find video how to filter unique records here:
https://www.youtube.com/watch?v=z_rMZV8zHPg

As I'm not sure if this is exactly what you are looking for, maybe it would be better to describe or show your specific needs (outcome you want).

Best regards.

Thank you for the responses. I have attached another file. This has a sample output tab that hopefully can provide a little better visual. Really I am looking for more of a "summary" page, with the ability to enter in which two portfolios we want to compare. in this case I would be looking at X & Y. I would like the output to be a list of every holding by sector that either portfolio has, so imagine we are just joining the two portfolios into one. There will be some ones that are common prior to consolidation, and we would want that holding only represented once (removing duplicates), and then also to pull in the holdings that are unique to X & unique to Y to get a full list of every holding either has. From there, I would want to use some type of "Match" (?) function to pull in the weight for X & return for X, weight for Y & return for Y for any time period listed. Let's just assume i'm only concerned with the first time set in this problem.

Does that help clarify?

If not let me know and I will do my best to elaborate!

Hi alexmues,
I added to your file one sheet "Data" what is just your original sheet where you had records I just put it into tabular form.
Than I added there sheet "Sample output v2" where I added sumifs function that is retrieving data as you wanted.
I filtered unique records as mentioned above and showed in video.

Please check attached file.

I hope that is what you look for.

Thank you! The output is exactly what I am looking for. That is so helpful. My last question would be, what would be the easiest way to "maintain" this spreadsheet. I am assuming I will be doing a download every month looking at the same data, just with a new rolling period.For example, I am not certain how I would get the data from the download, to Sample Output V2 without much manual work. Or I am not thinking this through correctly?

Again thank you and I look forward to your response.

Thank you again for the response. I apologize, but am still a little confused. The dates being named and used in a list do help, but I'm concerned about the amount of manual work to prepare the data each time before it is able to be analyzed. In particular, how do I go from Sheet 1 (the original data download) to the "Data" sheet and then to "Sample Output v2"? I'm looking at Column B on "Data" and am it seems that the beginning of each new sector is hard coded, and then the following holdings have the sector matching the hard coded version. If you could help explain the steps that you went through to describe how you set up the data, that may help. Thanks!

Another question I have is, when downloading the data, I may over time accumulate a lot of different portfolios. Is there an efficient way to "name" each individual portfolio and its holdings? I am thinking that if this gets up to 50 or so portfolios, I will have to re-name each one every time I do a download, and since their will be additional holdings/deleted holdings, the rows won't be consistent for each.

Thanks again!

My thought is, each month that I download the data, if an additional security is added to one of the portfolios, the list will not be in the same "range". What would be the best way to go about naming the lists each time I download data?

Unfortunately I can't adjust the way the data is downloaded. I will keep thinking about ways to manipulate data.

One last question I have, as I am converting the data into tabular format for all portfolios (referencing sheet 1), what is the quickest way to do so? I notice that in your data tab you have pulled in the information from Sheet 1, but for any cell that was blank you made it equal the cell above (when referencing sectors). is there a formula i can use in a new column that does this kind of work? For example, looking to say something like, "If Cell C9 has text, use the text, if not I want to pull in the appropriate sector (in this case consumer discretionary).

I noticed that you did a lot of manual equations below each hard coded sector to say equal the cell above. I would like to have a formula in a new column that does the thinking for me.

Does this make sense?

I solved my most recent question, but am stuck on how you consolidated the data. could you explain in detail what your references were, and how you did this?

I am confused on how to take the data and get it into the Sample Output V2 tab using the advanced filter technique. What columns are the list range, criteria range? How do you get the data from tabular form to the Sample Output v2?