Nov 11 2019 11:08 AM
Hello all,
I have a column that has text with activities that field technicians completed on meters. In this column, they input a meter #. However, sometimes they accidentally could input two meter #'s. I am interested in somehow conditionally formatting this column for each cell, that if they do have two meter #'s somewhere in the paragraph described, that it will flag the cell (color code, etc.). Each meter # is six digits long, so is there a way I could just search each cell, see if there exists two six digit numbers in that cell, and flag it? Or should I search each cell by text (look for "meter" twice in the paragraph, flag it)? Is this even possible?
Nov 11 2019 11:23 AM
Nov 11 2019 07:05 PM
If each Meter# consists of 6 digits and more than one Meter# is entered in the same cell, they must be separated by a delimiter. Is it the case?
If so, you can simply check the length of the cell content and if it is greater than 12 that simply means there are more than one Meter# present in the same cell.
Assuming your Meter# are in column A, then select the whole column A (when you select the whole column A, cell A1 will be Active Cell in the Selection) and make a new rule for conditional formatting using the formula given below and set the format as per your choice.
=LEN(A1)>12
Please make sure that the cell referred in the formula must be the first cell in the range in which you are applying the conditional formatting.
e.g. if you want to apply the conditional formatting to the range D2:D100 (assuming that the Meter# are in column D where D1 is the column header), select the range D2:D100 and use the below formula for the conditional formatting...
=LEN(D2)>12
If that doesn't work as expected, please upload a small sample file so that we can suggest a working solution considering your existing data.
Nov 11 2019 08:29 PM
@Subodh_Tiwari_sktneerIt's possible that one of us has mis-interpreted the orginal post by @npshibzk.
I read it to mean that the potential for two Meter Numbers is to be "buried" in a longer text entry describing work done by a technician on the meter in question. It's not just the entry of the meter number, in other words. If you are right, on the other hand, your suggestion will do the trick by highlighting the cell if it contains more than six figures.
Either way, though, both of us have asked for an uploading of an example of what @npshibzk is working on....always easier to work with an actual example.
Nov 11 2019 08:31 PM
Yes, I agree with you.
Nov 12 2019 05:30 AM
You are correct, the numbers are buried within text. I will upload some data for better reference. @mathetes
Nov 12 2019 05:43 AM
Here is a screenshot of the column of "Remarks" where the meter numbers would be documented in. There are over 11,000 rows, which is why I was wondering if there was an easier way to flag cells that have two meter numbers written in them. Also, I apologize, but it appears meter numbers are 7 digits long.
Nov 12 2019 06:09 AM
@npshibzkOh, my. What a tangled mess (if you'll excuse me). The fact that meter numbers are seven digits is not an issue. What makes this awfully difficult to sort out is that there are OTHER numbers in the same remarks (as I expected, frankly). So, although you could write a rule to get only seven digit numbers and somehow flag it, what you really need is a redesigned spreadsheet.
Some questions for you.
I'm going to assume that there are other columns, where you record such things as "Time Spent" or other things for billing/accounting purposes. Based on that assumption, I'm going to go back to my first suggestion, which was to add a column in which Meter Number is to be entered, and set it up with Data--Validate such that only a maximum of seven digits can be entered. Yes, this will be a lot of work. Think of it as a learning experience.
I've attached a sample showing how that data validation works. If you can upload a portion of the actual spreadsheet (showing other columns as well), I'd be happy to look at it further.
Nov 12 2019 06:12 AM - edited Nov 12 2019 06:13 AM
Each cell is a different customer/call.
Thank you for looking at this. The above screenshot shows all columns. I highlighted every cell that contains "METER" or "MTR" thus far to help narrow it perhaps. But still would require a lot of work. It is a mess!
Nov 12 2019 06:27 AM
@npshibzkSo you actually DO have meter number as a column of its own already (although I'd say it's ten digits long, given the "100nnnnnnn" format). And there are more than a few entries under the "Remarks" column where there is no reference to meter number. In your sample, unless I missed it (entirely possible), I didn't see any that referred to more than one meter.
My question now is: How important is it that you identify the remarks that refer to more than one? What management or business purpose is served by those remarks other than documentation that a call was made?
I'm racking my brain to see if I can think of a way to re-think the function of reporting these service calls that would prevent the problem from happening in the first place. For now, even though there are 11,000 entries, it seems to me that eye-balling and fixing would be warranted.
Nov 12 2019 07:43 AM
Solution@npshibzk Just re=reading what you wrote:
I highlighted every cell that contains "METER" or "MTR" thus far to help narrow it perhaps.
Did you do that highlighting using conditional formatting? Or was that a manual thing just for this posting? If you did it yourself using conditional formatting, maybe that's your quick and dirty solution.
But still would require a lot of work. It is a mess!
Yep. But a series of manual fixes, accelerated by just searching for METER, MTR, or even #.... and you could do that using a "helper column" next to the "Remarks" column.
In fact, after writing that, I went and wrote the formula for you. Here it is. I added multiple helper columns, with a "search term" at the top.... you could extend this. As you see, it will pick up and highlight the row that contains METER, meter, MTR, mtr, #. and any combination of those..... So all you need to do is put some temporary helper columns next to your "Remarks" and copy these formulas down all 11,000 rows. It'll still be work, but that should accelerate it.
Nov 12 2019 08:27 AM
Thank you so much! @mathetes
Nov 12 2019 09:07 AM
Nov 12 2019 07:43 AM
Solution@npshibzk Just re=reading what you wrote:
I highlighted every cell that contains "METER" or "MTR" thus far to help narrow it perhaps.
Did you do that highlighting using conditional formatting? Or was that a manual thing just for this posting? If you did it yourself using conditional formatting, maybe that's your quick and dirty solution.
But still would require a lot of work. It is a mess!
Yep. But a series of manual fixes, accelerated by just searching for METER, MTR, or even #.... and you could do that using a "helper column" next to the "Remarks" column.
In fact, after writing that, I went and wrote the formula for you. Here it is. I added multiple helper columns, with a "search term" at the top.... you could extend this. As you see, it will pick up and highlight the row that contains METER, meter, MTR, mtr, #. and any combination of those..... So all you need to do is put some temporary helper columns next to your "Remarks" and copy these formulas down all 11,000 rows. It'll still be work, but that should accelerate it.