DizzyDoodle
New Member
- Joined
- Mar 24, 2019
- Messages
- 2
I'm having two problems with the match function......
The first:
The match function is not finding every value of which I know exists in the database. I've made a small test that represents this problem below.
When using the below match function I can identify the central variable { C1 = X00807 } in only the Datapoint 2 and not Datapoint 1.
=MATCH("*"&C1&"*",I1:I9,0)
<colgroup><col></colgroup><tbody>
</tbody><strike></strike><strike></strike><strike></strike>
The first:
The match function is not finding every value of which I know exists in the database. I've made a small test that represents this problem below.
When using the below match function I can identify the central variable { C1 = X00807 } in only the Datapoint 2 and not Datapoint 1.
=MATCH("*"&C1&"*",I1:I9,0)
| Datapoint 1 = ------------------------------------------------------------------------------------------------------- Coal Advisory Group#X00807#Brian Ricketts#http://ec.europa.eu/transparency/re...groupID=807#Member#B#Employees/workers#Expert Group on the exchange of information on Best Available Techniques related to industrial emissions (IED Article 13 Forum)#E02611#http://ec.europa.eu/transparency/re...groupDetail.groupDetail&groupID=2611#Member#C ------------------------------------------------------------------------------------------------------- Datapoint 2 = -------------------------------------------------------------------------------------------------------
<colgroup><col></colgroup><tbody> </tbody> This is odd because one would expect match to identify the number in the array of Datapoint 1. Indeed, if I remove Datapoint 1 it does not find X00807 in Datapoint 2. I did some testing, and it will find X00807 if I change the Datapoint to the following: | |
| Datapoint 1: ------------------------------------------------------------------------------------------------------- <strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>Coal Advisory Group#X00807#Brian Ricketts#http://ec.europa.eu/transparency/re...groupID=807#Member#B#Employees/workers#Expert Group on the exchange of information on Best Available Techniques related to industrial ------------------------------------------------------------------------------------------------------- <strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>This suggests to me that the string is too long for the match function to find the value, but why? Is there a better way I can search for it? It is far too tedious to sift through and change the data manually, as it conisists of around 11,000 rows. This brings me onto my second question: Forgetting the problem with uncovering all of the datapoints, for the ones that do work I can use the below formula to look through the array: |
<colgroup><col></colgroup><tbody>
</tbody>
=SMALL(MATCH("*"&C1&"*",I1:I9,0),1)
This works fine, however, when I try to use values of n>1 it returns a #Num ! error. Why? I know the array consists of many variables, as if I delete datapoints in the array the return value changes. What am I doing wrong?
If you'd like to understand what I am trying to do:
Within large strings I need to find the ones that contain a particular substring corresponding to given codes, such as the one I used in the example (X00807), and then list them so that I know which rows contain a string with that code inside.
Any help would be greatly appreciated.
This works fine, however, when I try to use values of n>1 it returns a #Num ! error. Why? I know the array consists of many variables, as if I delete datapoints in the array the return value changes. What am I doing wrong?
If you'd like to understand what I am trying to do:
Within large strings I need to find the ones that contain a particular substring corresponding to given codes, such as the one I used in the example (X00807), and then list them so that I know which rows contain a string with that code inside.
Any help would be greatly appreciated.
