Useful vba code snipits

And !! Unprotect worksheet

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

  Dim ws As Worksheet

  Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name

  ' Check if the double-click is on a row

  If Target.Row > 0 Then

    ' Unprotect worksheet

    ws.Unprotect Password:="YourPassword" ' Replace "YourPassword" with your actual password

    ' Check the current fill color of the row

    If Target.Interior.Color = RGB(255, 255, 255) Then

      ' If white, turn it green

      Target.EntireRow.Interior.Color = RGB(0, 255, 0)

    Else

      ' If green, turn it back to white

      Target.EntireRow.Interior.Color = RGB(255, 255, 255)

    End If

    ' Protect worksheet again

    ws.Protect Password:="YourPassword" ' Replace "YourPassword" with your actual password

  End If

  Cancel = True ' Cancel default double-click behavior

End Sub

New snip ps to combine Cavs

# Set the path to the folder containing your CSV files

$folderPath = "C:\Path\To\Your\CSVFiles"

# Get all CSV files in the folder

$csvFiles = Get-ChildItem -Path $folderPath -Filter *.csv

# Output file path

$outputFilePath = Join-Path $folderPath -ChildPath "output.csv"

# Initialize a flag to skip header for files after the first one

$skipHeader = $false

# Loop through each CSV file and combine into output.csv

foreach ($csvFile in $csvFiles) {

  # Import CSV file

  $data = Import-Csv $csvFile.FullName

  # Skip header if $skipHeader is true

  if ($skipHeader) {

    $data = $data | Select-Object -Skip 1

  } else {

    $skipHeader = $true

  }

  # Append data to output.csv

  $data | Export-Csv -Path $outputFilePath -Append -NoTypeInformation

}

Write-Host "CSV files combined. Output file: $outputFilePath"

And same for text files !!

# Specify the folder path

$folderPath = "C:\Path\To\Your\Folder"

# Get all text files in the folder

$textFiles = Get-ChildItem -Path $folderPath -Filter *.txt

# Group files by content hash

$groupedFiles = $textFiles | Group-Object { Get-Content $_.FullName | Get-Hash }

# Iterate through groups and delete duplicates

foreach ($group in $groupedFiles) {

  if ($group.Count -gt 1) {

    $group.Group | Select-Object -Skip 1 | ForEach-Object { Remove-Item $_.FullName -Force }

  }

}

Write-Host "Duplicate text files deleted successfully."

Some more import stuff with extra bits

Sub ImportData()

  Dim filePath As String

  Dim ws As Worksheet

  Dim lastRow As Long

  ' Set the file path

  filePath = "C:\Path\To\Your\File.txt" ' Update with your file path

  ' Set the worksheet where you want to import the data

  Set ws = ThisWorkbook.Sheets("Sheet1") ' Update with your sheet name

  ' Clear existing data

  ws.UsedRange.Clear

  ' Import specific columns

  With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))

    .TextFileConsecutiveDelimiter = False

    .TextFileTabDelimiter = False

    .TextFileCommaDelimiter = False

    .TextFilePlatform = xlWindows

    .TextFileConsecutiveDelimiter = False

    .TextFileOtherDelimiter = "|"

    .Refresh

  End With

  ' Keep only specific columns (5, 6, 30, 78, 190)

  ws.Columns("A:D,E:E,AD:AD,BN:BN").Delete

  ' Remove blank rows

  On Error Resume Next

  ws.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

  On Error GoTo 0

  ' Remove duplicate rows

  lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

  ws.Range("A1:A" & lastRow).RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Sub ApplyTableToData()

  Dim ws As Worksheet

  Dim rng As Range

  Dim tbl As ListObject

  ' Set the worksheet and range containing your data

  Set ws = ThisWorkbook.Sheets("Sheet1") ' Update with your sheet name

  Set rng = ws.Range("A1:E10") ' Update with your data range

  ' Create a table

  Set tbl = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)

  ' Optionally, you can customize the table properties

  tbl.TableStyle = "TableStyleMedium9" ' Update with your desired table style

  tbl.Name = "MyDataTable" ' Update with your desired table name

End Sub

# Set the path to the folder containing CSV files

