some stuff with excel... probably with powershell
Here is what we have:
Location | Person |
---|---|
ZZQ | ZZQ-BOB |
ZZQ | ZZQ-JON |
ZZQ | ZZQ-TERRY |
ZZQ | ZZQ-JONAS |
ZZL | ZZL-BOB |
ZZL | ZZL-JOE |
ZZL | ZZL-GARY |
FTR | FTR-BOB |
FTR | FTR-BILL |
FTR | FTR-BORLIN |
FTR | FTR-TIM |
FTR | FTR-TOOL |
Hers is what we want:
Location | Person | Person | Person | Person | Person |
---|---|---|---|---|---|
ZZQ | ZZQ-BOB | ZZQ-JON | ZZQ-TERRY | ZZQ-JONAS | |
ZZL | ZZL-BOB | ZZL-JOE | ZZL-GARY | ||
FTR | FTR-BOB | FTR-BILL | FTR-BORLIN | FTR-TIM | FTR-TOOL |
- Run the powershell script
transpose-excel-sheet.ps1
- Provide the sheet index (example.
2
) - Provide the prefix number of characters (above example would be:
3
) - Provide the column alphabetical character (example:
A
) - Provide the path to the Excel file
.xls|.xlsx
file
- Provide the sheet index (example.
$sheetNumber = 2
$prefixLength = 3
$column = "A"
$file = "C:\path\to\file.xlsx"
PS> .\transpose-excel-sheet.ps1 $sheetNumber $prefixLength $column $file
- Export sheet with data to
.csv
- Run the powershell script
transpose-csv.ps1
- Provide the prefix number of characters (above example would be:
3
) - Provide the path to the
.csv
file that was exported
- Provide the prefix number of characters (above example would be:
- The transposed data will be output to
filename-transposed.csv
PS> .\transpose-csv.ps1 3 C:\path\to\file.csv