Posted by Simon McArdle on May 07, 2001 11:26 PM
1 Are you always using the same CSV file (same name,
same location)? = No. The file has different names and different locations but the format of the file is always the same. (Not the same data though)
2 Where does the reference number reside in the
spreadsheet (i.e., is it always in column A)? = The refence number is always in the same column.
3 Where do you put the status (column B)? = The status also has its own column (Column AF).
4 Is this spreadsheet being added to (putting new
reference numbers at the bottom) or is every reference
number in the spreadsheet missing it's status code? = The spreadsheet is not being added to. The status codes are missing.
I hope this clears it up a little. Please let me know if you need any more info.
Thanks again Barrie.
Simon
Posted by Kevin James on May 07, 2001 11:51 PM
Hi Simon,
If there is a one-to-one correspondency from the CSV to the Excel, why not just open the CSV in Excel and then copy the status column to your formatted sheet?
Kevin
Posted by Barrie Davidson on May 08, 2001 7:45 AM
Hi Simon, have you had a chance to look at Kevin's suggestion? Another way to do this (assuming that the reference numbers in the CSV file are not ordered the same as your spreadsheet) is to open the CSV file and then use a VLOOKUP. You could then copy and paste values over-writing the formula and keeping the status codes hard-coded in your spreadsheet.
Let me know if you think this might work better for you.
Barrie
Posted by Simon McArdle on May 08, 2001 9:12 AM
Re: Kevin has a good idea
Hi Barrie,
I would not know where to start really. Its the matching the data in the csv file with spreadsheet that gets me. I really do not know what is the best solution to this. Or where to start.
Any ideas?
Simon
ps..is it possible to give me an example that I could try out on the actuall spreadsheet and csv file.
Posted by Barrie Davidson on May 08, 2001 9:36 AM
I'll assume the following:
Your reference number (in the spreadsheet) is in column A and spans A1 to A100
Your status code (in the spreadsheet) is in column AF
When you open the CSV file in Excel, the reference number (in the CSV file) is in column A
The status code (in the CSV file) is in column B
The CSV data spans A1:B100
The sheet name, when you open the CSV file, is called CSV.
The CSV file name is Test.csv
You would enter the following formula in cell AF1,
=VLOOKUP(A1,[TEST.CSV]CSV!$A$1:$B$100,2,0), and copy the formula down. You would then select AF1:AF100, copy the data, and then paste values (over-writing the formula).
Let me know if this helps
Barrie
Posted by Simon McArdle on May 08, 2001 12:17 PM
Re: You wanted an example
Hi Barrie,
Thanks I will try it in the Morning. Its 9.15pm here in Belgium. Can I ask you for more help if I need it?
Thanks again.
Simon
Posted by Barrie Davidson on May 08, 2001 12:33 PM
No problem. By the way, if you're interested, I'm in Winnipeg, Canada (it's 2:30 in the afternoon here). Isn't the WWW great!!
Posted by Simon McArdle on May 09, 2001 4:27 AM
Re: You wanted an example
Hi Barrie,
This example seems to be just as manual as doing the job the long way round.
The example assumes a file named test.csv. (in reality I do not know what this file is called until I get it from the network. I also do not know where on the network it is until I navigate to it.
Then I need to assume the name of the excel sheet. I do infact have 10 excel sheets in the spreadsheet. The reference number and status thankfully are always in the same location.
How do I open the CSV file from the network, compare the reference numbers, and then populate the correct OK or Not OK. I can not enter a formular into AF1 because it already has text in it.
Is it a Macro that I need here or is it a formular. I am not sure that I have explained the situation correctly.
Any suggestions?
Thanks
Simon
Posted by Simon McArdle on May 09, 2001 7:55 AM
Would it be easier if I put the spreadsheet up on a website or something so you could take a look.
Thanks
Simon
Posted by Barrie Davidson on May 09, 2001 8:19 AM
Assumptions and formula vs. macro
Hi Simon, I'll try to answer your questions.
Q-How do I open the CSV file from the network?
A-Select File|Open (just like opening a regular spreadsheet) EXCEPT change the "Files of type" box from worksheets (*.xls) to text files (*.prn; *.txt; *.csv). This is assuming that your CSV file has the ".csv" extension. If the extension is something different, change to All files (*.*). You'll then be able to select the file from your network.
Q-How do I compare the reference numbers?
A-You could do this using the VLOOKUP function I provided. The thing I am not clear about is that you are telling me that you need to put the status code in column AF (indicating to me that there is no information in those cells) and in this message you are telling me that you can't put a formula in those cells because they already contain text. What text do you already have in those cells?
Barrie
Posted by Simon McArdle on May 09, 2001 11:48 AM
Re: Assumptions and formula vs. macro
Hi Barrie,
I have put a copy up on my web page. It is at (broken link) If yo have a look at it you will probably instantly see the issue. (Only 1 sheet put up). Let me know what you think.
Simon