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

**loganpmgoa** · 02-13-2019, 12:13 PM

Hi all,

I've been wracking my brain for days already trying to figure this out. I've searched all over the web and haven't come to an answer there either.

In the attached sample workbook, I need a formula that works fast and doesn't interrupt workflows that takes all the values of all the cells in a range pf rows and columns, removes the blanks, and outputs a single column with all these values in order, and outputs blanks at the end of the list, not zero's.

It needs to stay as a formula because all the data that are in these columns and rows are formulas linked to another sheet in the same workbook. The other sheet will be updated with more rows, in which case this range of data will update, and then the formula you guys might help me with will update.

In the attached sample, i have removed the formulas, but originally every cell from column A to L had formulas, and A to L is the range I need output into a single column N. The formulas go all the way down to row 12,000. So. there should be 144,000 cells in the range that this formula will be working with.

It doesn't matter how many helper columns I need, but I need it to work fast and update smoothly, so that most likely rules out array formulas since as far as i've seen they take forever to update and sometimes crash my system when looking through 144,000 formulas.

You guys are probably my last hope. If you guys can't figure this out, I might as well just give up.

Thank you so much!!

**Olly** · 02-13-2019, 12:28 PM

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.

**alansidman** · 02-13-2019, 12:29 PM

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.

**loganpmgoa** · 02-13-2019, 12:36 PM

olly, alansidman, thank you so much for your help. I've used power query only once, and I have no clue how to use it. I need to learn this fast, so I can't read a book about it.

Plus, my bosses want it to update automatically as you go without even needing to press a button.

Is there any way to accomplish this?

**Olly** · 02-13-2019, 12:39 PM

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.

**alansidman** · 02-13-2019, 12:41 PM

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

**loganpmgoa** · 02-13-2019, 01:11 PM

Oh, thank you so much for the powerquery help haha! Completely missed that. That is awesome btw that you have your own excel help website. I just bookmarked.

The reason we didn't want to have to refresh is because people accross our work network with just about zero tech experience will be adding to this workbook, and they'll be told to only touch the Master Sheet, not the others. But, if you can press "Data > Refresh All" from any Sheet, then that'll work great!

Or, actually I just found sfmagazine . com /post-entry/november-2017-excel-split-delimited-data-into-new-rows/ < this site

I'll reply you again if I can't figure out how to make it automatically update it the way I want, learning from the "IMPROVING THE WORK FLOW" section.

Thanks soooo much! Y'all are an amazing help guys. Added rep.

**loganpmgoa** · 02-13-2019, 01:19 PM

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!!

**Olly** · 02-13-2019, 01:22 PM

Simply remove the "Sorted Rows" step:

Please Login or Register  to view this content.

**loganpmgoa** · 02-13-2019, 01:23 PM

Thank you! I actually just figured that out and edited my post right before you replied :D

**alansidman** · 02-13-2019, 01:49 PM

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!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Transpose or Convert column data set to multiple columns-variable!

[SOLVED] Copy & Paste Columns to Specific Range while Removing Blanks & Duplicates

[SOLVED] Macro to convert and Transpose a Matrix of 16 Columns and 16 Rows

[SOLVED] How to transpose / convert columns and rows into single column?

[SOLVED] Create a column list out of table (rows and columns) while removing blanks

convert rows of street addresses into individual columns (no transpose)

Formula to convert/transpose columns to rows (and vice versa)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions