Address list: combine row information based on duplicates in column

I have a list with columns for address, last name, first names, and a photo file name. The list is separated into rows for each individual. What I need is the list combined for residence in the same household/address with additional first names moved to new columns or combined into a single column with "," separating first names. Duplicate row(s) should be deleted. Some households have up to 5 additional first names - not sure that matters. There are 800+ rows so sorting by hand would be tedious. Thanks in advance.

From:
A B C D
Address Last_Name First_Name PhotoID
123 N. First St Smith Aaron 12345548.jpeg
123 N. First St. Smith Beth 12345548.jpeg
123 N. First St. Smith Carol 12345548.jpeg
200 N. Second St. Jones Mark 67808708.jpeg
300 N. Third St. Davis John 5432399.jpeg
300 N. Third St. Davis Jane 5432399.jpeg

To:
A B C D E or E F
Address Last_Name First_Name PhotoID
123 N. First St Smith Aaron 12345548.jpeg Beth, Carol Beth Carol
200 N. Second St. Jones Mark 67808708.jpeg
300 N. Third St. Davis John 5432399.jpeg Jane Jane

I've attached a sample of the file.samplebook1.xlsx

Thanks for a really quick response. That is really close. What I don't need is the last name "," first name in one column. I just need a last name in one column and then the first names in separate columns. I need to be able to pull first names separately into a mail merge after I have a corrected list.

Also, when I run the macro with my full spreadsheet I get a "Subscript out of range" error. Not sure what that is about.

Try the attached

Please Login or Register  to view this content.

Thank you GC Excell. Perfect. Thank you. I didn't have a second sheet, so I was getting the error. Once I added the sheet and ran the macro - perfect.