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

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

---

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

---

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.

---

For the specific case that you have shown, I modified your formula to extract colors using formulas instead of using the VBA code.

=INDEX(RIGHT(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1),LEN(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1))-FIND("-",LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1))),MATCH(0,COUNTIF($B$1:B1,RIGHT(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1),LEN(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1))-FIND("-",LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1)))),0))

Like yours this is an array formula; so hit Ctrl-Enter.

You can then copy & paste this formula from B2 down and when you get #VALUES, there are no more uniques. Also if you modify the values in column A, you may need to copy formula to more cells if unique values increase.