Return Corresponding Single Unique Distinct Value From A List Of Sentences

S

Saaseend

New Member
Joined
Sep 30, 2017
Messages
1
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

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


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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Re: Excel: Return Corresponding Single Unique Distinct Value From A List Of Sentences

Welcome to the MrExcel board!

Formula approach:


Book1
ABCDEFGH
1
2pearTwo pear or two pair?The apple tree is next to the house
3banana#NUM!Animals are friends, not food
4chickenA chicken is not a cowTwo pear or two pair?
5cowMy cat resembles a cowA chicken is not a cow
6horsea gift horse is better than no horseMy cat resembles a cow
7houseThe apple tree is next to the housea gift horse is better than no horse
8appleThe apple grows on the treeI wont tell you
9To be or not to be
10The apple grows on the tree
Sentences
Cell Formulas
RangeFormula
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:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Count Distinct Values in Filtered Table across Multiple Columns
Replies
6
Views
636
Fluff
Fluff
Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
367
zero269
Z
B
  • Solved
Lookup value in two columns/cells and return a value from a different column
Replies
5
Views
822
Fluff
Fluff
P
  • Solved
How to find the maximum value among unique distinct data's and Return adjacent column data respective to the maximum value ?
2
Replies
10
Views
961
Fluff
Fluff
H
  • Question Question
comparing values in a column against multiple sheets and returning subsequent values from other columns
Replies
1
Views
1K
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,711
Messages
6,174,028
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top