Possible to use formula?

[Deleted]

I've been asked to try to find a formula that will look for a duplicate of a number in one cell against a cell that has a "range" of numbers. For ex:

I am looking to see if this number is duplicated

"7105+65" (land tract # ) and another cell has "7105+60 - 7206+10". 

So the number 7105+65 is duplicated in the range given.

is there any way to do a formula in a blank column to highlight or capture this duplication in some way without having to enter all of the tract numbers in the range?

[#2]

Is the range written in the same cell or it's in 2 near cells?

If it's all in the same cell, are all the numbers made of a 4 digits number, a + and a 2 digits number?

At the moment I think you should use the "separate datas in column" feature to have all numbers in different columns (7105, 60, 7206, 10) and then check if a specified number (7105, 65) is within the cells written.

[#3]

Is the range written in the same cell or it's in 2 near cells? The range is within one cell

If it's all in the same cell, are all the numbers made of a 4 digits number, a + and a 2 digits number?

the number is a land tract # or also called a station number and they are written as 7105+65  

At the moment I think you should use the "separate datas in column" feature to have all numbers in different columns (7105, 60, 7206, 10) and then check if a specified number (7105, 65) is within the cells written. -

I think dividing them up like that in columns would not be feasible since multiple tract numbers end in 65, 10, 15 etc. Each of those numbers would be called out as a duplicate when in fact it's not the number. The full number I need to look must include the 4 digits with a plus sign and the two digits.

I am currently manually changing the cells with a range by inserting additional rows to put in the tract numbers that are in the range into individual cells.   thanks so much for your quick response - much appreciated!

[#4]

Hi Denise,

Other words you'd like to find if the string in the cell is part of another strings in the columns cells. The question is you compare the cell or parts of the cells, i.e.

7105+65-8888+15   and
9999+10-7105+65

shall match or that's not the case, only

7105+65   and
9999+10-7105+65

shall work?

[#5]

Thanks for the quick response. I am trying to find out if one "tract" number that is one cell by itself can look in another cell to see

Example:

This number is in the cell shown (A5)

7105+65   ex: (column A - Row 5)

And  in a another cell (C12) is this range (group) of tract numbers

9999+10-7105+65 ex: (column C - Row 12)

So, I am trying to find a formula that will recognize that in C12  is a range (group) of numbers and would look to find if the number in A5 is within that range (group) of numbers.  

I know this is very confusing. I apologize for that. Thanks for your help!

[#6]

Hi Denise,

If simply to check A5 is within C12 or not when

=IF(ISNUMBER(SEARCH(A5,C12)),"In range", "Not in range")

If you have set of numbers in column A and different ranges in column C to find if every of A is within any of C will be bit more complex, but better to know how exactly your data are organized. If something like this better to have small Excel file sample.

[#7]

Sorry it took so long to respond - the team changed the formatting on the spreadsheet to see if it would help with this formula - the + signs have been removed and they have only used one number per cell. So the new question is - as you can see on this sample, the end # should match the start # of the next row and if it doesn't it will need called out  - so I am using this formula for that =IF(A4=B3,,"Overlap") which is working fine, but we will also need to know if any number that is "between" the start and end number is duplicated in any other row. Thanks for all the help that has been supplied so far.

START	END
790866	788366
788365	786721
786721	784104
784104	782292
782292	780500
780500	778700
778700	777900
777900	777100
777100	776900
776900	776600

[#8]

Denise, please clarify a bit what do you mean under the duplication. Does that mean any number between Start and End in one row (includes Start and End?) shall not be in the range between Start and End at any other row? Perhaps you may add the example(s) of the duplications to your sample.

[#9]

Sure!   So using row #1 as an example any number from the start number of  790866,  790865, 790864 down to 788366 would be in any other cell it would call it out.

For example - So in row 3 below I changed the start number and the end number that would be included within the numbers of the start and end of the first row. Row 3 would need to be called out as duplicating the numbers that would already be included in row one.

I hope this helps?

START	END
790866	788366
788365	786721
788369	790862
784104	782292
782292	780500
780500	778700
778700	777900
777900	777100
777100	776900
776900	776600

[#10]

Sorry, just seen where you asked if it included the start and end numbers. Yes, but not sure how it would be included in the formula.

The end number must (should) match the beginning number of the next row. That is how we know that they are not skipping an area in the field. If these are included it would make it look as if there is a duplicated number, but if it isn't included, we wouldn't know if someone was claiming that area too.

[#11]

Denise, thank you for the clarification.

I assume your numbers goes down from start to end, thus corrected end of third row in your latest sample. If they may go in both directions things become more complicated.

The formula could be

=IF(SUMPRODUCT((B2>$B$2:$B$110)*(B2<$A$2:$A$110))+SUMPRODUCT((A2>$B$2:$B$110)*(A2<$A$2:$A$110)),"Duplication","Correct")

as in attached file, not sure so far about including Start End values into it.

[#12]

Thank you!

[#13]

Good morning, I did several experiments with this formula by changing the data, and unfortunately, it's not going to work for what we need it to. I will keep researching. Thank you for your help! It is greatly appreciated!

[#14]

Hi Denise - could you attach small sample file without sensitive information to check what is wrong with formulas?