Help! Database question!

  • Thread starter Thread starter Gloria Nunez
  • Start date Start date
G

Gloria Nunez

Hello!

I am a damsel in distress! I've been given a small project, but I
really don't have much experience with databases or spreadsheets. I'm
not sure if I should be using excel or access to handle this......Here
is my problem...

I have 2 databases...actually they are .csv files.
The first database is called PRODUCTS.
It consist of 4000 records and about 5 different fields. The unique
key identifier (or whatever it's called) is the SERIALNO. (a 10 digit
number). Looks a little something like this...

SERIALNO NAME MANUFAC CONTACT COST
5001226001 browning china 6268531212 100
5001226002 browning china 6268221215 100
5001226045 thompson mexico 6268541232 300
5001227001 browning china 6269531242 100
5001228001 thompson china 6268321212 200
5001229331 manning usa 6268531882 100
5001229431 manning usa 6268531255 500
5001229930 browning china 6268531211 100
etc...

The 2nd .csv file is called NEWCODE. It only consist of 3000 records
and 2 fields. SERIALNO and CODE. looks something like this.

SERIALNO CODE
5001226001 46113
5001226002 36651
5001226045 11231
5001227001 54651
5001228001 46231
5001229331 46331
5001229431 12558
5001229930 12399
etc...


I need to add the CODE data form NEWCODE.csv to the PRODUCTS.csv
file. There are no duplicate numbers in SERIALNO and CODE.

So it goes something like this... If SERIALNO in NEWCODE.csv matches
SERIALNO in PRODUCTS.csv, then add CODE (from NEWCODE) to
PRODUCTS.csv.

I don't have the slightest idea on how to do this. I thought I could
do it in excel, but had no luck. is this something I can do in
access? Does anyone have the time to give me step by step
instructions?

Thank you!
Gloria
 
Hi Gloria
try the following:
1. We have to convert your two CSV files into Excel files:
- open Excel and create a new (empty) workbook (save this workbook)
- now open both CSV files (Excel should do the conversion
automatically -> you now have three different files
- in each of the imported CSV files right-click on the tab name and
choose 'Move/Copy' in the context menu
- copy the worksheet to your newly created workbook
- After this save the master workbook, close the imported CSV files
- You should now have a file with both CSV files incorporated (with
worksheets names 'NEWCODE' and 'PRODUCTS)

2. Now enter the following formula in F2 of your products worksheet
(column F will get the code data)
=VLOOKUP(A1,'NEWCODE'!$A$!:$B$4000,2,0)
copy this formula down for all rows (you also may double click on the
lower right corner of this cell and Excel will fill down the formula
automatically for all rows)

3. If you need a CSV file for further processing you may save this
products worksheet as a CSV file
 
Frank, you are awesome!
I'm going to give it a try right now. I'm just curious, at the end of
the formula, there is a 2,0. What does that do?


Thanks!
Gloria
 
Hi Frank.

I tried using the forumula but got an "formula contains an error..."
message.

=VLOOKUP(A1,'NEWCODE'!$A$!:$B$4000,2,0)

the table_array is in bold with $A$ highlighted.

Am I doing something wrong?

Thank you!
 
Hi Gloria

You can details on the very popular VLOOKUP Function here:
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

You will note that in my examples I use TRUE, FALSE or nothing (which is
TRUE by default) as the last argument (range_lookup). Frank has used 0
(zero) which in Boolean language is FALSE. Any other value, other than
zero, has a boolean value of TRUE. Boolean is simply TRUE or FALSE.

Here is the text from the MS Excel help

If range_lookup is TRUE, the values in the first column of table_array
must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z,
FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If
range_lookup is FALSE, table_array does not need to be sorted.





***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Dave said:
Hey Frank

Ironic isn't it? It was only the other day you corrected mine on
another VLOOKUP.
Click to expand...

:-)
It is
Wish you a nice day (or is it already evening for you)
Frank
 
Back
Top