Combining data from two text files

[amersh]

I am using Excel 2000 on windows XP Professional.

I am trying to combine two text files together onto the spreadsheet.

The first text file consists of an item id and price

the second text file consists of the same item id with a general item type description.

e.g.

1st text file

id price
200 50
210 45.00
300 56.00
400 77.00

2nd text file

id type description
200 general stock
210 normal stock
300 general stock
400 new stock
500 Misc

How do I end with a result like


type description id price
general stock 200 50
300 56.00
normal stock 210 45.00
new stock 400 77.00
misc 500

Thanks

 

[Fujirich]

There's a lot of options to what you want to do, but here's one way based on entering the ID number:

Book1
	A	B	C	D	E
3
4	ID	Price
5	200	50
6	210	45
7	300	56
8	400	77
9
10
11	ID	Description
12	200	General Stock
13	210	Normal Stock
14	300	General Stock
15	400	New Stock
16	500	Misc
17
18
19
20		Description	ID	Price
21		General Stock	200	50
22		Normal Stock	210	45
23		General Stock	300	56
24		New Stock	400	77
25		Misc	500	#N/A
26
Sheet1

In this example, the ID number entered in C21:C25 will return the item description and price in the adjcent cells. I'm using simple VLOOKUP's to find the correct data.

Does this help? I'm not sure from your description if this is what you are looking for or not.

 

[jindon]

Hi,
If
id price
200 50
210 45.00
300 56.00
400 77.00 in Sheet1 with headings on 1st row

and
id type description
200 general stock
210 normal stock
300 general stock
400 new stock
500 Misc in Sheet2 with headings on 1st row, then
try

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet, LastR As Long
Dim dic As Object, x, a(), b(), c(), i As Long
Set ws1 = Sheets("sheet1"): Set ws2 = Sheets("sheet2")
Set dic = CreateObject("Scripting.Dictionary")
With ws1
    .Range("d:f").Clear
    .Range("d1") = "type description": .Range("e1") = "id": .Range("f1") = "price"
End With
With ws1.UsedRange
    a = .Resize(.Rows.Count - 1, .Columns.Count).Offset(1).Value
End With
With ws2.UsedRange
    b = .Resize(.Rows.Count - 1, .Columns.Count).Offset(1).Value
End With
For i = LBound(b) To UBound(b)
    If Not dic.Exists(b(i, 2)) Then: dic.Add b(i, 2), Nothing
Next
x = dic.keys
For i = LBound(x) To UBound(x)
    ReDim c(1 To UBound(b), 1 To 3)
    LastR = ws1.Range("e65536").End(xlUp).Row: c(1, 1) = x(i)
    For iii = LBound(b) To UBound(b)
        If x(i) = b(iii, 2) Then: iv = iv + 1: c(iv, 2) = b(iii, 1)
    Next
    For v = LBound(c) To UBound(c)
        For vi = LBound(a) To UBound(a)
            If c(v, 2) = a(vi, 1) Then: vii = vii + 1: c(vii, 3) = a(vi, 2)
        Next
    Next
    ws1.Range("d" & LastR + 1).Resize(UBound(c), UBound(c, 2)).Value = c
    iv = 0: vii = 0: Erase c
Next
Set dic = Nothing: Set ws1 = Nothing: Set ws2 = Nothing
Erase a, b, x
End Sub

rgds,
jindon