Writing Excel formula to check all rows if they have same IDs but different file names

Question

I have a long list of data within an excel sheet, I am going to be changing the file IDs to names instead. The problem is I need to first check whether there are different IDs for the same file name, as this would create a problem.

So for the columns file_id and the column File_name I need help writing a formula that would compare wherther there are two or more IDs for the same file name.

Is this possible?

Thanks

Answer 1

Assuming Column A is ID and Column B is Name The following will check if there is more than one ID for each name. Paste in Cell C1 and drag down.

=IF(COUNTIFS(B:B,"<>"&B1,A:A,A1)>0,"MORE THAN ONE ID FOR THIS NAME","OK")

Note that the 'COUNTIFS()' Function is only for excel 2007, 2010.

If you have an older version of Excel you need to use an array formula which will look like this:

=IF(SUM(IF(A:A=A1,IF(B:B<>B1,1,0),0))>0,"MORE THAN ONE ID FOR THIS NAME","OK")

To make it work You'll have to push Ctrl+Shift+Enter when entering the formula.

Answer 2

Here's is my attempt. What this code would do is mark those file name whose id doesn't match with the previous IDs. Look at the picture below :

I've attempted it using VBA

Press ALT + F11 to enter VBA, on the left hand column you'll see "Project -VBA Project", under this double click on Sheet1, or whatever is the name of your sheet on which you're working.

Paste the following code there:

Sub mark()

    Dim rng As Range
    Dim row As Integer
    Dim id As Integer
    Dim name As String

    Set rng = Sheets("Sheet1").Range("$A$2")
    row = 0

    Do Until rng.Offset(row, 0) = ""

        id = rng.Offset(row, 0)
        name = rng.Offset(row, 1)

        Do While rng.Offset(row, 1) = name
            If rng.Offset(row, 0) <> id Then
                rng.Offset(row, 0).Interior.Color = 255
                rng.Offset(row, 1).Interior.Color = 255
            End If
            row = row + 1
        Loop

    Loop

End Sub

Keep the following in mind

1. In my code the name of the sheet is "Sheet1", if the name of your sheet is something else then edit the below line in the code as follows

   Set rng = Sheets("Your sheet name").Range("$A$2")
   

2. In my code, the starting Range(Cell) has been specified as A2, if the starting cell is something else for example D2, then edit the line in the code as follows

   Set rng = Sheets("Your sheet name").Range("$D$2")
   

If you want to do some thing else with these cells instead of marking them, feel free to comment.