Forum Discussion
Solved
ExcelScript Get Rows in Array from Filter
I have an ExcelScript that I want to filter a Column by the State and get all the rows in an array that match that State. This is what I have so far: let selectedSheet = workbook.getActiveWorkshee...
I can't speak to your VBA/macro question. However, what you've described sounds as if the FILTER function itself might work. If you're not aware of that function--which has only been around for a few years--here's a YouTube video created by Microsoft to introduce it. Worth watching.
As variant
function main(workbook: ExcelScript.Workbook) {
let filteredRows: (number | string | boolean)[][] = [];
const tableName = "Table1"
const filterValue = "CA"
const filterBy = "CUSTOMER_STATE";
const table = workbook.getTable(tableName)
const index = table
.getHeaderRowRange()
.getValues()[0]
.indexOf(filterBy)
const dataRows = table
.getRangeBetweenHeaderAndTotal()
const nRows = dataRows.getRowCount()
for (let i = 0; i < nRows; i++) {
const values = dataRows.getRow(i).getValues()[0];
if (values[index] === filterValue) {
filteredRows.push(values)
}
}
console.log(filteredRows)
}depend on what you are going to do with that array of arrays (filtered rows x columns) - perform some aggregation, copy to another table, etc.
- Thanks!
I appreciate it.smithmrk , you are welcome
