Linking Info using Excel or Access 1

[robyno]

I trying to check a large list of values to set the conditional path for a data entry script. This list of values that I have is around 6000 entries. My data entry fields will pass a value, example "1234" and I need to search for the numeric code assigned to "1234" in the excel spreadsheet and bring the value "2" back. Then the next record is read in and the value "2222" needs to be looked up and the numeric code "3" attached to it needs to be returned. The final result can be word , excel or access whatever will work. I prefer to keep the records as fixed but I will make do with any tool.

input record
1 1234
2 2222
3 3232

spreadsheet
1234 2
2222 3


I would like to have a third form (text or spreadsheet) that reflects the original input records with the numeric code from the second sheet concatenated to the input value.

New sheet
1 1234 2
2 2222 3

I am really struggling and would appreciate any way of accomplishing this retrieve and merge of the data. I am brain dead and can't seem to find the right approach.

Thanks in Advance...Robyn

 

[darag2358]

Not exactly getting everything that you are doing, but sounds like you are wanting the VLookup function in Excel? Take a look in the help section in Excel for details on how to use the function.

 

[robyno]

I am trying to get a handle on Vlookup... Here is a sample of what I want. I receive a file on a daily basis of data.

TEST.TXT (fixed record file of accounts and services)
1 123444 MyName 333333 05052002 444444 05052002 444333 050520002
2 324444 MyName2 444444 08082002 344444
3 311111 MyName3 333333 11012002 123333 11012002 444444 11012002

Worksheet A: (List of key codes and values)
123333 C
311111 A
333333 B
344444 C
444444 B
444333 D

Preferred result
1 123444 MyName 333333B 05052002 444444A 05052002 444333D 050520002
2 324444 MyName2 444444A 08082002 344444C 11012002
3 311111 MyName3 333333B 11012002 123333C 11012002 444444A 11012002

I appreciate any and all help since I am under the gun.
Thanks, Robyn

 

[darag2358]

Still not sure about your process, but this should help. I'm assuming that you are opening your Test.txt file in Excel and then separating them in to columns - in your example above, columns A through I. Somewhere to the right, like column J put this formula in J1 and copy down:

=D1&VLOOKUP(D1,WorksheetA!$A$1:$B$6,2,FALSE)

Where D1=333333 in your example and WorksheetA!$A$B6 is the range of values in WorksheetA in your example. The result of the formula will be "333333B" (the concatenation of D1 and the lookup value in the WorksheetA table). You can then copy, paste-special the value back in to your D column to replace the old number. Then just go to the next column, K, and do the same thing except replace with "D1" with "F1". There are more elaborate ways, but this will work.

Hope that makes sense.

 

[robyno]

The Vlookup seems to do the matching that I need but I now I have a problem because the file can not be saved as a Fixed record with the same parameters as before. The blank fields and blank fills all drop when I try to save as fixed record length. The program that I use the file for needs a fixed record to interface to and therefore can not vary in field length and position. I am using Reflections script to mimic a data entry task.