Home

Dealing with duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-772953%22%20slang%3D%22en-US%22%3EDealing%20with%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772953%22%20slang%3D%22en-US%22%3EHi%20everyone.%3CBR%20%2F%3ECan%20you%20help%20me%20with%20this%20problem%3F%3CBR%20%2F%3EFor%20example%20my%20data%20set%20looks%20like%20this.%20I%20have%20two%20columns.%3CBR%20%2F%3EName%20Group%3CBR%20%2F%3EMaria%201%3CBR%20%2F%3EMaria%202%3CBR%20%2F%3ELuna%202%3CBR%20%2F%3ELuna%202%3CBR%20%2F%3EPaul%201%3CBR%20%2F%3EPaul%201%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20the%20same%20name%20appears%20twice%20and%20is%20a%20member%20both%20groups%20(e.g.%20Maria)%2C%20the%20student%20is%20labelled%20as%20belonging%20to%20Group%201.%20So%20I%20need%20to%20add%20another%20column%20indicating%20the%20final%20group%20membership.%3CBR%20%2F%3E%3CBR%20%2F%3EName%20Group_final%3CBR%20%2F%3EMaria%201%3CBR%20%2F%3EMaria%201%3CBR%20%2F%3ELuna%202%3CBR%20%2F%3ELuna%202%3CBR%20%2F%3EPaul%201%3CBR%20%2F%3EPaul%201%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-772953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772979%22%20slang%3D%22en-US%22%3ERe%3A%20Dealing%20with%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772979%22%20slang%3D%22en-US%22%3ESuppose%20your%20data%20is%20in%20A2%3AA7%20then%20this%20formula%20in%20C2%20will%20list%20the%20group%20that%20is%20entered%20next%20to%20the%20first%20time%20a%20name%20is%20in%20the%20list%3A%3CBR%20%2F%3E%3DVLOOKUP(A2%2C%24A%242%3A%24B%247%2C2%2CFALSE)%3CBR%20%2F%3EFor%20this%20to%20work%2C%20sort%20your%20table%20(using%20a%20custom%20sort)%2C%20first%20on%20column%20A%2C%20then%20on%20column%20B.%20This%20ensures%20the%20groups%20are%20in%20ascending%20order%20for%20each%20name.%3C%2FLINGO-BODY%3E
Deleted
Not applicable
Hi everyone.
Can you help me with this problem?
For example my data set looks like this. I have two columns.
Name Group
Maria 1
Maria 2
Luna 2
Luna 2
Paul 1
Paul 1

If the same name appears twice and is a member both groups (e.g. Maria), the student is labelled as belonging to Group 1. So I need to add another column indicating the final group membership.

Name Group_final
Maria 1
Maria 1
Luna 2
Luna 2
Paul 1
Paul 1

Thanks.
1 Reply
Suppose your data is in A2:A7 then this formula in C2 will list the group that is entered next to the first time a name is in the list:
=VLOOKUP(A2,$A$2:$B$7,2,FALSE)
For this to work, sort your table (using a custom sort), first on column A, then on column B. This ensures the groups are in ascending order for each name.