Data Validation List from two sources

[Jan Pingel]

Help - this one has stumped me a bit.

I am trying to create a data validation list dropdown, where the total list content is coming from two sources.
Thinking I can combine those two in some formula.

To further complicate things, those two sources are: one is a column range i.e. C3:C6, and the other is one field with a comma-separated list.

I have attached a small sample sheet to show the problem.

I am thinking that I need to combine those two sources dynamically to one source.
I have figured out how to combine them into one field with comma separated items.

To problems with that:
1. I want to eliminate duplicates
2. The Data Validation List source doesn't seem to accept a single field with comma delimited entries.

Any ideas? Any help appreciated!

-Jan

 

[Peter Bartholomew]

The problem does highlight some features that are lacking in Excel. For me, using Excel 365 and Charles Williams's FastExcel add-in, a solution might be

= LET(
  Combined, TEXTJOIN(",",,Attendees,Contributors),
  Expanded, TRANSPOSE(TRIM(SPLIT.TEXT(Combined,", "))),
  SORT(UNIQUE(Expanded)) )

Without the add-in that becomes

= LET( comma, ",",
  combined, TEXTJOIN(",",, Attendees, Contributors),
  n, LEN(combined),
  k, SEQUENCE(n+1),
  x, IF(MID(comma&combined, k, 1)=comma, k),
  y, IF(MID(combined&comma, k, 1)=comma, k),
  start, FILTER(x, x),
  end,   FILTER(y, y),
  split, MID(combined, start, end-start),
  SORT(UNIQUE(TRIM(split))) )

There is a traditional spreadsheet technique of replacing the separators by long strings of spaces. This allows the resulting string to by divided and a word extracted from each. It is rather clever and works well for smaller lists.

 

[Peter Bartholomew]

365 file

 

[Jan Pingel]

Thank you very much Peter! This is definitely a great step in the right direction.
I guess now all I need is to create the Data Validation List dropdown referencing the range.
I will check to see if your formula works on a table so that I will auto expand to the actual size of the list?
This way the dropdown will only contain the area I am looking for - if not I can figure out the length, and use an indirect formula for the full range.

Thanks again
-Jan

 

[Jan Pingel]

Hi Peter
Thanks again - I made it work. I used your formula and calculated the number of non-empty rows with
=MATCH(TRUE,ISBLANK(AB10:AB50),0)
And then use an indirect formula to return the range with non-empty rows.

I am very interested in your formula structure. Where can I learn more about this. Seems like you are able to identify a variable within the formula, define the variable, and then use it below inside the same formula. I did not know that was possible.

Thanks
-Jan

 

[Peter Bartholomew]

If the formula has worked I assume you have access to Excel 365.
BYW, I liked your formula using TEXTJOIN to amalgamate the lists prior to conversion to a validation list. Because I use dynamic arrays for all calculation, you do not need to count the entries or otherwise search for the end of the list. In the workbook I have attached here the Name 'ValidationList' refers to the dynamic range starting in cell $H$4#. I turned your source data into tables so that they are fully dynamic (if you add some names to the second row of other contributors, the table should extend and the names appear in the validation list).

I suspect my approach is somewhat controversial in that it rejects much of the fundamental spreadsheet wisdom such as direct cell addressing and relative references and, instead, builds solutions from less used functionality, such as named formulae and dynamic arrays. They are not new, though. They were ported to Windows as part of basic Excel.

Dynamic arrays were introduced in September 2018 and the LET function is even more recent.
Announcing LET in Excel (microsoft.com)
The latest shock was the LAMBDA function but I have yet to gain access to that.

Because I wasn't to know whether you have 365 or not, I have also included a Power Query solution to your problem:

let
    Source = Excel.CurrentWorkbook(){[Name="tblContributors"]}[Content],
    OtherContributors = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Other contributors", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Other contributors"),
    TrimmedText = Table.AddColumn(OtherContributors, "List", each Text.Trim([Other contributors]), type text),
    #"Removed Columns" = Table.RemoveColumns(TrimmedText,{"Other contributors"})
in
    #"Removed Columns"

let
    Source = Excel.CurrentWorkbook(){[Name="tblAttendees"]}[Content],
    Renamed = Table.RenameColumns(Source,{{"Attendees", "List"}}),
    AppendQuery = Table.Combine({Renamed, Contributors}),
    NoDuplicates = Table.Distinct(AppendQuery),
    Sorted = Table.Sort(NoDuplicates,{{"List", Order.Ascending}})
in
    Sorted

Note: These are M-language instructions, not Excel formulas.