$folderPath = "C:\Path\To\Your\Folder"

# Set the output text file path

$outputFilePath = "C:\Path\To\Your\Output\CombinedFile.txt"

# Get all CSV files in the folder

$csvFiles = Get-ChildItem -Path $folderPath -Filter *.csv

# Initialize an empty array to store content

$combinedContent = @()

# Loop through each CSV file and append its content to the array

foreach ($csvFile in $csvFiles) {

  $content = Get-Content $csvFile.FullName

  $combinedContent += $content

}

# Write the combined content to the output text file

$combinedContent | Out-File -FilePath $outputFilePath -Encoding UTF8

Write-Host "Combination complete. Output file: $outputFilePath"

# Load Outlook COM Object

$outlook = New-Object -ComObject Outlook.Application

$namespace = $outlook.GetNamespace("MAPI")

$inbox = $namespace.GetDefaultFolder(6) # 6 corresponds to the Inbox folder

# Create a text file for storing extracted data

$outputFilePath = "C:\Path\To\Your\Output\File.txt"

# Iterate through each email in the inbox

foreach ($email in $inbox.Items) {

  # Extract body, subject, and date

  $body = $email.Body

  $subject = $email.Subject

  $date = $email.ReceivedTime

  # Append data to the text file delimited by pipe symbol

  "$body`|$subject`|$date" | Out-File -Append -FilePath $outputFilePath

}

# Release Outlook COM Object

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($outlook) | Out-Null

# Load Outlook COM Object

$outlook = New-Object -ComObject Outlook.Application

$namespace = $outlook.GetNamespace("MAPI")

$inbox = $namespace.GetDefaultFolder(6) # 6 corresponds to the Inbox folder

# Create an array to store email data

$emailData = @()

# Iterate through each email in the inbox

foreach ($email in $inbox.Items) {

  # Extract body, subject, and date/time received

  $body = $email.Body -replace "[\r\n]+", " " # Normalize line breaks

  $subject = $email.Subject

  $dateTimeReceived = $email.ReceivedTime

  # Create an object with email data

  $emailObject = [PSCustomObject]@{

    Body = $body

    Subject = $subject

    DateTimeReceived = $dateTimeReceived

  }

  # Add the object to the array

  $emailData += $emailObject

}

# Release Outlook COM Object

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($outlook) | Out-Null

# Save the array to a CSV file

$outputFilePath = "C:\Path\To\Your\Output\File.csv"

$emailData | Export-Csv -Path $outputFilePath -NoTypeInformation

Write-Host "Data saved to $outputFilePath"

PS to run .bat

Start-Process -FilePath "C:\scripts\example.bat" -NoNewWindow -Wait

$computerName = "RemoteComputerName"

if (Test-Connection -ComputerName $computerName -Count 1 -Quiet) {

  Write-Host "$computerName is up and running."

} else {

  Write-Host "$computerName is not reachable. Attempting to bring it up..."

  Restart-Computer -ComputerName $computerName -Force

}

Sub LookupValueAndReturnRow()

  Dim searchValue As String

  Dim lookupRange As Range

  Dim foundCell As Range

  ' Set the value to search for

  searchValue = "ValueToSearchFor"

  ' Set the range to search in (change Sheet1 to your sheet name and A:A to your column)

  Set lookupRange = Sheets("Sheet1").Columns("A:A")

  ' Search for the value

  Set foundCell = lookupRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)

  ' Check if value is found

  If Not foundCell Is Nothing Then

    MsgBox "Value found at row: " & foundCell.Row

  Else

    MsgBox "Value not found"

  End If

End Sub

# Set the path to your Excel workbook

$excelFilePath = "C:\Path\To\Your\Excel\File.xlsx"

# Set the name of the worksheet you want to read data from

$worksheetName = "Sheet1"

# Set the path where you want to save the CSV file

$csvFilePath = "C:\Path\To\Your\Output\File.csv"

# Create a new Excel application object

$excel = New-Object -ComObject Excel.Application

# Make Excel visible (optional)

$excel.Visible = $true

# Open the workbook

$workbook = $excel.Workbooks.Open($excelFilePath)

# Select the worksheet

$worksheet = $workbook.Worksheets.Item($worksheetName)

# Get the range of data in the worksheet

$dataRange = $worksheet.UsedRange

# Export data to CSV file

$dataRange | Export-Csv -Path $csvFilePath -NoTypeInformation

# Close the workbook without saving changes

$workbook.Close($false)

# Close Excel application

$excel.Quit()

# Release COM objects

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

# Set the path to your Excel workbook

$excelFilePath = "C:\Path\To\Your\Excel\File.xlsx"

# Set the name of the worksheet you want to read data from

$worksheetName = "Sheet1"

# Set the path where you want to save the CSV file

$csvFilePath = "C:\Path\To\Your\Output\File.csv"

# Create a new Excel application object

$excel = New-Object -ComObject Excel.Application

# Make Excel visible (optional)

$excel.Visible = $true

# Open the workbook

$workbook = $excel.Workbooks.Open($excelFilePath)

# Select the worksheet

$worksheet = $workbook.Worksheets.Item($worksheetName)

# Get the range of used cells in the worksheet

$usedRange = $worksheet.UsedRange

$rowCount = $usedRange.Rows.Count

$columnCount = $usedRange.Columns.Count

# Open or create the CSV file

$csvFile = New-Object -TypeName System.IO.StreamWriter -ArgumentList $csvFilePath

# Loop through each row in the worksheet

for ($row = 1; $row -le $rowCount; $row++) {

  $rowData = @()

  # Loop through each column in the row

  for ($col = 1; $col -le $columnCount; $col++) {

    # Get the value of the cell and add it to the row data array

    $cellValue = $worksheet.Cells.Item($row, $col).Text

    $rowData += $cellValue

  }

  # Convert the row data array to a comma-separated string and write it to the CSV file

  $csvFile.WriteLine($rowData -join ",")

}

# Close the CSV file

$csvFile.Close()

# Close the workbook without saving changes

$workbook.Close($false)

# Close Excel application

$excel.Quit()

# Release COM objects

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

# Set the path to your Excel workbook

$excelFilePath = "C:\Path\To\Your\Excel\File.xlsx"

# Set the name of the worksheet you want to read data from

$worksheetName = "Sheet1"

# Set the name of the named range you want to use

$namedRange = "MyNamedRange"

# Set the path where you want to save the CSV file

$csvFilePath = "C:\Path\To\Your\Output\File.csv"

# Create a new Excel application object

$excel = New-Object -ComObject Excel.Application

# Make Excel visible (optional)

$excel.Visible = $true

# Open the workbook

$workbook = $excel.Workbooks.Open($excelFilePath)

# Select the worksheet

$worksheet = $workbook.Worksheets.Item($worksheetName)

# Get the named range

$range = $worksheet.Range($namedRange)

# Get the row and column counts from the range

$rowCount = $range.Rows.Count

$columnCount = $range.Columns.Count

# Open or create the CSV file

$csvFile = New-Object -TypeName System.IO.StreamWriter -ArgumentList $csvFilePath

# Loop through each row in the range

for ($row = 1; $row -le $rowCount; $row++) {

  $rowData = @()

  # Loop through each column in the row

  for ($col = 1; $col -le $columnCount; $col++) {

    # Get the value of the cell and add it to the row data array

    $cellValue = $range.Cells.Item($row, $col).Text

    $rowData += $cellValue

  }

  # Convert the row data array to a pipe-separated string and write it to the CSV file

  $csvFile.WriteLine($rowData -join "|")

}

# Close the CSV file

$csvFile.Close()

# Close the workbook without saving changes

$workbook.Close($false)

# Close Excel application

$excel.Quit()

# Release COM objects

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($range) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Sub ExportNamedRangeToTextFile()

  Dim filePath As String

  Dim cell As Range

  Dim delimiter As String

  Dim fileNum As Integer

  ' Set the file path where you want to save the text file

  filePath = "C:\YourFolder\YourFile.txt"

  ' Define the delimiter

  delimiter = "|"

  ' Open a new text file for writing

  fileNum = FreeFile

  Open filePath For Output As #fileNum

  ' Loop through each cell in the named range

  For Each cell In ThisWorkbook.Names("YourNamedRange").RefersToRange

    ' Write the cell value to the text file

    Print #fileNum, cell.Value;

    ' Print the delimiter except for the last cell in each row

    If cell.Column <> ThisWorkbook.Names("YourNamedRange").RefersToRange.Columns.Count Then

      Print #fileNum, delimiter;

    End If

  Next cell

  ' Add a carriage return after each row

  Print #fileNum,

  ' Close the text file

  Close #fileNum

  MsgBox "Export complete."

End Sub

Sub ExportNamedRangeRowsToTextFile()

  Dim filePath As String

  Dim rowNum As Long

  Dim colNum As Long

  Dim delimiter As String

  Dim fileNum As Integer

  Dim cellValue As Variant

  Dim namedRange As Range

  ' Set the file path where you want to save the text file

  filePath = "C:\YourFolder\YourFile.txt"

  ' Define the delimiter

  delimiter = "|"

  ' Set the named range

  Set namedRange = ThisWorkbook.Names("YourNamedRange").RefersToRange

  ' Open a new text file for writing

  fileNum = FreeFile

  Open filePath For Output As #fileNum

  ' Loop through each row in the named range

  For rowNum = 1 To namedRange.Rows.Count

    ' Loop through each column in the current row

    For colNum = 1 To namedRange.Columns.Count

      ' Get the value of the current cell

      cellValue = namedRange.Cells(rowNum, colNum).Value

      ' Write the cell value to the text file

      If colNum > 1 Then

        Print #fileNum, delimiter; cellValue;

      Else

        Print #fileNum, cellValue;

      End If

    Next colNum

    ' Move to the next line in the text file after each row

    Print #fileNum,

  Next rowNum

  ' Close the text file

  Close #fileNum

  MsgBox "Export complete."

End Sub

Sub CopyWordFiles()

  Dim sourceFolder As String

  Dim destinationFolder As String

  Dim fileExtension As String

  Dim objFSO As Object

  Dim objFolder As Object

  Dim objSubFolder As Object

  Dim objFile As Object

  ' Define the source folder path

  sourceFolder = "C:\SourceFolder\"

  ' Define the destination folder path

  destinationFolder = "C:\DestinationFolder\"

  ' Define the file extension to search for

  fileExtension = "*.doc*"

  ' Create a File System Object

  Set objFSO = CreateObject("Scripting.FileSystemObject")

  ' Get the source folder

  Set objFolder = objFSO.GetFolder(sourceFolder)

  ' Loop through each file in the source folder

  For Each objFile In objFolder.Files

    ' Check if the file has the specified extension

    If objFSO.GetExtensionName(objFile.Path) Like "doc*" Then

      ' Copy the file to the destination folder

      objFSO.CopyFile objFile.Path, destinationFolder

    End If

  Next objFile

  ' Recursively search through subfolders

  For Each objSubFolder In objFolder.SubFolders

    ' Call a recursive function to search through subfolders

    RecursiveSearch objSubFolder, destinationFolder

  Next objSubFolder

  MsgBox "File copy completed."

End Sub

Sub RecursiveSearch(ByVal objFolder As Object, ByVal destinationFolder As String)

  Dim objFSO As Object

  Dim objSubFolder As Object

  Dim objFile As Object

  ' Create a File System Object

  Set objFSO = CreateObject("Scripting.FileSystemObject")

  ' Loop through each file in the current folder

  For Each objFile In objFolder.Files

    ' Check if the file has the specified extension

    If objFSO.GetExtensionName(objFile.Path) Like "doc*" Then

      ' Copy the file to the destination folder

      objFSO.CopyFile objFile.Path, destinationFolder

    End If

  Next objFile

  ' Recursively search through subfolders

  For Each objSubFolder In objFolder.SubFolders

    RecursiveSearch objSubFolder, destinationFolder

  Next objSubFolder

End Sub

String into SQL

Sub AddNumbersToSQLCriteriaAndOutput()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim criteria As String
Dim conn As Object
Dim rs As Object
Dim strSQL As String

' Assuming the numbers are in column A starting from the second row
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Initialize the criteria string
criteria = ""

' Loop through each cell in column A and add the number to the criteria string
For i = 2 To lastRow ' Assuming data starts from row 2
    If IsNumeric(ws.Cells(i, 1).Value) Then
        criteria = criteria & ws.Cells(i, 1).Value & ","
    End If
Next i

' Remove the trailing comma
If Len(criteria) > 0 Then
    criteria = Left(criteria, Len(criteria) - 1)
End If

' Your SQL statement with the criteria added
strSQL = "SELECT * FROM YourTable WHERE YourColumn IN (" & criteria & ");"

' Open a connection to your database (replace the connection string and provider as necessary)
Set conn = CreateObject("ADODB.Connection")
conn.Open "your_connection_string_here"

' Execute the SQL statement
Set rs = conn.Execute(strSQL)

' Paste the results into a specified column (e.g., column B starting from row 2)
ws.Range("B2").CopyFromRecordset rs

' Close the recordset and connection
rs.Close
conn.Close

End Sub

Sub FindDuplicateRows()
Dim wsSource1 As Worksheet, wsSource2 As Worksheet, wsSource3 As Worksheet
Dim wsOutput As Worksheet
Dim lastRow1 As Long, lastRow2 As Long, lastRow3 As Long
Dim i As Long, j As Long
Dim duplicateFound As Boolean

' Set your source and output worksheets
Set wsSource1 = ThisWorkbook.Sheets("SourceSheet1")
Set wsSource2 = ThisWorkbook.Sheets("SourceSheet2")
Set wsSource3 = ThisWorkbook.Sheets("SourceSheet3")
Set wsOutput = ThisWorkbook.Sheets("OutputSheet")

' Clear previous results in output sheet
wsOutput.Cells.Clear

' Get the last row of data in each source sheet
lastRow1 = wsSource1.Cells(wsSource1.Rows.Count, "A").End(xlUp).Row
lastRow2 = wsSource2.Cells(wsSource2.Rows.Count, "A").End(xlUp).Row
lastRow3 = wsSource3.Cells(wsSource3.Rows.Count, "A").End(xlUp).Row

' Loop through each row in the first dataset
For i = 2 To lastRow1 ' Assuming row 1 contains headers
    duplicateFound = False
    ' Check if this row is a duplicate of any previous row
    For j = 1 To i - 1
        If RowsAreEqual(wsSource1.Rows(i), wsSource1.Rows(j)) Then
            ' If duplicate is found, output the row numbers to the output sheet
            wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = "Row " & i & " (Sheet1) is a duplicate of Row " & j
            duplicateFound = True
            Exit For
        End If
    Next j
Next i

' Loop through each row in the second dataset
For i = 2 To lastRow2 ' Assuming row 1 contains headers
    duplicateFound = False
    ' Check if this row is a duplicate of any previous row
    For j = 1 To i - 1
        If RowsAreEqual(wsSource2.Rows(i), wsSource2.Rows(j)) Then
            ' If duplicate is found, output the row numbers to the output sheet
            wsOutput.Cells(wsOutput.Rows.Count, "B").End(xlUp).Offset(1, 0).Value = "Row " & i & " (Sheet2) is a duplicate of Row " & j
            duplicateFound = True
            Exit For
        End If
    Next j
Next i

' Loop through each row in the third dataset
For i = 2 To lastRow3 ' Assuming row 1 contains headers
    duplicateFound = False
    ' Check if this row is a duplicate of any previous row
    For j = 1 To i - 1
        If RowsAreEqual(wsSource3.Rows(i), wsSource3.Rows(j)) Then
            ' If duplicate is found, output the row numbers to the output sheet
            wsOutput.Cells(wsOutput.Rows.Count, "C").End(xlUp).Offset(1, 0).Value = "Row " & i & " (Sheet3) is a duplicate of Row " & j
            duplicateFound = True
            Exit For
        End If
    Next j
Next i

' Inform user when no duplicates are found
If Not duplicateFound Then
    MsgBox "No duplicate rows found.", vbInformation
End If

End Sub

Function RowsAreEqual(row1 As Range, row2 As Range) As Boolean
' Function to check if two rows are equal
Dim i As Long
Dim colCount As Long

colCount = row1.Columns.Count

RowsAreEqual = True
For i = 1 To colCount
    If row1.Cells(1, i).Value <> row2.Cells(1, i).Value Then
        RowsAreEqual = False
        Exit Function
    End If
Next i

End Function

Sub FindDuplicateRows()
Dim wsSource1 As Worksheet, wsSource2 As Worksheet, wsSource3 As Worksheet
Dim wsOutput As Worksheet
Dim data1 As Variant, data2 As Variant, data3 As Variant
Dim i As Long, j As Long
Dim dict1 As Object, dict2 As Object, dict3 As Object
Dim key As String
Dim duplicateFound As Boolean

' Set your source and output worksheets
Set wsSource1 = ThisWorkbook.Sheets("SourceSheet1")
Set wsSource2 = ThisWorkbook.Sheets("SourceSheet2")
Set wsSource3 = ThisWorkbook.Sheets("SourceSheet3")
Set wsOutput = ThisWorkbook.Sheets("OutputSheet")

' Clear previous results in output sheet
wsOutput.Cells.Clear

' Read data from each source sheet into arrays
data1 = wsSource1.UsedRange.Value
data2 = wsSource2.UsedRange.Value
data3 = wsSource3.UsedRange.Value

' Create dictionaries to store unique rows for each dataset
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")

' Loop through each row in the first dataset
For i = 2 To UBound(data1, 1)
    key = GetRowKeyFromArray(data1, i)
    If dict1.exists(key) Then
        ' If duplicate is found, output the row numbers to the output sheet
        wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = "Row " & i & " (Sheet1) is a duplicate of Row " & dict1(key)
        duplicateFound = True
    Else
        dict1(key) = i
    End If
Next i

' Loop through each row in the second dataset
For i = 2 To UBound(data2, 1)
    key = GetRowKeyFromArray(data2, i)
    If dict2.exists(key) Then
        ' If duplicate is found, output the row numbers to the output sheet
        wsOutput.Cells(wsOutput.Rows.Count, "B").End(xlUp).Offset(1, 0).Value = "Row " & i & " (Sheet2) is a duplicate of Row " & dict2(key)
        duplicateFound = True
    Else
        dict2(key) = i
    End If
Next i

' Loop through each row in the third dataset
For i = 2 To UBound(data3, 1)
    key = GetRowKeyFromArray(data3, i)
    If dict3.exists(key) Then
        ' If duplicate is found, output the row numbers to the output sheet
        wsOutput.Cells(wsOutput.Rows.Count, "C").End(xlUp).Offset(1, 0).Value = "Row " & i & " (Sheet3) is a duplicate of Row " & dict3(key)
        duplicateFound = True
    Else
        dict3(key) = i
    End If
Next i

' Inform user when no duplicates are found
If Not duplicateFound Then
    MsgBox "No duplicate rows found.", vbInformation
End If

End Sub

Function GetRowKeyFromArray(data As Variant, rowNum As Long) As String
' Function to generate a unique key for a row from an array
Dim key As String
Dim i As Long

For i = LBound(data, 2) To UBound(data, 2)
    key = key & "|" & data(rowNum, i)
Next i

GetRowKeyFromArray = key

End Function

$inputFile = "input.txt"

$outputFile = "duplicates.txt"

$lines = Get-Content $inputFile

$duplicates = $lines | Group-Object | Where-Object { $.Count -gt 1 } | ForEach-Object { $.Group }

$duplicates | Out-File $outputFile

$csvPath = "path\to\your\file.csv"

$logPath = "path\to\your\log\file.log"

$csvData = Import-Csv -Path $csvPath -Delimiter "^"

$groupedData = $csvData | Group-Object -Property Field1, Field2, Field3

$duplicateGroups = $groupedData | Where-Object { $_.Count -gt 1 }

foreach ($group in $duplicateGroups) {
$group.Group | Export-Csv -Path $logPath -Append -Delimiter "^" -NoTypeInformation
}

Write-Host "Duplicate records have been written to $logPath"