Convert a name-list with addresses to a list with each adres > names

[MatthijsG]

Hi,
I’ve a straight-forward .csv with +1000 names & addresses. Like
Hans - road 1
Sarah - road 1
Bill - way 3
etc

I need to convert this to a tab where each address-line has a cell with the names. Like
Road 1 - Hans, Sarah
Way 3 - Bill
etc

Tried Pivot-tables. Get the address right, but i don’t get the names. When i use a registration number (each name has an unique number), the Pivot-table does sum, product or something else. But not the names per adres.

Which function or formula should i use? What is the correct term i can google for? Thanks in advance

[joshua4]

Do the hyphens in your example represent a different cell, or is your CSV just a text file with one line per person and a literal hyphen between the name and address?

Is a macro (User Defined Function) a possibility? Calc: How to convert columns into multiple rows does some related logic. If you are interested in that sort of solution, upload an example ODS Calc file with one sheet just like the contents of your CSV (using just a few fake names/addresses, of course).

[JohnSUN]

There are several “dirty hacks” to accomplish this task with a PivotTable. But I suggest using another way.

First of all, prepare a list of unique addresses. It’s not difficult: select the column with addresses and use the Standard filter with the options “Copy results to” and "No duplication"

As a result, you will receive a list of addresses in the G column.

Now add the heading “Names” in cell H1, and in cell H2 enter the formula
=TEXTJOIN(", ";1;IF($B$2:$B$1000=G2;$A$2:$A$1000;""))
Finish entering the formula by pressing the key combination Shift + Ctrl + Enter (this is an array formula)

Now copy this cell to all cells in column H until the end of the list of addresses.