Return Corresponding Single Unique Distinct Value From A List Of Sentences

[Saaseend]

Hello,



Struggling with a heap of text that needs to be sorted. (I have about 10.000 entries in column A)

I'm not very proficient in excel, but I believe I need to

1.return single unique distinct values in column C,
2.based on the values in column A
3.with the lookup range being $H

A1 <colgroup><col width="64"></colgroup><tbody> </tbody>	B	C	D	E	F	G	H
pear <colgroup><col width="64"></colgroup><tbody> </tbody>		Two pear or two pair? <colgroup><col width="64"></colgroup><tbody> </tbody>					The apple tree is next to the house <colgroup><col width="64"></colgroup><tbody> </tbody>
banana <colgroup><col width="64"></colgroup><tbody> </tbody>		#N/A <colgroup><col width="64"></colgroup><tbody> </tbody>					Animals are friends, not food <colgroup><col width="64"></colgroup><tbody> </tbody>
chicken <colgroup><col width="64"></colgroup><tbody> </tbody>		A chicken is not a cow <colgroup><col width="64"></colgroup><tbody> </tbody>					Two pear or two pair? <colgroup><col width="64"></colgroup><tbody> </tbody>
cow <colgroup><col width="64"></colgroup><tbody> </tbody>		My cat resembles a cow <colgroup><col width="64"></colgroup><tbody> </tbody>					A chicken is not a cow <colgroup><col width="64"></colgroup><tbody> </tbody>
horse <colgroup><col width="64"></colgroup><tbody> </tbody>		a gift horse is better than no horse <colgroup><col width="64"></colgroup><tbody> </tbody>					My cat resembles a cow <colgroup><col width="64"></colgroup><tbody> </tbody>
house <colgroup><col width="64"></colgroup><tbody> </tbody>		The apple tree is next to the house <colgroup><col width="64"></colgroup><tbody> </tbody>					a gift horse is better than no horse <colgroup><col width="64"></colgroup><tbody> </tbody>
apple		the apple grows on the tree <colgroup><col width="64"></colgroup><tbody> </tbody>					I wont tell you <colgroup><col width="64"></colgroup><tbody> </tbody>
							To be or not to be <colgroup><col width="64"></colgroup><tbody> </tbody>
							The apple grows on the tree <colgroup><col width="64"></colgroup><tbody> </tbody> exe

<tbody>
</tbody>

Using Vlookup I get duplicate entries. No bueno.


Now, I've done some googling and all tutorials seem to return either multiple unique values in a column.

I need a single distinct value, as specified in the added example.

I hope this the problem is explained clearly enough for you. It's most likely a simple fix, but I can't seem to figure it out.

Anyone who could help/offer a formula would be greatly appreciated!

 

[Peter_SSs]

Re: Excel: Return Corresponding Single Unique Distinct Value From A List Of Sentences

Welcome to the MrExcel board!

Formula approach:

Book1
	A	B	C	D	E	F	G	H
1
2	pear		Two pear or two pair?					The apple tree is next to the house
3	banana		#NUM!					Animals are friends, not food
4	chicken		A chicken is not a cow					Two pear or two pair?
5	cow		My cat resembles a cow					A chicken is not a cow
6	horse		a gift horse is better than no horse					My cat resembles a cow
7	house		The apple tree is next to the house					a gift horse is better than no horse
8	apple		The apple grows on the tree					I wont tell you
9								To be or not to be
10								The apple grows on the tree
Sentences

Cell Formulas
Range	Formula
C2	=INDEX(H$2:H$10,AGGREGATE(15,6,(ROW(H$2:H$10)-ROW(H$2)+1)/(ISNUMBER(SEARCH(" "&A2&" "," "&H$2:H$10&" "))*ISNA(MATCH(H$2:H$10,C$1:C1,0))),1))

Macro approach:

Sub UniqueSentences()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, j As Long
  Dim s As String
  
  a = Range("H2", Range("H" & Rows.Count).End(xlUp)).Value
  Set d = CreateObject("Scripting.Dictionary")
  For Each itm In a
    d(" " & itm & " ") = 1
  Next itm
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    s = " " & a(i, 1) & " "
    b(i, 1) = "#N/A"
    For Each itm In d.keys
      If InStr(1, itm, s, 1) > 0 Then
        b(i, 1) = itm
        d.Remove itm
        Exit For
      End If
    Next itm
  Next i
  Range("C2").Resize(UBound(b)).Value = b
End Sub