Question: PowerShell

[![](https://i.stack.imgur.com/POQMl.png)](https://i.stack.imgur.com/POQMl.png)

Excel

[![](https://i.stack.imgur.com/3AtxN.png)](https://i.stack.imgur.com/3AtxN.png)

I have added two attachments One is of PowerShell and other one is Excel sheet.
I have coded to get the repeated values present in Excel Sheet. But this code functions only if they find exact values. But my Aim was to get the count of repetitive values present in every cell .
For example in my excel screenshot , If you Word "**Workday**" is present in **A2 and A6.** . I needed the count as 2 , but as per my PowerShell script its showing 1.

How to implement this feature?

Answer: 
Building on Abraham's solution, using `Group-Object` should get you close by using a scriptblock to define the custom property you would like to group by. In this case we can check which names match other names and use one or the other (i chose to go with the shortest term)

```
# START TEST DATA
$data = @'
Name,PIA
Workday,23
Docusign,56
OneTrust,89
Mk Denial,78
How will you join Workday,45
Which Workday,23
OneTrust Maintenance,75
'@ | ConvertFrom-Csv
# END TEST DATA

# Replace $data below with Import-Csv Testing.csv
$data | Select-Object Name | Group-Object -Property {
    $item = $_
    if (($shortest = $data.name | Where-Object { $_ -ne $item.Name -and $item.name -match [regex]::Escape($_) } | Sort-Object {$_.Length} | Select-Object -First 1)) {
        $shortest
    }
    else {
        $item.Name
    }
} | Select-Object Count, Name, @{n = 'Items'; e = { $_.Group.Name -join ', ' } }

```

Output

```
Count Name      Items
----- ----      -----
    1 Docusign  Docusign
    1 Mk Denial Mk Denial
    2 OneTrust  OneTrust, OneTrust Maintenance
    3 Workday   Workday, How will you join Workday, Which Workday

```

Update
------

To add the PIA column as comma-separated values like the Name column we can add another calculated property to `Select-Object`. I added in a `Foreach-Object` loop in order to make the custom properties a bit more readable by first sorting the group by Name length first and then using this variable in both of the calculated properties

```
# Replace $data below with Import-Csv Testing.csv
$data | Group-Object -Property {
    $item = $_.Name
    $data.name | Where-Object { $item -match [regex]::Escape($_) } | Sort-Object { $_.Length } | Select-Object -First 1
} | ForEach-Object {
    $sortedGroup = $_.Group | Sort-Object { $_.Name.Length }
    $_ | Select-Object Count, Name, 
            @{n = 'Items'; e = { $sortedGroup.Name -join ', ' } }, 
            @{n = 'PIA';   e = { $sortedGroup.PIA -join ', ' } }
}

```

Output

```
Count Name      Items                                             PIA
----- ----      -----                                             ---
    1 Docusign  Docusign                                          56
    1 Mk Denial Mk Denial                                         78
    2 OneTrust  OneTrust, OneTrust Maintenance                    89, 75
    3 Workday   Workday, Which Workday, How will you join Workday 23, 23, 45

```
Following from my comments, you were on the right path from what you responded with. [`Group-Object`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/group-object?view=powershell-5.1) allows a *scriptblock* to be passed into your `-Property` parameter which you can in-turn use `-Replace` to replace the property with the name trying to be matched:

```
Import-Csv -Path ".\Testing.csv" | 
    Group-Object -Property { $_.Name -replace "(Jay)\w+",'$1' } -NoElement

```

Specifying `-NoElement` omits the members of the group from the results leaving you with:

```
Count Name                     
----- ----                     
    2 Ajay                     
    2 Jay                      
    1 Ray                      

```