Get all results of rows with matching criteria from a range

Question

As mentioned in another question, I'm on a few Discord servers that allow players to auction in-game content for trade. Use sheets to keep track of my own auctions, I copy the tag (name#xxxx) and ID (long number) into a list so I can filter out anyone who fails to follow through on a winning auction.

What I'm looking to do now is two fold.

One is to have an automated list of duplicate ID's that have different tags (someone changed their name). The list would show the ID number in one cell, then in the next cell, show all of the tags they have used (using textJoin() and sort() to make it easier to read).

The second is to grab a list of tags used based on a separate list of ID's (known list of users who have failed to follow through).

I've tried and failed with vlookup(), filter(), query(), and regular lookup(). When I tried filter() for using a list, it only seems to look at the first item, but ignores anything after that. If I try to expand the length of the list (instead of F2 I make it F2:F) it turns red and won't work.

Using conditional formatting, I'm able to get duplicates to highlight within the range. However, I'm trying to take it a step further and retrieve matches. Sort of like an automated filter system to show known duplicates.

What am I not thinking of here?

Edit: Here is some dummy info to give an idea...

Alred         970192322624802134
Barry         493288047120884104
Clark         648567312202297155
Dick          336368714780211631
Enigma        513446866409315771
Flash         493288047120884104
Green Arrow   382294342593163268
Harley        894386762337961412
Ivy           192571739320652789
Jimmy         662344040550649441
Krypto        944264752128564134
Lois          608531705193876711
Mera          461451144314768165
Nyssa         533348352505340666
Oliver        382294342593163268
Perry         877529730964781752
Robin         336368714780211631
Superman      648567312202297155
Thomas        969367121152160055

Obviously the names would have a # sign followed by a 4 digit number, but it's an irrelevant factor for this matter.

Looking at the list, you can see that some of the ID's (column of numbers) are the same, but with different names.

So the goal is to have a separate column that would go through, find ID's that are duplicated, and then list all names with that matching ID.

Example:

648567312202297155   Clark, Superman
336368714780211631   Dick, Robin
382294342593163268   Green Arrow, Oliver
etc

For the second one, a list of ID's where matching ID's would show all matching names. Sort of like if the above only listed ID's that have more than one entry, and then that list were to be used to find names, except the list of ID's could be generated by other means. To break it down into two simplified tasks... One finds duplicate ID's and simply lists those ID's (one time each), then another looks up those ID's and lists all matching entries (ie, the names).

Edit: This link is an example of what I'm trying to do, but it's not working for me. Google Sheet Query - Group / concatenate multiple rows

I have A(A2:A)=Names/tag, B(B2:B)=ID, F(F2:F)=ID's to filter. When I try =join(",",filter(A:A,B:B=F2)) it returns the first entry but then stops.

Also, when I try to simulate the other page (values and formulas), it only returns the results for Bill (Red,Blue,Yellow), but not for Sarah.

By simulate, I mean that I added an empty sheet and filled in the values and formulas as shown in the image.

Answer 1

If I understand your problem correctly, you are trying to match a username against a range of user names in a column, in order to see if it matches.

One way to do it would be to use match in a query, for example:

=QUERY("Charleston!A2:H"," where Col6 matches '" & join("|", A2:A246) & "' Order By Col8")

If that does not help, please provide us some use cases, and your code if you can.

Answer 2

The solution combines the functions TEXTJOIN with ARRAYFORMULA. Given your sample data, you can create a list of the unique user IDs with the UNIQUE formula. So cell D1 has =UNIQUE($B$1:$B$19). This fills in the data in column D.

The formula in column E then matches each of the unique user IDs in column B with any of the names in column A using ARRAYFORMULA to return all of the names, then concatenates the list of names using TEXTJOIN. The formula in column E is

=TEXTJOIN(",",true,arrayformula(if($D7=$B$1:$B$19,$A$1:$A$19,"")))