Listng team-wise names from a list

[jsridhar]

I have a database. Sample file is attached. It has different persons from different teams. I need to get them listed separately based on teams. Kindly guide me and oblige.

[bebo021999]

First cell of output table:

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($F$2:$F$17)/($F$2:$F$17=I$3),ROWS($1:1))),"") 

drag down and accross

[Pete_UK]

Use this formula in cell G2:

=F2&"_"&COUNTIF(F$2:F2,F2)

Copy down to the bottom of your table. Then you can use this formula in cell I4:

=IFERROR(INDEX($B:$B,MATCH(I$3&"_"&ROWS($1:1),$G:$G,0)),"")

Copy across and down as required.

Hope this helps.

Pete

[Glenn Kennedy]

The whole table in one go:

=LET(A,B2:B17,B,F2:F17,C,TRANSPOSE(SORT(UNIQUE(B))),D,DROP(REDUCE("",C,LAMBDA(x,y,HSTACK(x,FILTER(A,B=y)))),,1),VSTACK(C,D))

no copy/paste. Just delete any expected results.

[HansDouwe]

A little bit other solution:

Please Login or Register  to view this content.

[jsridhar]

No please. It is showing an error. Screenshot attached.

[Glenn Kennedy]

A picture is no use. Also, who areyou talking to?

[jsridhar]

Thank you. It worked even though I could not understand the formula!! Thank you very much.

[Glenn Kennedy]

Once again, we DO NOT KNOW who you are talking to!!!!

[jsridhar]

Sorry. This did not work.

[jsridhar]

I was responding to bebo021999 as the solution given did not work and showed a Circular Reference error. Hence I shared the screenshot.
Your solution worked even though I could not understand the formula. Thank you.

[Glenn Kennedy]

Please STOP for a moment. Who are you talking to? We have names. Use them.

[Glenn Kennedy]

Finally. You are telling us who yiu are replying to . Please continue to do so. It is very annoying to see comments that are directed at no-one in particular.

You're welcome. Thanks for letting us know that you got an answer.

---

If that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[jsridhar]

No please. It did not work. I attach the worksheet with formula shown by you. Kindly take a look and guide me. Thank you.

[jsridhar]

Sorry. I am not very tech savvy. I clicked on the Reply button. Apparently, I clicked the wrong one.
I am not yet closing it as I am looking for solutions that I am able to comprehend more easily.
Thank you.

[bebo021999]

The formula in post 2, assumed that line 3 already has the title IPL-A...
It seems that you want the formula for line 2 (the title) and line 3 downwards...
I3, then copy accross:

Please Login or Register  to view this content.

I4, drag down and accross

Please Login or Register  to view this content.

[jsridhar]

@bebo021999 Thank you. It works. Just one suggestion. In the Title Row, it lists IPL-D, IPL-C, IPL-B and IPL-A. Instead, if it lists from IPL-A to IPL-D, it would look better. Otherwise, it is fine. Thank you.

[jsridhar]

@HansDouwe
Just one more query.
In the sample I gave four teams - IPL-A to IPL-D.
If I have more teams, let's say 26 teams, IPL-A to IPL-Z, will the formula change?

[bebo021999]

@bebo021999 Thank you. It works. Just one suggestion. In the Title Row, it lists IPL-D, IPL-C, IPL-B and IPL-A. Instead, if it lists from IPL-A to IPL-D, it would look better. Otherwise, it is fine. Thank you.

If you want to sort the header rows, there are several ways to do it depending on your data:
1- If your data is relatively stable, and the team list is fixed:
Method 1: Copy I3:L3, paste it transposed into another column, for example, N3:N6. Sort this range, and then copy-paste transpose it back into I3:L3.
Method 2: Sort column F. Then copy/paste the values in the header range I3:L3. Revert to sorting by column A.

2- If your data frequently changes, and the team list is regularly updated:
Method 1: Use an auxiliary column, like column G, to mark column F according to A-Z conditions. (like sample attached)
Method 2: In my opinion, the best approach is to use VBA. (will come back if you refer to VBA)

[jsridhar]

@bebo021999
Thank you.

[HansDouwe]

If there more teams/data only adjust the ranges.

If not all teams have the same size also add the function IFNA to the formula to prevent #N/A-errrors:

Please Login or Register  to view this content.

[alansidman]

One more solution with Power query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[jsridhar]

@HansDouwe
This is brilliant.
I will add one more variant now! In the attached file, I have a summary table of members.
There are totally 29 members. However, when I choose COUNTA, it shows 32 as it adds wherever the formula is present.
How do I get 29 by ignoring cells that do NOT have names?

[jsridhar]

@alansidman
Thank you very much for your valuable inputs. I appreciate it.

[HansDouwe]

Please try in I14:

Please Login or Register  to view this content.

[jsridhar]

@HansDouwe
Thank you. It works.
If you are not tired of me, I will add more variations. If you would rather stop here, I will understand.