Excel: Putting together two different complex formulas (Unique distinct list & Split)

Question

Hello all you amazing people

I wanted to do two things

• populate a unique distinct list from a long list of repetitive values
• extract component of a text delimited with hyphen

I found solutions to each one of these problems at different places.

Unique distinct list here: http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

The formula is

{=INDEX(A2:A65000,MATCH(0,COUNTIF($B$1:B1,A2:A65000),0))}
Where Column B is where the unique list gets populated

---

And Extracting (splitting text) from here http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/

Function ExtractElement(str, n, sepChar)

' Returns the nth element from a string,
' using a specified separator character
Dim x As Variant
x = Split(str, sepChar)
If n > 0 And n - 1 <= UBound(x) Then
ExtractElement = x(n - 1)
Else
ExtractElement = ""
End If
End Function

The formula below demonstrates how the ExtractElement function can be used in a formula.

=ExtractElement("546-339-909-944",3,"-")

This formula returns 909, the third element in the string (which uses a "-" as the delimiter).

---

These are great and solve a lot of what I am trying to do. But I am also trying to do both of these functions together to another column.

I have a column with values such as:

Banana - Yellow - Fruit
Sun - Yellow - Star
Blood - Red - Liquid
Exit - Red - Signage

I am trying to get the result as

Yellow
Red

I wish to do this all with formulas and don't want to use helper columns. I don't mind VBA (as you can see, the second link here is vba).

Any help is appreciated. Thanks a million!

Sriram

Answer 1

An example using ADO.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim i As Integer

''http://support.microsoft.com/kb/246335

strFile = ActiveWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''The connection string is set up for 
''headers, so you can use column names
''You can substitute a named range 
''for [Sheet8$]
strSQL = "SELECT DISTINCT " _
& "Mid(ColName,Instr(ColName,""-"")+1," _ 
& "InStrRev(ColName,""-"")-Instr(ColName,""-"")-2) " _
& "FROM [Sheet8$]"

rs.Open strSQL, cn, 3, 3

''Copies the results to a worksheet
Worksheets("Sheet7").Cells(2, 1).CopyFromRecordset rs

Answer 2

There were some useful links in your question - thanks.

I did this as follows using your sample data Banana - Yellow - Fruit Sun - Yellow - Star Blood - Red - Liquid Exit - Red - Signage

step 1 Copied it into notepad and opened it in excel and ran through the wizard to extract the delimiters " " and "-". This placed the data as text in 3 adjacent columns which I then named list1,list2 and list3 in columns A,B and C (respectively these were in ranges A2:A5,B2:B5,C2:C5). It would be better to use a separate sheet in the workbook to do this. I kept the first row clear.

step 2 merged the lists from step1 into one column (in my case now column D) by using the following formula starting in cell D2:

{=IFERROR(INDEX(List1,ROWS($D$1:D1)),IFERROR(INDEX(List2,ROWS($D$1:D1)-ROWS(List1)),IFERROR(INDEX(List3,ROWS($D$1:D1)-ROWS(List1)-ROWS(List2)),"")))}

which is an array formula that should be entered using CTRL + SHIFT + ENTER and then copied down. This range I called ListSource (d2:d13). Again I am not using the first row.

step 3 To extract only the duplicates in column E starting at cell E2 this formula did the trick

{=IFERROR(INDEX(ListSource,MATCH(0,COUNTIF($E$1:E1,ListSource)+IF(COUNTIF(ListSource,ListSource)>1,0,1),0)),"")}

which again is an array formula that should be entered using CTRL + SHIFT + ENTER and copied down into the other cells within the column. Again there is nothing in row 1 although you could add some headings.

This produced the desired output Yellow Red

You may decide that using the import wizard in step1 is not strictly a programming solution so it may be possible to automate this step.

Hope this helps.