extract data

[ANGELBB3J]

I would like to find a formula(s) or VBA macro that can help me to do a couple of things
-- extract data from an original table to another location (not using advanced filter function) if the cell contains a certain word
-- extract unique values from a dynamic range and combine them in alphabetical order into one cell of text, separating the words with a comma (if there are more than two) and an "&" between the last two words. Please help

 

[Chihiro]

What version of Excel do you have access to?

 

[Chihiro]

Here's sample of first requirement.
2nd part will need further clarification.

1. Is there any pattern that separates words in a given cell, other than "and"?

2. If there is unique combo (ex: Skunk and Raccoon) of words in a cell, does this go as pair or need to be separated and then concatenated using delimiter?

 

[ANGELBB3J]

What version of Excel do you have access to?

I'm using Excel 2013

 

[ANGELBB3J]

Here's sample of first requirement.
2nd part will need further clarification.

1. Is there any pattern that separates words in a given cell, other than "and"?

2. If there is unique combo (ex: Skunk and Raccoon) of words in a cell, does this go as pair or need to be separated and then concatenated using delimiter?

Both are very good questions. My example did present some challenges.

1. We can just eliminate the "&." I can put it in manually when I copy the text over. Would it be possible to count the number of unique variables and use an if/then statement? I don't know if that's even possible.

2. For unique combos, they can be concatenated as a whole pair/group of words

Thank you again for your.

 

[Chihiro]

You'll need UDF for the solution. I've included "&" for last word.

Hmm, for point 2. It's easier to split them and sample below does that.

I haven't done thorough testing on UDF. Let me know if you encounter issues.
Sample usage is in attached file.

Public Function UnqConcat(rng As Range, Optional sDelim As String = " ", _
    Optional cDelim As String = ",", Optional amp As Boolean = True) As String

    Dim a, x, dic As Object
    Dim i As Long, j As Long
    Dim res As String
    a = rng.Value2
    'Create dictionary and add unique words to key, splitting by space
    Set dic = CreateObject("Scripting.Dictionary")
    With dic
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a)
            x = Split(a(i, 1), sDelim)
            For j = 0 To UBound(x)
                If StrConv(x(j), 2) <> "and" Then
                    .Item(Trim(StrConv(x(j), 3))) = 1
                End If
            Next
        Next

    End With
    'Using ArrayList sort key alphabetically, ascending
    With CreateObject("System.Collections.ArrayList")
        For Each Key In dic.Keys
            .Add Key
        Next
        .Sort
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 0 To .Count - 1
            dic(.Item(i)) = 1
        Next
    End With
    i = 1
    'Concatenate sorted list and use & for last item when amp option is true
    For Each Key In dic.Keys
        If i = dic.Count And amp Then
            res = res & " & " & Key
        Else
            res = IIf(Len(res) = 0, Key, res & cDelim & " " & Key)
        End If
        i = i + 1
    Next
       
    UnqConcat = res
End Function

EDIT: This code is for Windows based machine only. MAC does not have ArrayList and will need custom sort using array (bubble sort logic etc).

 

[ANGELBB3J]

This workbook that you did works great.

I have made some changes to my example which reflect my actual data a little bit better. My original data set is awfully large and not as well organized. I'm trying to apply your formulas but am running into some problems.

1. Does your original data set have to be located to the left of the filtered data destination?

2. Does the "filtering" column have to be on the left-most side of data sets?

3. Is it possible for the original data set to be located on another worksheet?

 

[Chihiro]

So what are you searching for this time?

Previous sample was searching for text string that occurred with another string. But this sample looks like you need to search for date? However, it is unclear which field you need to search in. Is it in "Staff Pres dates"?

If so, since date value and string can't be compared in raw form, you'll need to perform some conversion to date field.

 

[ANGELBB3J]

My search criteria is located in H3. I believe that I would use TEXT ($H$3,m/d/yyyy) for the conversion. Would that work within the larger?

 

[Chihiro]

Close. TEXT($H$3,"m/d/yyyy")
Note that you need double quotes for Format argument for Text function.

Assuming lookup range is Staff Press dates.
in H6:
=IFERROR(INDEX($U$6:$U$59,SMALL(IF(ISNUMBER(SEARCH(TEXT($H$3,"m/d/yyyy"),$AC$6:$AC$59)),ROW($A$6:$A$59)-5),ROWS($A$1:$A1))),"")

Confirmed as Array.

In I6:
=IFERROR(INDEX($V$6:$V$59,SMALL(IF((H6=$U$6:$U$59)*(""<>$U$6:$U$59),ROW($A$6:$A$59)-5),COUNTIF($H$6:H6,H6))),"")

Confirmed as Array.

As for your questions.

1. No it could be anywhere, just change reference range accordingly.

2. No, unlike VLOOKUP, lookup using INDEX construct can look up to left or right without issue.

3. Yes, you just have to change reference accordingly to include sheet name reference.

NOTE: UDF may need adjustment as it looks like your requirement for concatenation changed. If you need help in altering, detail what is expected as result, for few different examples.