| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine Multiple Entries with differing amounts of entries
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combine Multiple Entries with differing amounts of entries
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(IN DIRECT("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(N ame=$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 "Katie" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combine Multiple Entries with differing amounts of entries
WOW!!!
-- RyGuy "Teethless mama" wrote: 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(IN DIRECT("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(N ame=$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 "Katie" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| look for multiple entries | Excel Worksheet Functions | |||
| Differing Amounts of Data | Excel Discussion (Misc queries) | |||
| Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions | |||
| Can I compare 2 lists to combine duplicate entries in new list? | Excel Worksheet Functions | |||
| How do I change multi-line entries to single line entries in Exce. | Excel Worksheet Functions | |||
Linear Mode
