Spliting a string from one list and making a list box select from that list

[tinkerz1]

Hi,

I have 2 sets of locations from different spreadsheets but in different formats.

The first is in this format:

LISTA:

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece,

Europe

The second list is in the format:

LISTB:

Europe

France

Germany

UK

Greece

Belgium

I have manged to great a string from list B to select data from List A but if List B has no data I can not make a full string to look into List A and collect the data.

So I need to look at selecting LIST A's string of data and splitting it by comma's then make LIST B look for the individual countries.

Is it concat and AGGR?

Thanks.

[tinkerz1]

So this works if I select both lists

if

(concat(distinct [Issue - Location],',')=GetFieldSelections(Location,',',getselectedcount ([Location])),[Market Risk Threshold Data])

No I just have to make the connections between A and B automatically as before

Spreadsheet A will carry a list of cells.

Groups

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

Europe

Then in spreadsheet B:

Country Data

Europe

Greece

Wales

Germany

UK

[sunny_talwar]

Have you checked this application?

May be this is what you are looking for

[tinkerz1]

Not quite what I am after.

When I select the single list of countries I want to find its match in the group of countries.

So if I select:

France

Germany

The concat string is France,Germany

I would like this information to be matched in the group of countries from spreadsheet A.

And if I select a field from spreadsheet A that looks like

Europe,Germany,UK.

I want that data to be selected in the single list of countries from spreadsheetA

[tinkerz1]

=

if(concat(distinct [Issue - Location],',')=[Location],[Market Risk Threshold Data])

This is working nicely, and finds the selections, just looking for a solution the other way around,

[tinkerz1]

Yes thanks Sunny for this, I almost have the answer,

When using subfield the data is split on a one to many relationship. so if I select fact table country I return all the GroupCountry data a country is part of.

I need to match they complete selection once from the fact table

and if i select group count once, return those countries from the fact table,

in your relationship i will pull out any group country list France is a part of.

If France is a single selection I just want that, if I select France and Germany, I just want France,Germany from the grouptable.

[sunny_talwar]

Oh so you are saying that you are looking for a and search. So show all those within GroupCountry where it matches with Country1 and Country2

So if Europe and Germany is selected you want to see these two rows seleced in GroupCountry?

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

[tinkerz1]

a complete match Sunny

Europe,Germany is not in that list.

I hope you see it now

[sunny_talwar]

That make sense. Let me see if I can work this out

[sunny_talwar]

I am not sure how we can do this. May MarcoWedel‌ might have a work around he can think of. I am going to very interested in knowing this for myself as well.

Best,

Sunny

[tinkerz1]

I am thinking a link table is needed after both tables are loaded.