Sort Active Directory users by group

Hi,

I made a Powershell script to extract all groups and users. The output is a .csv file that looks like this.

HTML Code: 

I separate them them like this.
Data > text to column > Delimited by Comma

Group	"member"	enabled
Admin	Pietsje Overmars	True
Admin	Reinder Wortel	True
Admin	Karima Hazewinkel	False
HR	Wouterina Keulemans	True
HR	Phaedra Lugt	True
IT	Marlin Moens	False
IT	Manuel Dongelmans	True

I want the output to look like this with the users that are disabled (False) in red or strike-through:

Admin	HR	IT
Pietsje Overmars	Wouterina Keulemans	Marlin Moens
Reinder Wortel	Phaedra Lugt	Manuel Dongelmans
Karima Hazewinkel

I only get something that is remotely similar to what I want when I put both Group and Member under ROWS in a PIVOT-TABLE. When I ad enabled to filter I can select True or False which is nice but not really necessary. They should be next to each other (blank) and Grand Total should be gone.

You could do something like:

Use Power Query to import your CSV data, and index by Group:

let
    Source = Csv.Document(File.Contents("C:\temp\powershell.csv"),[Delimiter=",", QuoteStyle=QuoteStyle.Csv]),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column2", each Text.BetweenDelimiters(_, """", """"), type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Text Between Delimiters", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"member", type text}, {" enabled", type logical}}),
    #"Index Partitions" = Table.Group(#"Changed Type", {"Group"}, {{"Partition", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Index Partitions", "Partition", {"member", " enabled", "Index"}, {"member", " enabled", "Index"})
in
    #"Expanded Partition"

Load this query to the Data Model. Add a couple of measures:

Member Name:=IF ( 
    HASONEVALUE ( powershell[member] ),
    VALUES ( powershell[member] ),
    BLANK()
)

Member Enabled:=IF ( 
    HASONEVALUE ( powershell[enabled] ),
    VALUES ( powershell[enabled] ),
    BLANK()
)

Now you can use CUBEVALUE formulae to display the names:

Formula:

=CUBEVALUE("ThisWorkbookDataModel",

CUBEMEMBER("ThisWorkbookDataModel","[powershell].[Index].&["&ROW()-1&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Member Name]"),

A$1)

Capture.png


Finally, use a CUBEVALUE formula in conditional formatting, and format to suit:

Formula:

=NOT(CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Member Enabled]"),CUBEMEMBER("ThisWorkbookDataModel","[powershell].[Index].&["&ROW()-1&"]"),A$1))

See https://excel.solutions/xlf_1269773/ for a worked example file.

Hello denisuu and Welcome to Excel Forum.
Here is a formula based option that assumes you already have the data in Excel as shown below the statement "I separate them them like this":
1. Populate row 1 with the headers Admin, HR and IT
2. Populate the rows under the headers using: =IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW(A$2:A$8)-ROW(A$1))/($A$2:$A$8=E$1),ROWS(A$1:A1))),"")
3. Conditionally format the range using: =NOT(INDEX($C$2:$C$8,MATCH(E2,$B$2:$B$8,0)))
For future reference you will usually get faster responses if you upload a file by clicking on the Go Advanced button below the Quick Reply window then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

This will automate the displaying of headers:

Formula:

=IFERROR(IF(MATCH(0,INDEX(COUNTIF($D1:D1,$A2:$A8),,),)>COUNTA($A2:$A8),"",INDEX($A2:$A8,MATCH(0,INDEX(COUNTIF($D1:D1,$A2:$A8),,),))),"")

Select cell E1,
Paste the formula into the formula bar,
Drag the fill handle over to cell H1.
Let us know if you have any questions.

I don't understand why that should be so. Here is the file with the formula applied.
Let us know if you have any questions.