Jun 15 2018
01:56 PM
- last edited on
Jul 31 2018
08:28 AM
by
TechCommunityAP
Jun 15 2018
01:56 PM
- last edited on
Jul 31 2018
08:28 AM
by
TechCommunityAP
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?
Jun 15 2018 02:54 PM
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.
Jun 16 2018 06:44 AM
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!
Jun 16 2018 07:23 AM
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?
Jun 18 2018 01:19 PM
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!
Jun 18 2018 03:19 PM
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.
Jun 20 2018 07:52 AM
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 |
Jun 20 2018 08:47 AM
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.
Jun 20 2018 09:09 AM - edited Jun 20 2018 09:12 AM
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 |
Jun 20 2018 09:25 AM
Jun 20 2018 09:25 AM
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.
Jun 20 2018 11:27 AM
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.
Jun 21 2018 07:18 AM
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!
Jun 21 2018 07:57 AM
Hi Denise - could you attach small sample file without sensitive information to check what is wrong with formulas?