VBA: Dynamic List from same column on multiple sheets

Hello,

I am back again looking for more help.

My question is how to create a dynamic Master list on a master sheet that just grabs all the information from the same column on multiple sheets and creates 1 master list of all the values.
Looking for 2 solutions:
1 - just basic pull all the data into master list excluding blank rows
2 - same as above only sorted

The actual excel workbook can have up to 50+ worksheets but the values will always be in the same column.
The number of rows can also be different but never more than 1000.

I thought this would be relatively simple but I was not able to google it. I tried some filter and indirect combinations but I could not get these to work for me.
This will be used primarily by someone that is not familiar with excel so hopefully nothing too complicated.

Any help would be appreciated. TY
Gray.

Please try

=FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,Vehicles1:End!B2:B90)&"</m></x>","//m")

insert new sheet of Vehicles# before sheet end

WOW, thank you very much. This works perfectly.
Could I ask you to explain the formula for me? I am not sure what is going on here.

Thank you again for the solution.
Gray

ha ha ha .. I was just fooling with the formula and I discovered the exact same thing ... UNIQUE and SORT.

Now I just need to figure out what that FILTERXLM function is doing in my example. I do not understand it at all.
TY very much.

Gray

Thank you, yes that helps immensely. I understand XML I was just not sure what the <x><m> were .. but I am imagining that you can call those whatever you like.

I appreciate your time.
Thank you very much to your both for the formula and the explanation.
You've both been rep'd.

Gray.

Update:
In the end this did not help me. The number of sheets I need to reference along with the amount of rows brings the character length too high for excel to complete the formula. After the 2 sheet the calculation stops.

Looking for an alternative now.
Gray.

Added another Excel to this to show that it doesn't work after the 32,767 characters is reached
Just take Tab 6 and slide it after END and back

For Excel Insider try

=LET(z,VSTACK(Vehicles1:End!B2:B100000),SORT(UNIQUE(FILTER(z,z<>""))))

or Power Query
Change file path in red

Please Login or Register  to view this content.

Watch this for Power Query
https://youtu.be/KfuYxBDBkAo

WOW .. amazing again.
How do you come up with these things. Thank you so much.

Are you able to explain what this formula is doing?
1) the formula in the workbook looks a little different.
yours - =LET(z,VSTACK(Vehicles1:End!B2:B100000),SORT(UNIQUE(FILTER(z,z<>""))))
WrkBk - =LET(z,_xlfn.VSTACK(Vehicles1:End!B2:B100000),SORT(UNIQUE(FILTER(z,z<>""))))

2) it works as is but then I try to edit it or use it elsewhere it breaks it, just copy. Name error now.

Thank you for the Help.

Gray

This:

_xlfn.VSTACK

means that you do not have the VSTACK function.

This means that you are not on the insider channel.

Thank you for that. Looks like I am back to the drawing board. Power Pivot will not be helpful. I am not familiar nor have I ever used it and neither has the user.
Was looking for something a little simpler.

Might close this thread and post a question in the VBA thread and see if a macro might make more sense.

Thank you all.
Gray

No - you don't need to do that. I can move the thread for you.

Please stop quoting unnecessarily!

The thread has been moved and its title tweaked.

Please Login or Register  to view this content.