Unique Value Returns with Exact Match

[nhartman49]

I am looking for a way to extract unique exact values from a text string. I have found way to get multiple values but they are not exact.

Examples:

A1- Unique Values:
19
20
35
40

B1 - There were 40 cats who ran up a tree in 1920.

C1 - Result:
19
20
40

I would need the result to be 40 only because it is the only exact match to the criteria.

Thank you.

 

[jtakw]

Hi,

If I understand correctly:

Book1
	A	B	C
1	19	There were 40 cats who ran up a tree in 1920	40
2	20
3	40
4	35
Sheet320

Cell Formulas
Range	Formula
C1	=LOOKUP(2,1/SEARCH(" "&A1:A4&" "," "&B1&" "),A1:A4)

 

[nhartman49]

Jtakw,

Thank you for the quick reply. I will try this first thing tomorrow at work.

 

[Peter_SSs]

There is always a problem with this sort of thing if punctuation gets involved. jtakw's formula certainly returns 40 from your sample sentence. However, I note your sentence ends with a period "."

If the sentence was instead ..
In 1920 the number of cats that ran up a tree was 40.
.. then the suggested formula will not find the 40

Similarly if the sentence was
Although there was 40, another 5 ran down a hole.

Is that an issue for you?

Are you actually always looking for whole numbers like this example?

And what do you want to do if there is more than one exact match?

 

[nhartman49]

Jtakw,
Thank you for the suggestion, but Peter_SSs is correct. The return value I received was not the expect value that was needed. Instead the return value was a part of a date. I can not provide a screenshot for some reason, but there are multiple different punctuations in the cells. Essentially, this is what it looks like:

Cell A2:

58
61
99
94
93
76
75
67
66
74
50
57
56
55
54
41
40
52
51
79
73
92
91
98
97
69
68
01
02
65
64
04
03
08
07
84
83
63
62
96
95
78
77
06
05

Cell B2:
COMPLY WITH INTRM: CODE: 58 BASIC NO: 1387 REV LTR: AMEND: PART: KIT NO: 00
INSPECTION AND REPORTING OF P-8A AUXILIARY POWER UNIT (APU) PART NUMBER VERIFICATION. O-LEVEL MAINT.
COMPLY NLT NEXT 90 DAY MAINTENANCE CHECK. [SER# : P-8778] NLT 06JAN2012

Cell C2:

"Result"

 

[nhartman49]

Correction:

Cell C2:
"58"

 

[Peter_SSs]

The formula from post 2 correctly returns 58 for that sample.
Could you give a couple of samples (& the expected results) of text that could go in B2 where, for those same numbers in column A, the formula does not give the correct result?

 

[nhartman49]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD][table="width: 500, class: grid"]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]

