MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Missing data in query of Excel worksheet

Posted by george on December 07, 2001 1:51 PM

I am trying to merge data from Excel to Word. I have used a query to get the data in order. When I execute the query, some of the data that lives on the worksheet is gone. The data is a nine digit number. Some of the numbers have '#' as place holders. These data showup fine. The numbers that have no leading '#'s are missing. The data is 'text'. Please help

Posted by Jack on December 07, 2001 3:14 PM

This i know well sadly first lok at where your data comes in fropn, Excel is so powerful you can ODBC live from Unix SCO or Citrix or Bespoke programs on servers.

ODBC need correct configuration to kopenlive3 on the link and now we get complex

I guess might be wroung you squirting raw data from a datbase .. ok now odbc lets data flow AS IS, now not many people know this BUT raw data Exel hates, lables and values are the name of the game, ODBC wont care just bungs them in, Excel hates it.

Data being lost is a link and reporting problem # Excel will dislike.
What yopu need to do is set the SS to general formatting and then download and custom what you get to how you want it find replace convert what ever, they time to play.

Im UK and all my ODBC in Finance report in $ or Euros no good to me so i have to convert. Also te account numbers with A to start ae values??? so i convert to text..

OK try this
insetcol in A making original A to now be B
Copy down
Next auto filter to get rid on all correct and select #NA and in top of list = (Cell to left B ??)
Copy down
Take filter off, and sort however now copy all Col A and paste as value.
Delete Col b
Now you can do as you wish .. this is common in many companies i visit... Lables and text and values.

Let me know if you need more help.