Excel / Scripting Help
Discussion
I have an Excel XML file which is a download of a series of (let's say) 500 records.
I can open in Notepad++ as XML, or in Excel as an Excel XML file, whereby it displays nice and neatly for sort / filter.
Each record is contained over a varying series of rows, each row is effectively a sub-set of the record.
For example, Item A might have 10 rows - 0,1,2,3,4,5,6,7,8,9 - Item B might have 5 rows - 0,1,2,3,4
The entire spreadsheet is thousands of rows.
I want to DELETE the same 'row' from each item, so I know I'll be deleting 500 rows. I could just use a filter for 'row 3' and delete these rows or the text within them.
However, this leaves row labels as 0,1,2,4,5,6,7,8,9 and 0,1,2,4 which messes up my data integrity.
Tl;dr - how can I remove these rows without buggering up the contiguous numbering?
I can open in Notepad++ as XML, or in Excel as an Excel XML file, whereby it displays nice and neatly for sort / filter.
Each record is contained over a varying series of rows, each row is effectively a sub-set of the record.
For example, Item A might have 10 rows - 0,1,2,3,4,5,6,7,8,9 - Item B might have 5 rows - 0,1,2,3,4
The entire spreadsheet is thousands of rows.
I want to DELETE the same 'row' from each item, so I know I'll be deleting 500 rows. I could just use a filter for 'row 3' and delete these rows or the text within them.
However, this leaves row labels as 0,1,2,4,5,6,7,8,9 and 0,1,2,4 which messes up my data integrity.
Tl;dr - how can I remove these rows without buggering up the contiguous numbering?
seems there is no way to delete the row with out buggering up the number order as you are removing the entire row in your example.
However, based on what you wanted the next step is then 're-numbering' the remaining rows per product.
Something like inserting a new column to the left, then create a loop that goes through each product, starting at 1 then +1 for each row.
Or potentially something as simple as starting at Row 2, check if >+1 of Row 1, if not amend to +1 of the row before (this could then be product agonistic and just 'refresh' the numbers) for example
1
2
4
5
6
7
Should logically change to
1
2
3
4
5
6
as each of the numbers after the first amendment will be >+1 out
Unless you remove the first row as your change, you will need to add a product sanity check to it first and then start from 0? (come up with your own error checking based on your data)
However, based on what you wanted the next step is then 're-numbering' the remaining rows per product.
Something like inserting a new column to the left, then create a loop that goes through each product, starting at 1 then +1 for each row.
Or potentially something as simple as starting at Row 2, check if >+1 of Row 1, if not amend to +1 of the row before (this could then be product agonistic and just 'refresh' the numbers) for example
1
2
4
5
6
7
Should logically change to
1
2
3
4
5
6
as each of the numbers after the first amendment will be >+1 out
Unless you remove the first row as your change, you will need to add a product sanity check to it first and then start from 0? (come up with your own error checking based on your data)
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


