Dear All:
I am trying to extract a list of values in a table, on a per row basis, following a procedure as follow:
get value.jpg
shall locate first within E2:W2, all labels which contain:
- condition1
- on the left of the label, before the first "_", the different possible values wich exist before "_configuration" (valueA, valueB , valueB2 etc.), for each row, in B3:B11
- example: "valueC_configuration" in B3:B11, we would look after "valueC" types, so our selected labels in this first step will be: "valueC_data_suffix1" and "valueC_data_suffix2" (two matches)
- notice: in B3:B11, there could be a single configuration value, or multiple, comma-separated, in which case we shall locate all corresponding labels in E2:W2
- example: "valueA_configuration,valueB1_configuration" in B3:B11, here we look for "valueA" and "valueB1" types, this would return "valueA_data_suffix1", "valueA_data_suffix2" and "valueB1_data_suffix1" (three matches)
- condition2
- on the right of the label, after the last "_", any labels with "suffix" as a root (suffix1, suffix2, etc.)
- it means that any root which is not "suffix" shall be excluded
- example: "valueE_configuration" in B3:B11, here we look for "valueE" on the left (condition1) and "suffix" on the right (condition2), this would return "valueE_data_suffix1", "valueE_data_suffix2" (two matches), however "valueE_data_select1" would be excluded because of condition2
then shall return the value(s) of the arrays of the selected labels, for the corresponding row:
- if there is a single array (single label), then it has to be a single result
- example, "valueG_configuration" woud lead to a single label "valueG_data_suffix3", this would return a single value, QCUQB
- if there are multiple arrays, the results is as follow:
-- if there are multiple suffixes for the same configuration, then we separate the results with a comma
-- example, "valueC_configuration" (single configuration) woud return 2 suffixes, which we display as follow: UYBKS,JGUYV (separator: comma)
-- if there are multiple configurations, then we separate such results with an hyphen
-- example, "valueA_configuration,valueB1_configuration" (two configurations) woud lead to 1 suffix for the configuration1, and 2 suffixes for the configuration 2, which we display as follow: GFMLV,ZBWUK-GLPGU (separator: comma, then hyphen)
Any help would be much appreciated. The file is attached. Thanks.
Bookmarks