transpose/convert range of rows and columns to one column, removing blanks

Forget using formulas.

Format range A:L as a table. The table will resize if you add more rows.

Then use this Power Query (Get & Transform Data) to extract the unique values:

Please Login or Register  to view this content.

Choose Load To, and load to a Table, in range M1.

Now you can simply refresh this query to update your column of distinct values.

Using Power Query, you can load the data from the original spreadsheet and keep the link. Then any updates to the source document will automatically the target document. Load your data to Power Query, Unpivot all columns. Filter the column you have created to remove all nulls. Load and close to your spreadsheet. After making changes to the source document, click on refresh at least twice and the target document will update. If you need additional help on PQ, a good book is "M is for (Data) Monkey," This is a very powerful feature in Excel. It quickly manages reconfiguration of data quickly and easily.

There's a link in my signature, to help you through using the code I gave you.

With respect to automatically updating - you _could_ write some VBA to catch change events, then refresh the query...

But clicking "Data > Refresh All" isn't much hardship. Keep it simple.

Look at this video on how to load the file. Then follow the steps, I have outlined.

Once you have the results loaded back into your spreadsheet, write a macro to refresh the data (remember you will need to refresh twice) using the On_Open event for the workbook.

https://www.youtube.com/watch?v=a7E29H5ZUmE

Originally Posted by Olly

There's a link in my signature, to help you through using the code I gave you.

With respect to automatically updating - you _could_ write some VBA to catch change events, then refresh the query...

But clicking "Data > Refresh All" isn't much hardship. Keep it simple.

Olly, is there a way to do this same code that you created, but have it not sort it alphabetically? I need it to stay in the same order it was in.

E.G.
A1
B1
C1
D1
E1
F1
G1
H1
I1
J1
K1
L1
A2
B2
C2
D2
etc...

*EDIT* nevermind, just figured out i just had to remove the sorted rows line at the end of the query settings > applied steps :D


alan, thank you so much but I'd like to use Olly's code, as I want this to be in the same sheet as original data, not a different workbook.

Thank you!!

Simply remove the "Sorted Rows" step:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks for the Rep!