CODEWITHSUNDEEP
excelformulasarray-formulas
Anthony Edmonds
Anthony Edmonds

Reputation: 33

Excel - Dynamic list from delimited cell / remove blanks from dynamic row

Program

Goal

Restrictions

Details

My original plan was to create a single cell which contained a comma delimited list, which would then be used to create a data validation list. Upon successfully creating a nicely formatted comma delimited list, it seems that you can't simply reference the list and have it translated by the data validation tool, and now I need to find an alternate method.

The data is held in a long row (not a column, and it can't be translated to a column easily) which is generated by summing various values above it. This means that there can be a lot of blanks between two values - this is unavoidable.

The following website has an array formula that works for removing blank cells from a column, however I have had no luck translating it into a formula that works for a row: http://www.cpearson.com/excel/NoBlanks.aspx

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

If anyone has any ideas on making this work for a row, I'd appreciate the help. It seems to require a bit more than just replacing the ROW() functions with COLUMN() - possibly something to do with the "1:" on the INDIRECT(), though I don't understand how the formula works enough.

I'll keep playing with it in the meantime, and of course if anyone has any other NON-VBA solutions to either the row data or the comma delimited data issue, I'm all ears.

EDIT: To clarify, there is a solution provided by the link above for taking a vertical set of data into a horizontal result lane, however this does not help with my horizontal data set.

Upvotes: 3

Views: 2698

Answers (1)

L42
L42

Reputation: 19727

is this what you want?

=IFERROR(INDEX($A$1:$E$1,1,SMALL(IF(LEN($A$1:$E$1)<>0,COLUMN($A$1:$E$1),9.9E+100),COLUMN())),"")

I just assumed data looks like this:
Data in Row1 and apply formula in Row2.

Result in Row2 would be data in Row1 without blanks.

Edit1:

I saw you need it for Data Validation List>
So i assume this is what you may need:

And using this formula:

=IFERROR(TRANSPOSE(INDEX($A$1:$E$1,1,SMALL(IF(LEN($A$1:$E$1)<>0,COLUMN($A$1:$E$1),9.9E+100),ROW(A1)))),"")

Hope this helps.
I'm just guessing what you want but this should give you idea on how to tweak tha array formula.

Upvotes: 1

Related Questions