How to extract data in a column that is grouped by a particular keyword

kolokoy

New Member
Joined
Mar 31, 2016
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I don't know exactly how to describe the problem. I search the forum and I cannot find a similar scenario which I believe is because I was not able to phrase my keyword properly. Here is the problem.

I have a lot of data in rows and columns. The rows are representing individual data grouped into teams. The members are listed first, then at the last row of each group the leader is listed. What I want to do is add a last column that will extract the names of the members (column C) and list them in the row of the leader. For instance, rows 2, 3 and 4 are members and row 5 is the leader. This status (being Leader or Member) is shown in column I. What I want to accomplish is list the names of members found in column C to column I. It should be listed in the leader's row.

I attached a screenshot.

Thank you for helping.
 

Attachments

  • Excel-Problem-April.png
    Excel-Problem-April.png
    18.3 KB · Views: 35

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to extract member names for each leader in a worksheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With a helper column
Members-Leader.xlsx
ABCDEFGHIJK
1NumberCodeNameStatusMembers
2EricMemberEric 
3JamesMemberEric, James 
4BernardMemberEric, James, Bernard 
5RobLeaderEric, James, BernardEric, James, Bernard
6JayMemberJay 
7JoyMemberJay, Joy 
8BobLeaderJay, JoyJay, Joy
9MarthaMemberMartha 
10GinaLeaderMarthaMartha
Sheet2
Cell Formulas
RangeFormula
J2J2=IF(I1<>"member",C2,IF(I2="leader",K1,K1&", "&C2))
K2:K10K2=IF(I2="member","",J2)
J3:J10J3=IF(I2<>"member",C3,IF(I3="leader",J2,J2&", "&C3))
 
Upvote 0
Solution
Oh sorry about that. Yes, I posted the same problem on another forum. Here is the link:

Thanks,
Kolokoy
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With a helper column
Members-Leader.xlsx
ABCDEFGHIJK
1NumberCodeNameStatusMembers
2EricMemberEric 
3JamesMemberEric, James 
4BernardMemberEric, James, Bernard 
5RobLeaderEric, James, BernardEric, James, Bernard
6JayMemberJay 
7JoyMemberJay, Joy 
8BobLeaderJay, JoyJay, Joy
9MarthaMemberMartha 
10GinaLeaderMarthaMartha
Sheet2
Cell Formulas
RangeFormula
J2J2=IF(I1<>"member",C2,IF(I2="leader",K1,K1&", "&C2))
K2:K10K2=IF(I2="member","",J2)
J3:J10J3=IF(I2<>"member",C3,IF(I3="leader",J2,J2&", "&C3))
Thank you very much! This is exactly what I need. Thank you!

Best regards,
Kolokoy
 
Upvote 0
With two formulas.

Put this formula in cell 'K2' (helper column).
Code:
=IF(I2="Leader",K1,IF(I2=I1,K1&", "&C2,C2))
Put this formula in cell 'J2'.
Code:
=IF(I2="Leader",K2,"")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top