Combining data from two text files

amersh

New Member
Joined
Feb 26, 2005
Messages
1
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fujirich

Active Member
Joined
May 1, 2003
Messages
320
There's a lot of options to what you want to do, but here's one way based on entering the ID number:
Book1
ABCDE
3
4IDPrice
520050
621045
730056
840077
9
10
11IDDescription
12200General Stock
13210Normal Stock
14300General Stock
15400New Stock
16500Misc
17
18
19
20DescriptionIDPrice
21General Stock20050
22Normal Stock21045
23General Stock30056
24New Stock40077
25Misc500#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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top