COUNTIF() with spaces 2

[grnzbra]

I am using the following


=COUNTIF(EquipmentID,F1)
=COUNTIF(EquipmentID,F2)

in the A column. EquipmentID is the F column. There are two entries in the F column that are exactly the same except one of them ends with two spaces. Both rows in the A column show a result of 1.

I have tried

=COUNTIF(EquipmentID,TRIM(F1))
=COUNTIF(EquipmentID,TRIM(F2))

but still get a count of 1. How can I make the COUNTIF() function realize that these two entries are the same?

 

[1726]

There is also an RTRIM function which removes trailing spaces

Cheers

 

[Andrzejek]

>these two entries are the same
Well, they are NOT the same.

Before you run your formulas, try using the CLEAN function or the TRIM function.
You may have some nonprintable characters, not just spaces...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

There are two entries in the F column that are exactly the same except one of them ends with two spaces.

You mean F1 and F2? Or named range EquipmentID is somewhere in col. F and you refer to it?

In all your formulas you count single string occurences in EquipmentID range. To count multiple entries you can:
- use wildcards ("?" or "*"),
- sum COUNTs with different criteria each,
- create helper column (with TRIM for instance), simple if you use structured table,
- create power query for complicated transformations.

combo

 

[grnzbra]

EquipmentID is the F column.
The Z column for a new line (i) is PROGRAMMED to be set to TRIM(Fi)
The A column for a new line (i) is PROGRAMMED to be set to the COUNTIF() function to find count of occurrences of Fi in EquipmentID
I PROGRAMMED the VB to look when an entry is made in the F column to check the A column to see if the count is greater than 1 to instantly clear the entry and return focus to that cell and display a message saying that it is a duplicate.
I am trying to avoid the use of a helper column


Ah, but they are the same, just not to Excel.
FIRE DOOR
FIRE DOOR
appear the same to a reader but not to Excel (the second line a space at the end).
This is being used for an inventory. In this case, we have two fire doors. Column F is a name given to the item by the inventory taker that makes sense to the inventory taker and allows him to differentiate between the two doors. They could be FIRE DOOR 1 and FIRE DOOR 2 or FD1 and FD2.

 

[mintjulep]

Maybe if you shared your PROGRAMMED.......

 

[grnzbra]

The programming doesn't matter; it only checks to see if the COUNTIF() function is greater than 1. I was just trying to determine if there was a way of setting up the COUNTIF() function so that the extraneous spaces in the entry would be removed all within the COUNTIF() function. COUNTIF(EquipmentID,TRIM(F1)) seemed like it should work, but it doesn't. I was trying to avoid helper columns because they can be problematic when someone with a little knowledge of Excel finds them and figures out how to muck around with the programming that prevents them from getting to them.

 

[combo]

If I understand well, you put FIRE DOOR in F1, search EquipmentID for this entry, but you also need to find the text with leading/trailing spaces. In this scenario TRIM(F1) will not help, this function should be applied to EquipmentID entries. Wildcards are not useful too, as only spaces are accepted in the search result.

A simplified example workbook uploaded would be helpful. Also info about lowest excel version, this problem seems to be simple to solve in power query, manual refreshing of the query is the only additional task for the user.

combo

 

[Andrzejek]

Or, if the leading/trailing spaces in column F (6[sup]th[/sup] column) are causing the problems, what you can do is just run this little code to eliminate these spaces:

Sub CleanColF()
Dim r As Integer

r = 1
Do While Cells(r, 6).Value <> ""
    Cells(r, 6).Value = Trim(Cells(r, 6).Value)
    r = r + 1
Loop

End Sub

Assuming there are no empty cells from the top to the bottom of the data in Column F

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[grnzbra]

Thanks.
That looks promising. I will try that. The only question is how long it will take to run when there are a few thousand records. The only reason we went with the COUNTIF() in the A column was because it reacted pretty much instantly no matter how many records were in the table. Originally we did a loop and when we had more than a few hundred records the delay became noticeable. This doesn't look like it would have that problem.

 

[Andrzejek]

That should take just a moment, and it is a one time deal - at the very beginning just to get rid of spaces.

If you have a LOT of records, you may run out of the limit of an Integer.
If that happens, change it to Long:
[tt]
Dim r As [blue]Long[/blue][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson