Excel / Scripting Help
Author
Discussion

timetex

Original Poster:

688 posts

179 months

Wednesday 12th September 2018
quotequote all
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?

AndrewEH1

4,922 posts

184 months

Wednesday 12th September 2018
quotequote all
Instead of having an integer in that cell maybe have =(A1+1)

A1 being the first row with 0 in it

You'd just need to copy that formula down and it would work if rows were deleted

Ignore that, just re-read your OP

Edited by AndrewEH1 on Wednesday 12th September 12:37

louiebaby

11,024 posts

222 months

Wednesday 12th September 2018
quotequote all
I'm confused, can you post a bit of an example using the table bits out of the formatting?

Or a picture?

TallTom

208 posts

190 months

Wednesday 12th September 2018
quotequote all
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)

TheGroover

1,044 posts

306 months

Wednesday 12th September 2018
quotequote all
Could you write some vba to open the document as xml, then remove the nodes you don't want, then open the new modified xml?
Are the row numbers added by excel on opening or are they contained in the source xml?