To Whom It May Concern: I'm trying 1

[kanin247]

To Whom It May Concern:
I'm trying to combine/read from two text files in excel. I have already imported one text file (listings.txt) which contains general information into excel. I want the other text file (failuretype.txt) which lists the detailed description of each failure types to be read into (i.e., added to) the spreadsheet containing listings.txt according to the specified failure type. So, ultimately, the two spreadsheets will be combined.

For clarity, the files look like the following:

listings.txt
Machine Name ID Voltage Type
Berkeley G120 150 Failure 3
Bamberg Q890 350 Failure 10
Newberry P076 250 Failure 75

failuretype.txt
Type Part Part Number
Failure 1 Plastic P23823
Failure 2 Glass G82391
Failure 3 Screw S72489
... ...
Failure 10 Fuse F32719
... ...
Failure 75 Wire W23932

final spreadsheet should look like
Machine Name ID Voltage Type Part Part Number
Berkeley G120 150 Failure 3 Screw S72489
Bamberg Q890 350 Failure 10 Fuse F32719
Newberry P076 250 Failure 75 Wire W23932

The Type field from the listings.txt file will be hidden so it will just be used as a reference since the failuretype.txt file already has the necessary heading.

So, if anyone could provide me with any assistance, I would greatly appreciate it. Thank you.

kanin

 

[Zelandakh]

Open listings.txt in Excel. Save as Excel. On a new sheet, copy the failuretype.txt into it. Name it as Listing.

Back on listings worksheet, in column E set each row as vlookup(d2,Listing,2) and in column F put vlookup(d2,listing,3)

Then copy those down the page

 

[kanin247]

Okay, well I'm having a little trouble. I opened/saved the listings.txt file into excel. Then on a new sheet (within the same listings.xls file -> Sheet2 (renamed to failures) I put the failuretype.txt file by doing the following in Excel2000: Data->Get External Data->Import Text File... Then back on Sheet1 which contains the listings.txt file I did the vlookup command typing: vlookup(D2,failures,2) but I am getting a #name error. It doesn't seem to like the failures part of the command. Was I suppose to import the failuretypes.txt file into a NEW spreadsheet or is this the method you mentioned? Also, what could I be doing wrong?

Thanks again for your help.

kanin

 

[bearjam]

Hi Karin247
Your failures mmust be a range. To do an exact match using vlookup(love it)...=vlookup(type3,(the range of type,partpartnumber),2(being the column you want to return,your matched column would be 1,part would be 2 etc),false)
false denotes a exact match.......very NB!!!!!!!!!.
Just check it as if U R looking up in the same workbook you have to anchor your range with $.

 

[bearjam]

Email me a snippet of the two files and I'll do it and return it to you :150395@absa.freemail.co.za

 

[Zelandakh]

yeah, sounds like you haven't named the failures data as a named range called failures.

 

[kanin247]

bearjam,
well, i tried sending you the files via your specified e-mail address but for some reason it could not/would not send it out. so, i have included a sample of my text files here. you can just copy each into a notepad document and save accordingly. thanks again for your time and effort in trying to provide me some assistance.

kanin

the contents of the files (in particular, the columns) should all be lined up evenly but when i post it here the spacing is all messed up, so you can adjust them accordingly)

listings.txt
NAME ID VLT TYPE
Berkeley G120 150 Failure 3
Bamberg Q890 350 Failure 10
Newberry P076 250 Failure 7
Sumter A235 100 Failure 8
Chester D479 100 Failure 2

failuretypes.txt
NAME PART SERIAL
Failure 1 Case C24892
Failure 2 Monitor M48232
Failure 3 Screw S82423
Failure 4 Fuse F23981
Failure 5 Wire W29023
Failure 6 Diskette D82301
Failure 7 Drive D23013
Failure 8 Speaker S89290
Failure 9 Memory M29382
Failure 10 Bus B23902

 

[kanin247]

Okay,
Finally got it. Thanks to everyone for all your efforts and time. I appreciate it.

kanin