counting multiple instances of a number in a range

Question

I have a range of numbers and I need to identify if the first number of each cell is repeated anywhere in the corresponding row.

For example, in row 2 below, column 2 and column 3 both start with a 3. I know that if I do =LEFT(TRIM(cell)) to get just the first number but how do I find the rows that have repeated numbers in the row so row 1 isn't marked but row 2 is?

100|600|203|700|   |    
202|302|301|400|600|

The marking (like a "Y" for example) would appear in column to the right of last column — amwakatsuki, Jan 09 '17 at 21:11
Can you share screenshots of you attemps for the different answers? It would help us help you. — Pomul, Jan 09 '17 at 21:46

score 2 · Accepted Answer · answered Jan 09 '17 at 19:48

2

Use a helper column with this as an array formula:

=SUMPRODUCT(--(COLUMN($A1:$E1)<>MATCH(INT($A1:$E1/100),INT($A1:$E1/100),0)))>0

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

answered Jan 09 '17 at 19:48

Scott Craner

148,073
10
49
81

This formula returned an error for me. It could be that some of the columns have blank cells in the 4th or 5th spot. – amwakatsuki Jan 09 '17 at 21:28
Blanks should not matter, If you are getting an error one of two things. 1 you did not enter it with Ctrl-Shift-Enter as an array formula. 2. your local settings use `;` instead of `,` to delineate the criteria in your formulas. – Scott Craner Jan 09 '17 at 21:31

score 1 · Answer 2 · answered Jan 09 '17 at 19:58

Consider the following UDF():

Public Function AnyDups(rng As Range) As Boolean
    Dim valu() As String
    Dim i As Long, L As Long
    L = rng.Count
    ReDim valu(1 To L)
    AnyDups = False
    If L = 1 Then Exit Function
    i = 1

    For Each r In rng
        valu(i) = Left(r.Value, 1)
        i = i + 1
    Next r

    For i = 1 To L - 1
        v = valu(i)
        For j = i + 1 To L
            If v = valu(j) Then AnyDups = True
        Next j
    Next i
End Function

For example:

The code just loops through the possible combinations of left-most characters in the cells.

It should work with either text or numeric data.

I'm not too familiar with VBA but I will look into it so I can test it. This may be helpful since the procedure will be reused many times and running a macro will be a one click solution each time (if I understand macros correctly) — amwakatsuki, Jan 09 '17 at 21:31

score 0 · Answer 3 · answered Jan 09 '17 at 19:44

0

One way to do it would be to use this formula as a basis:

=IF(ISERROR(FIND(LEFT(TRIM(A1),1),B1)),FALSE,"Row "& ROW(A1))

Depending on how you want to check your row, you can adapt it. You could either have one formula to check one cell (Lock the A1 reference and drag right) - which would allow you to know where the match is but take more space on the sheet. Or, if you don't have too many cells to check in each row, you could concatenate all cells in the same formula:

=IF(ISERROR(FIND(LEFT(TRIM(A1),1),B1&C1&D1&E1)),FALSE,"Row "& ROW(A1))

I'm sure Gary's Student will have a more elegant answer though!

answered Jan 09 '17 at 19:44

Pomul

392
3
11

This solution almost worked but returned some false positives and some false negatives. – amwakatsuki Jan 09 '17 at 21:31
It didn't do a very good job then! Make sure the references are correct and that all the columns containing data are included in the second formula, as it currently stop at column E. If data in column F would make the formula True, it won't ba captured. I got False positive when I dragged to the right the first formula without locking the reference (e.g. A1 instead of $A$1) and False negative with the second formula because the data making the formula True was in column F (not currently included in the formula). – Pomul Jan 09 '17 at 21:45

counting multiple instances of a number in a range

3 Answers3