Ask a new question

Excel Convert Range to Column and Exclude Blanks / Almost Blanks

Searching on the net there are a number of solutions to converting a range (2 dimensions) to a column (1 dimension), and separately there are a number of solutions to eliminating blanks / zeros and "" for 1 dimension to 1 dimension.

However I cannot find a formula (not VBA) solution to do both in one step.  Is this possible and if so how?

I could do it in 2 steps with a helper in between, however the intermediate step of range to column rapidly gives me lots of rows even with small ranges eg 20 x 20 range obviously gives 400 rows for the intermediate step - which I would like to avoid - if possible.

Any solutions out there for this?

Many thanks

Hi

Perhaps this video might help you

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

Please,
Consider marking this reply as the answer to your question if it does so.
It will help others in the community with similar problems or questions.
Thank you in advance


Regards
Jeovany CV

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Many thanks - the UNIQUE function is good, but from what I can see it would still require 2 steps - first to get the range into a single column, then use UNIQUE to screen out the repeats etc.

Am I missing something?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Say we have data in the range A2 to J999 with many blanks at the bottom of each column as well as blanks embedded within the columns and are using Excel 365.  Pick a cell and enter:

=FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE($A$2:$J$999)),",","</b><b>")&"</b></a>","//b")

For example:

GSNU2020

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

This should be possible using the Query Editor.  Share some data and show the expected result.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 27, 2023 Views 888 Applies to: