Hi.
I asked CoPilot exactly what you posted above and provided the image you attached.
Extracting the Last 3 Values from Each Row to Form a New Table
To recreate a 3x3 table using only the last three values from each row (highlighted in green), follow this logic:
- Identify all rows with a green background. Each row contains multiple values.
- From each green row, extract the last 3 values.
- Create a new table with 3 rows and 3 columns. Each row corresponds to a green row, and each cell holds one of its final 3 values.
Example Data Extracted:
- Row 1: 10a, 11b, 12c
- Row 2: 20a, 21b, 22c
- Row 3: 30a, 31b, 32c
Resulting 3x3 Table:
Code:
10a | 11b | 12c
20a | 21b | 22c
30a | 31b | 32c
Tip: You can achieve this programmatically using Excel, Power Query, or even scripting tools depending on your source format. Let me know how you'd like to automate it!
CONTINUED… I’d suggest testing with a COPY of your excel sheet/workbook or ask CoPilot the way you did in this forum and keep the questions rolling with CoPilot
Variate the questions to CoPilots replies, don’t be scared to call it names or just be outright mean “CoPilot, you’re so dumb” it will only apologise.
Extracting the Last 3 Values from Each Row Using PowerShell and Batch Scripts
If you have a space-delimited text file where each row contains multiple values, and you want to extract only the final 3 from each line, use the scripts below.
PowerShell Method:
Powershell:
# Assumes input.txt contains your original data
Get-Content "input.txt" | ForEach-Object {
$fields = $_ -split '\s+'
$lastThree = $fields[-3..-1] -join ' '
$lastThree
} > "output.txt"
Batch Script Method:
Batch:
@echo off
setlocal enabledelayedexpansion
rem Assumes input.txt contains the data
for /f "tokens=* delims=" %%A in (input.txt) do (
set "line=%%A"
set "last="
for %%B in (!line!) do (
set /a count+=1
set "token[!count!]=%%B"
)
set /a start=count - 2
echo !token[%start%]! !token[%count%-1]! !token[%count%]!
set count=0
) >> output.txt
endlocal
Notes:
- These scripts output only the last 3 fields from each line.
- PowerShell is more concise and handles edge cases better.
- Batch is useful for environments with restricted scripting capabilities.
It says: Let me know if you'd like a version adapted for tab-delimited formats
or Excel-style processing!
I went with the PowerShell method and it worked fine, as long as I removed all trailing spaces or commas from the input text file. I will check out CoPilot in the future.
