Can I create a DB from a spreadsheet?

[jgarite]

Hello again, I have an Excel spreadsheet containing country codes that I would assume needs to be turned into a database in order to be queried. The query would be something like this:
Search for United States (finds it in column B) and replace it with its Country Abbreviation: US (Finds this in column A).

How can I create the file into a database? I'll do some research on the query first before I ask for help with that (unless anyone feels like saving me the time/trouble )

 

[AnotherHiggins]

I haven't been following your other thread, but just looking at this it seems likely that you can do what you need with worksheet formulas without having to use MS Query or Access.

Please provide more detail about that you're trying to accomplish.

For starters, have a look at VLookup function. Since the column you want returned is to the left of the column you're looking up, you will actually use Index along with Match, but it will perform very much like the VLookup.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jgarite]

Ok, well to start, I can move the columns around, I don't think it matters which column is the abbreviation and which column is the full name. Here is what I am trying to do:

I have a file that contains country names that need to be converted to the abbreviations. I will be running this on a daily basis. I currently use a macro to create 1 of 2 files and a query to created the 2nd file. In the original file are the country names (they also get brought into the 1st file created by the macro) that need to be abbreviated. I want to include this into the macro. I was originally thinking of using an IF United States than US type of coding but than realized there are 239 countries in my spreadsheet...I think that would also be a huge lag using VLookUp (I could be wrong).

With that being said, any suggestions?

 

[ck1999]

Sub displayresults()
vcountry = "VANUATU"
With Worksheets(1).Range("a1:b500")
Set c = .Find(vcountry, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Row
End If
abbrev = Cells(firstaddress, 1)
End With


End Sub


could you use something like the above in a loop for each country name

This should be relatively fast

ck1999

 

[AnotherHiggins]

how many records are we talking about? I use VLookup with several thousand records all the time. In fact, as a test I just used both VLookup and Index-Match auto-filling for over 50,000 records and each ran in about 1 second.

I'm using the following Named Ranges in my examples:
CtryAbr = Abbreviations
CtryName = Country Names
LkupTbl = both columns with Name in col A and abbr in col B

With your sheet the way it is currently laid out:
[tab][COLOR=blue white]=INDEX('country-codes.xls'!CtryAbr, MATCH(A2, 'country-codes.xls'!CtryName, 0))
[/color]

Or if you swap columns A & B on the "country-codes" sheet, then
[tab][COLOR=blue white]=VLOOKUP(A2, 'country-codes.xls'!LkupTbl, 2, 0)[/color]

So basically, record a macro where you open the country-codes.xls worksheet, flip back over to your sheet where you need the abbreviations, put in the formula you want to run, replace formulas with values, close country-codes.xls et viola!

Post the generated code and we can help you flush out the code to be more resilient - automatically fill the formula in all populated rows without looping and stuff like that.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Hmm. You solution does look more elegant, ck1999.

But some quick testing shows that your code takes about 18 seconds to run, compared to about 1 second for mine (turning off ScreenUpdating and setting calculation to manual).

That is why I don't like loops.

There's probably a way to use your approach without looping that would be pretty slick, but it's time to head home....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jgarite]

higgins, how would i use this in a macro? the country-codes.xls would be looking in Final Macro Completed.xls (this is where the macro resides) for the data that needs replacing. And how would i loop it to continue through row 2,3,4,etc...there usally arent more than 10 rows so it is a small file im working with. I like the fact of working with a formula, but how does that work? wouldnt i need to create a new column to insert the formula, than have it reference the original country name and have it query the abbreviation in the other file?? I'm confused on that part.

ck1999, i have no idea what any of that does...what would i need to replace? where would the reference be that calls the country-codes.xls file? my macro is in a different file, although I can make it part of the same file by creating a worksheet in the Final Macro Completed.xls file named country codes...would htat work? and if so,, how would i make it reference that?

trying to get both methods so i can than see which one is more efficient with my macro...thanks for your help

 

[ck1999]

Here is a more completed cod

Sub Macro1()
'

   Dim wbk, wbk2 As Workbook
   Dim wbkcountry
   
   Dim vaws
   Set wbk2 = ActiveWorkbook   ' name of workbook
   wbkcountry = "C:\Documents and Settings\Owner\Desktop\country-codes.xls"
   Set wbk = Workbooks.Open(Filename:=wbkcountry)
   
   vaws = "sheet1" ' Sheet name in other file
   For counter = 1 To wbk2.Sheets(vaws).Range("a1").End(xlDown).Row ' replace a1 with first cell in range to countries to replace

    vcountry = wbk2.Sheets(vaws).Range("A" & counter).Value
    With wbk.Worksheets(1).Range("a1:b500")
        Set c = .Find(vcountry, LookIn:=xlValues)
    If Not c Is Nothing Then
        wbk2.Sheets(vaws).Range("a" & counter).Value = Cells(c.Row, 1)
    End If
        
    End With
   Next counter
   wbk.Close
   Set wbk = Nothing
   Set wbk2 = Nothing
   
End Sub

Place this in the worksheet with column of countries to replace name with abbreviations

You should only have to make 3 changes
1. vcountry = location and filename of country-codes.xls
2. where it uses range("a1") Replace a1 with the top cell location in your list

3. vaws set equal to the sheet name of the sheet that has the column of countries you are changing.

ck1999

 

[ck1999]

3 quick questions. I am thinking of a quicker method

1. Sheetname of table where you are replacing country name with abbreviation.
2. Col of country names. And 1st row with data
3. How many rows approximately are in the table? Will it vary?

ck1999

 

[jgarite]

1. ebay
2. i dont have the sheet in front of me (im at home), but I think it is M2
3. it will vary. so far hasnt been more than 10.

 

[ck1999]

How did the code work?

ck1999