Unique Value Returns with Exact Match

nhartman49

New Member
Joined
Oct 10, 2018
Messages
6
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi,

If I understand correctly:


Book1
ABC
119There were 40 cats who ran up a tree in 192040
220
340
435
Sheet320
Cell Formulas
RangeFormula
C1=LOOKUP(2,1/SEARCH(" "&A1:A4&" "," "&B1&" "),A1:A4)
 
Upvote 0
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?
 
Last edited:
Upvote 0
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"
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody> </tbody>



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.
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody> </tbody>

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]
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody> </tbody>

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**
<colgroup><col width="544" style="width: 408pt; mso-width-source: userset; mso-width-alt: 19894;"> <tbody> </tbody>

67
0015

<tbody>

</tbody>

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.
 
Upvote 0
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
ABCDE
1COMPLY 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/A550899741155
2COMPLY 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.740015581468
3Comply 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]671155550899
4COMPLY 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**581468670015
Sheet332
Cell Formulas
RangeFormula
B1=LOOKUP(2,1/SEARCH(" "&D$1:D$4&" "," "&$A1&" "),D$1:D$4)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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