PowerShell Script - List Certain ADGroups as Column Headings & their Users as Row Headings showing who is in which groups

How do I do this in PowerShell please?

I can only find a way to list Users in groups or list groups for a user, not list both in this way.

List AdUsers (in certain groups) as row headings and a subset of AdGroups as Column headings (eg: Group1, Group3 & Group10 only) with the column values having 1's or Y's to show which of those groups the users are in.

eg:

Group1 members are:
UserA
UserC

Group3 members are:
UserA
UserB

Group10 members are:
UserB
UserC

What I'm expecting:
User       Group1    Group3    Group10
UserA      1         1
UserB                1         1
UserC      1                   1

I have this so far, but want to add the matrix shown above as a summary worksheet in the Excel file.

# The ActiveDirectory (AD) App generally only lists Staff Ids which is painful when trying to find out each staff members name to determine who needs to be removed from what groups.
# This script will:
# * Export certain properties of AD Group Members (eg: StaffIds, Names, Depts, etc) for each SSRS AD Group that <Dept> uses for <Dept> RAP Report Security.
# * Exports the data to an Excel Spreadsheet with 1 worksheet per AD Group, listing it's members.
#
# PRE-REQUISITES: 
# * Export-Excel module must be installed
# * ActiveDirectory module must be installed
# ** Remote Server Administration Tools (RSAT) must be installed
# --------------------------------------------------------------------------

# Shows what Properties are available that can be added to lines 30-35 below
#Get-ADUser 12345678 -Properties *

Clear-Host

# Set the filename for the output Excel file
$dateTimeStr = (get-date).tostring('yyyyMMdd_HHmmss')
[String]$ExcelPath  = "C:\Temp\DeptSsrsAdGroups_$dateTimeStr.xlsx"

# Set the list of AD Groups to export results for
[String[]]$AdGroups = ("Group1", "Group3", "Group10")

# Loop through each AD Group in the list
# Export certain properties/fields for each user/member in the AD Group to a worksheet with the same name as the AD Group
# Each excel worksheet will have a table with the AD Group name, auto sized and the top row frozen for formatting/layout purposes.
ForEach($AdGroup in $AdGroups)
{
     Get-AdGroupMember -Identity $AdGroup -Recursive | `
     Get-ADUser -Properties SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,Title,Department,Office,Company,Manager | `
     #Select SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,Title,Department,Office,Company,Manager | `
     Select SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,@{n="ManagerName";e={(Get-AdUser $_.manager -Properties DisplayName).DisplayName}},Title,Department,Office,Company | `
     Sort-Object Surname,GivenName | `
     Export-Excel -Path $ExcelPath -AutoSize -WorksheetName $AdGroup -TableName $AdGroup -FreezeTopRow
} 

# --------------------------------------------------------------------------

You are trying to build a Pivot Table. You need to group by users so you can add one row of users to a table. Then enumerate through the properties. Use code like below

<!-- begin snippet: js hide: false console: true babel: false -->

$table = [System.Collections.ArrayList]::new()
ForEach($user in $users)
{
   $newRow = New-Object -TypeName psobject
   ForEach($property in $properties)
   {
      $newRow | Add-Member -NotePropertyName  propertyName -NotePropertyValue value
   }
   $table.Add($newRow) | Out-Null
}

<!-- end snippet -->

Here's the solution I'm using.
I added a 'Summary' worksheet which has 2 columns: User & Group.
Then added a 'PivotData' worksheet which pivots the 'Summary' worksheet data.

# The ActiveDirectory (AD) App generally only lists Staff Ids which is painful when trying to find out each staff members name to determine who needs to be removed from what groups.
# This script will:
# * Export certain properties of AD Group Members (eg: StaffIds, Names, Depts, etc) for each SSRS AD Group that <Dept> uses for <Dept> RAP Report Security.
# * Exports the data to an Excel Spreadsheet
# ** 1 worksheet per AD Group, listing it's members
# ** Summary worksheet
# ** PivotData worksheet (based on Summary worksheet)
#
# PRE-REQUISITES: 
# * Export-Excel module must be installed
# * ActiveDirectory module must be installed
# ** Remote Server Administration Tools (RSAT) must be installed
# --------------------------------------------------------------------------

