Excel help? imported data could be missing.

Heedtheadvice

Can anybody help, someone who has very advanced knowledge.

I have loaded some important data into my spreadsheet, (it is a sort of file with some commas between individual items -does that make sense?) and there is lots of it sent to me by others. However somebody thinks some of it might be missing.

Each data item has several lines and there are well over over ten thousand of them. Possibly many more!

Anybody know why some might be missing and what I should do to make it work better? I work in IT but cannot understand what is going on but some schoolboy who has no real experience and is a pure amateur said I should use a database! That is far too complicated and slow to learn and we need fast results and I have produced many a graph before now so I feel I have expertise!

Help!

@cov-2 team leader

tempus_fugit

Is the data just missing from the Excel file or from the original (comma-delimited) data file?
It's difficult to know why some might be missing, it could really be anything from something in the data file interrupting the importing of data, e.g. an extra comma or a space or anything that means the data items are not in the place expected, to errors in the spreadsheet after the data has been imported or somehow they have simply got deleted, so more information would really be required. What makes this person think that the data is missing, for example, and can they specify what data is missing and show that is in the original files but not in the spreadsheet? You could then check at the import stage to see if it is importing it correctly.

lr1277

I think the OP is mocking the problems in getting the Covid test data.
According to the BBC news site, it has something to do with an old Excel version, which can only handle a few thousand rows. If the data gathering body (Public Health England?) had used the current Excel version, the system could have handled a million rows of data IIRC. At the moment another workaround is being implemented.

keithdc

Heedtheadvice said:

Can anybody help, someone who has very advanced knowledge.

I have loaded some important data into my spreadsheet, (it is a sort of file with some commas between individual items -does that make sense?) and there is lots of it sent to me by others. However somebody thinks some of it might be missing.

Each data item has several lines and there are well over over ten thousand of them. Possibly many more!

Anybody know why some might be missing and what I should do to make it work better? I work in IT but cannot understand what is going on but some schoolboy who has no real experience and is a pure amateur said I should use a database! That is far too complicated and slow to learn and we need fast results and I have produced many a graph before now so I feel I have expertise!

Help!

@cov-2 team leader

I really think that your team in the Department of Health should find a different way to collate the COVID test result data! 😀😀

debitcardmayhem

The Function should read =SUM(F($1)+U(S1)+D($1)+G($1)+E($1))

inspectorperez

Dear Heedtheadvice,

The magnificent contribution you have made thus far in this current pandemic crisis has not gone unnoticed!

I have today recommended to my Honours Committee that your name is put forward for the highest civilian award possible in recognition of this fine work.

I am also recommending that you are seconded to other important government departments where your immense experience can be shared, particularly the Treasury, where your spreadsheets could play a vital part in the country's economic recovery!

Yours sincerely

BJ

John_Gray

The sneaky OP may also be referring to the horrors of the Comma-Separated Variables (CSV) file format regarding the treatment of embedded commas in the variables.  Unless included in double-quote (") characters, they will be treated as representing a null variable, and subsequent variables will be associated with the wrong columns.  For an interesting article on the subject of Excel, plus a link to a website listing very many instances where organisations have lost money by the misuse/misunderstanding of Excel spreadsheets, see here.

stragglebod

Heedtheadvice said:

Each data item has several lines and there are well over over ten thousand of them. Possibly many more!

When I saw the news report this is what jumped out at me.

Rule number one of using any kind of spreadsheet to manage lists: 1 record = 1 row.

Rule number two: never use xls.

Rule number three: fire anyone who doesn't understand rules one and two.

Heedtheadvice

Well most of you know better than the "experts"!!

Considering the number of problems there have been with gov 'projects' it really comes as no surprise.

The teams designing the 'solutions' obviously know less than they think or their hands are tied by masters who have no idea how to manage. There are so many flaws in how things are done, IMHO -having done similar work successfully for several years- they cannot have gone through a proper design process. It is a sad state of affairs!

WaywardDriver

Beggars belief that for something as important as this Excel was used rather than a proper database solution. 

Neil_Jones

Nothing surprises me with IT and government, I believe a lot of NHS computer systems are still running on Windows XP for one reason or another.  Wouldn't surprise me if the rest of the the Health department is stuck on XP too, and the "system upgraded" tooted turns out to be... Windows 7, with the same version of Office (and/or template) that caused the issue in the first place...