Convert rows to comma separated values by Group

Question

Hello,

I have a listing of Active Directory (AD) Groups and the Members that make up each AD Group.  I have assigned each Member a 'User Type'.  Each Member may only be assigned one 'User Type'.  Each AD Group, however, may have more than one 'User Type' represented within it.  Ultimately, what I need, is, for each AD Group, to write out all the 'User Types' of the Members of that Group and write them into a single cell so that each AD Group, instead of having multiple rows for each Member, has only one row capturing all the 'User Types' in a single cell next to the AD Group Name.  Visually, this is what I'm after:

If there is a formulaic answer, that would be preferred, but some VB code may be necessary.

Thank you!

Answer 1

Hi

try this , you need to do some workanound, Assuming you data set is start from B1:C8

1- remove duplicate by column "UserType"

2- Enter formula in A2 :=IF(B2<>B3,"","X") & copy down as for as need 

3-Enter fomuls in D2:=IF(B1=B2,C2&","&D1,C2) &  copy down

4-we need to convert all formula into values so select all data set from A1 to D8 & copy & pastespecial as value

5- now apply filter & filter value x & delete all x rows values now select blank in filter this would be your disired result , check screen shot

Reagards