# 1st shows default properties (-Properties not reqd) that can be used in the Select below.
# 2nd shows extended properties, must use -Properties then Select.
#Get-ADUser 12345678 
#Get-ADUser 12345678 -Properties *

Clear-Host

# Set the filename for the output Excel file
$dateTimeStr = (get-date).tostring('yyyyMMdd_HHmmss')
[String]$ExcelPath  = "C:\Temp\SsrsAdGroups_$dateTimeStr.xlsx"

# Set the list of AD Groups to export results for
[String[]]$AdGroups = ("Group1", "Group3", "Group10")

# Loop through each AD Group in the list
# Export certain properties/fields for each user/member in the AD Group to a worksheet with the same name as the AD Group
# Each excel worksheet will have a table with the AD Group name, auto sized and the top row frozen for formatting/layout purposes.
$UserGroupSummary = @()
ForEach($AdGroup in $AdGroups)
{
    # Prepare data for Summary Excel Worksheet
    $Users = Get-AdGroupMember -Identity $AdGroup -Recursive | `
             Get-ADUser -Properties DisplayName | `
             ForEach { "{0}  ({1})" -f $_.DisplayName, $_.SamAccountName } 
    ForEach( $User in $Users ) {
        $UserGroupSummary += [PSCustomObject]@{
                            User  = $User
                            Group = $AdGroup
                        }
    }

    # AdGroup Excel Worksheets
    Get-AdGroupMember -Identity $AdGroup -Recursive | `
    Get-ADUser -Properties DisplayName,EmailAddress,Enabled,Title,Department,Office,Company,Manager | `
    Select SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,@{n="ManagerName";e={(Get-AdUser $_.manager -Properties DisplayName).DisplayName}},Title,Department,Office,Company | `
    Sort-Object Surname,GivenName | `
    Export-Excel -Path $ExcelPath -AutoSize -WorksheetName $AdGroup -TableName $AdGroup -FreezeTopRow
} 
# Summary Excel Worksheet - Users & their matching Groups - data source for an Excel pivot table
$UserGroupSummary | Sort-Object User,Group | Export-Excel -Path $ExcelPath -AutoSize -WorksheetName Summary -TableName Summary -FreezeTopRow

# Add a Pivot Table to the Excel file (if it exists) based on the Summary worksheet
If (Test-Path $ExcelPath) 
{
	Try {

		# Open the Excel file
		$excel = Open-ExcelPackage -Path $ExcelPath

        # Create new worksheet at the start
        Add-Worksheet -ExcelPackage $excel -Activate -MoveToStart -WorksheetName PivotData | Out-Null

		# Add a pivot table to the new Excel worksheet
		Add-PivotTable -ExcelPackage $excel -SourceWorkSheet "Summary" -PivotTableName "PivotData" -PivotTableStyle Medium9 -PivotRows User -PivotColumns Group -PivotData Group #-Activate

		# Save & close the Excel file
		Close-ExcelPackage $excel
	}
	Catch {
		# An error occurred
		$message = $_
		write-output "ERROR - $message"
		write-warning "ERROR updating existing Excel file $ExcelPath. ABORTING. $message"
	}

    # Open the excel file
    $excelObj = New-Object -ComObject Excel.Application
    $excelObj.DisplayAlerts = $false;

    # Change pivot table to tabular format - only seems to work if make Visible true for some reason
    $excelObj.Visible = $true
    $excelWb = $excelObj.Workbooks.Open($ExcelPath)
    $excelWsh = $excelWb.Sheets("PivotData")
    $pivot = $excelWsh.PivotTables(1)
    $pivot.RowAxisLayout(1)   # Tabular layout
    $pivot.EnableFieldList = $false
    $excelObj.Visible = $false

    # Freeze panes at B3 in PivotData Worksheet
    [void]$excelObj.Cells.Item(3,2).Select()
    $excelObj.ActiveWindow.FreezePanes = $True

    # Save and close
    $excelObj.ActiveWorkbook.SaveAs($ExcelPath, 51)   # xlsx
    $excelObj.Workbooks.Close()
    $excelObj.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj) | Out-Null

}

write-output "Finished exporting data to $ExcelPath"

# --------------------------------------------------------------------------