Check if data is correct in 2nd column with matching a value in the 1first column

[looney]

Hi!

I have 2 columns of data.

first column is filled with names and the second is filled with a team number like: Team 1, Team 2, Team 3, etc.

These columns are filled in by users through a form. The problem is that users accidentally fill in the wrong team number. So some of the data will come out like in the example below.

Names	Teams
Name 1	Team 1
Name 1	Team 2
Name 1	Team 2
Name 2	Team 3

So 'Name 1' made a mistake with filling in 'Team 1' and it suppose to be 'Team 2'.

It would be enough for me if I can just get an alert when there is a name connected with more then one team. I could change the values manually. Solutions in VBA or formula's in other column are both welcome.

Thanks in advance.

[dilipandey]

Hi Looney,

welcome to the forum.
You can use below formula to check the data:-

Please Login or Register  to view this content.

name & team.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[TMS]

I'm not sure I understand the logic. Is a Name a member of only one (1) Team? If so, what's the point of asking them to fill in what is, to all intents and purposes, some static information linked to the Name.

Why not have a table set up with all the possible names and their associated team and use VLOOKUP to fill the Team box. problem solved.

Regards, TMS

[benishiryo]

hi looney, welcome to the forum. assuming your data is in A2:B5, you can try this formula in C2:
=SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5<>B2))>0

drag down the formula & you have a bunch of TRUEs & FALSEs. you can then filter the TRUE & check them. you can also do conditional formatting to highlight those cells.
select the range you want to apply to (say from A2:B5)
in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
in Excel 2003, go to Format -> Conditional Formatting -> Formula is:
=SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5<>B2))

[looney]

Thanks.

This is helping... Although after tweaking the formula to my situation. It also tells me 'need checking' when there are more rows with the same name. The entries are daily so there will probably 4 to 5 entries with the same name. Sometimes there is a mistake made with the selected teamnumber. So I only need to check if there are names who have selected different team number in the same week. for example. On Monday 'Name 1' selects 'Team 1' and on Tuesday he selects 'Team 2' and so on. Name 1 should select in one week every time the same team.

To reply on TMShucks...
The teams can change every week. A team member can belong to a different team the following week. And there is not a list or possibility to keep track of who is in which team every week.
If they would... It would make a much easier job for me... but thanks!

I think the solution of Benishiryo would help me a bit more on the way. I applied this on the column for teams and highlighting those cells is really a charm! It shows directly what needs to be changed!
I am going to see If I can apply this in some sort of macro.

Thanks!