[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 408"]
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody>[TR]
[TD="width: 544, bgcolor: transparent"]COMPLY WITH INTRM: CODE: 55 BASIC NO: 0899 REV LTR: AMEND: PART: KIT NO: 00 CONFIRMATION/INSP OF MODE S AIRCRAFT ADDRESS INSTALLED ON ALL ACTIVE NAVY AND MARINE CORPS AIRCRAFT TO BE PERFORMED BY O-LEVEL MAINT. TECHNICIAN WITH IN 30 DAYS OF DTG MESSAGE. UP TO AND INCLUDING AMENDMENT 1. UP TO AND INCLUDING AMENDMENT 1. [SER#: 0027] WEIGHT AND BALANCE IS N/A
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]74
[/TD]
[TD]1155
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][TABLE="width: 408"]
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody>[TR]
[TD="width: 544, bgcolor: transparent"]COMPLY WITH INTRM: CODE: 74 BASIC NO: 0015 REV LTR: AMEND: PART: KIT NO: 00 INSPECTION OF WIRING HARNESS INSTALLATION UNDER AUXILIARY BATTERY. O-LEVEL MAINTENANCE ACTIVITIES COMPLY WITH INSPECTION NLT 90 DAYS FROM DTG OF THIS NAVAL MESSAGE. [BUNO#: 168429]. DUE NLT 27JAN14.[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD]58
[/TD]
[TD]1468
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][TABLE="width: 408"]
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody>[TR]
[TD="width: 544, bgcolor: transparent"]Comply with Intrm: Code: 67 Basic No: 1155 Rev Ltr: Amend: 1 Part: Kit No: 00 ACB 1155 A1 ONE-TIME INSPECTION OF OPNAV 4790/138 AIRCREW SYSTEM RECORD FOR SIGNAL, SMOKE ILLUMINATION MARINE (FLARE), MK-124 MOD 0, LOT # MEI93C001- 019 (WUC 91B84) TO BE COMPLIED WITH BY O LEVEL OR ABOVE NO LATER THAN 30 DAYS AFTER DTG OF THIS MESSAGE. [Ser#: 0414]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD]55
[/TD]
[TD]0899
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][TABLE="width: 408"]
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody>[TR]
[TD="width: 544, bgcolor: transparent"]COMPLY WITH INTRM: CODE: 58 BASIC NO: 1468 REV LTR: AMEND: PART: KIT NO: 00 INSP OF AIR TURBINE STARTER (ATS) MAGNETIC PLUG. THIS TD SHALL BE ACCOMPLISHED AT O-LEVEL MAINTENANCE WITHIN 25 FLIGHT HOURS FROM DATE TIME GROUP OFT HIS NAVAL MESSAGE. [SER#: GRTF7149] ** DD NLT NEXT 25 FLT HOURS**[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD]67
[/TD]
[TD]0015
[/TD]
[/TR]
</tbody>[/TABLE]

Columns D and E are the lookup values. Example: So in column B1 I need the return value to be 55, and column C1 I need the return value to be 0899 and then B2 should be 074 and C2 would be 0015. I only brought these cells so close for ease, but these cells are multiple cells apart and there are thousands of these type records.

The recommended formula does not work for me, I just get a return value of #NA . Thank you all again for your help.

 

[jtakw]

The recommended formula does not work for me, I just get a return value of #NA . Thank you all again for your help.

Works for me...

B1 formula copied down and across to C4.

Book1
	A	B	C	D	E
1	COMPLY WITH INTRM: CODE: 55 BASIC NO: 0899 REV LTR: AMEND: PART: KIT NO: 00 CONFIRMATION/INSP OF MODE S AIRCRAFT ADDRESS INSTALLED ON ALL ACTIVE NAVY AND MARINE CORPS AIRCRAFT TO BE PERFORMED BY O-LEVEL MAINT. TECHNICIAN WITH IN 30 DAYS OF DTG MESSAGE. UP TO AND INCLUDING AMENDMENT 1. UP TO AND INCLUDING AMENDMENT 1. [SER#: 0027] WEIGHT AND BALANCE IS N/A	55	0899	74	1155
2	COMPLY WITH INTRM: CODE: 74 BASIC NO: 0015 REV LTR: AMEND: PART: KIT NO: 00 INSPECTION OF WIRING HARNESS INSTALLATION UNDER AUXILIARY BATTERY. O-LEVEL MAINTENANCE ACTIVITIES COMPLY WITH INSPECTION NLT 90 DAYS FROM DTG OF THIS NAVAL MESSAGE. [BUNO#: 168429]. DUE NLT 27JAN14.	74	0015	58	1468
3	Comply with Intrm: Code: 67 Basic No: 1155 Rev Ltr: Amend: 1 Part: Kit No: 00 ACB 1155 A1 ONE-TIME INSPECTION OF OPNAV 4790/138 AIRCREW SYSTEM RECORD FOR SIGNAL, SMOKE ILLUMINATION MARINE (FLARE), MK-124 MOD 0, LOT # MEI93C001- 019 (WUC 91B84) TO BE COMPLIED WITH BY O LEVEL OR ABOVE NO LATER THAN 30 DAYS AFTER DTG OF THIS MESSAGE. [Ser#: 0414]	67	1155	55	0899
4	COMPLY WITH INTRM: CODE: 58 BASIC NO: 1468 REV LTR: AMEND: PART: KIT NO: 00 INSP OF AIR TURBINE STARTER (ATS) MAGNETIC PLUG. THIS TD SHALL BE ACCOMPLISHED AT O-LEVEL MAINTENANCE WITHIN 25 FLIGHT HOURS FROM DATE TIME GROUP OFT HIS NAVAL MESSAGE. [SER#: GRTF7149] ** DD NLT NEXT 25 FLT HOURS**	58	1468	67	0015
Sheet332

Cell Formulas
Range	Formula
B1	=LOOKUP(2,1/SEARCH(" "&D$1:D$4&" "," "&$A1&" "),D$1:D$4)

 

[Peter_SSs]

Columns D and E are the lookup values. Example: So in column B1 I need the return value to be 55, and column C1 I need the return value to be 0899 and then B2 should be 074 and C2 would be 0015. I only brought these cells so close for ease, but these cells are multiple cells apart and there are thousands of these type records.

The recommended formula does not work for me, I just get a return value of #NA . Thank you all again for your help.

Because you have changed your columns, and now require the formula to be copied down (wasn't mentioned originally) some slight changes were needed to the original post 2 formula and jtakw has given you those changes. I confirm that the amended formula works for me too, subject to the point below.

You mentioned that the expected result for cell B2 should be 074. How can that be when 074 is neither in cell A2 nor in D1:D4?