Calling any Excel guru

[89th]

I have a spreadsheet.

It has about 500 rows.

The first column are Usernames. All cells filled in.

Next to each name (in the next 10 or so columns) list Group(s) that each person is in. Some people are just in one group so they only have Column B filled in. Some people are in 10 groups so they have Columns B-K filled in.

My goal is to flip this, of sorts, and have a list of Groups, and to the right of each, the Usernames in each Group.

Thoughts? See sample of the data structure below:

So in this case it would be something like:

Orange: Horace, Aqua, Mik
Blue: Aqua, Jon's Sister

[Horace]

They have competitions for people to do these sorts of things in Excel as quickly as possible. The same guy has been winning for years. If I had to do something like that inside excel, I'd figure out how to use VBScript. If I had to do it in general, I'd output a CSV and write a quick tool to create a new CSV as described. So I can't help.

[89th]

Yeah but can you just do it for meeeeeeeeeeeee

[89th]

Kidding, no worries. Wasn't sure if there was a pivot table magic wand or something I'm not thinking about right away.

[jon-nyc]

Copy then paste with transpose checked.

[Mik]

Just copy the sheet to a new one and sort by group then name.

[Klaus]

https://support.microsoft.com/en-us/office/groupby-function-5e08ae8c-6800-4b72-b623-c41773611505#:~:text=The GROUPBY function allows you to group%2C aggregate%2C sort%2C,on the fields you specify.&text=A column-oriented array or,range may contain multiple columns.

[Klaus]

@Horace said in Calling any Excel guru:

If I had to do it in general, I'd output a CSV and write a quick tool to create a new CSV as described. So I can't help

That's what I would also do.

I love love love csvkit.

Apart from other very useful tools, it gives me the full power of SQL.

[LuFins Dad]

What does this have to do with a Korean car?

[taiwan_girl]

A pivot table would not work?

[Axtremus]

@Klaus said in Calling any Excel guru:

@Horace said in Calling any Excel guru:

If I had to do it in general, I'd output a CSV and write a quick tool to create a new CSV as described. So I can't help

That's what I would also do.

Dang, that’s also what I would do.
But that’s just a function of whether one is more fluent with Excel or with another scripting/programming language. :man-shrugging: