Combine Multiple Entries with differing amounts of entries

  • Thread starter Thread starter Katie
  • Start date Start date
K

Katie

Good afternoon! Once again I come back to the experts for some assistance. I
can't seem to grasp my head around this to figure it out.

There are multiple entries for multiple people listing different
qualifications, and I would like to combine them to make one entry. Here is
my example:

John Smith 1A
John Smith 2A
John Smith 3B
Jane Doe 1A
Jane Doe 3B
Sally Smith 1A
Sally Smith 2A
Sally Smith 3B
Sally Smith 4B

What I would like to have is:
John Smith 1A, 2A, 3B
Jane Doe 1A, 3B
Sally Smith 1A, 2A, 3B, 4B

Is this possible without the manual work? I have approximately 15,000
entries.

Thank you for any help you are able to provide.
 
C2:
=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1))),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

D2:
=IF(ISERR(SMALL(IF(Name=$C2,ROW(INDIRECT("1:"&ROWS(Qual)))),COLUMNS($D:D))),"",INDEX(Qual,SMALL(IF(Name=$C2,ROW(INDIRECT("1:"&ROWS(Qual)))),COLUMNS($D:D))))

ctrl+shift+enter, not just enter
Copy across and down

The first formula returns unique Names, and the second formula returns
Qualifications
 
WOW!!!

--
RyGuy


Teethless mama said:
C2:
=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1))),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

D2:
=IF(ISERR(SMALL(IF(Name=$C2,ROW(INDIRECT("1:"&ROWS(Qual)))),COLUMNS($D:D))),"",INDEX(Qual,SMALL(IF(Name=$C2,ROW(INDIRECT("1:"&ROWS(Qual)))),COLUMNS($D:D))))

ctrl+shift+enter, not just enter
Copy across and down

The first formula returns unique Names, and the second formula returns
Qualifications
Click to expand...
 
Back
Top