Two excel docs into one...with a common value

[Martha W. Stone]

I'm a self-taught excel wizard, first or second level. I've got two lists
of information. One list has 15,000 records and the second list has 800
records. The second smaller list has a column that is a code (i.e. L-1234)
that corresponds to the same code in the first, larger list. Both lists
have different information. How can I get the info from the smaller list
into the larger list without manually cutting and pasting?

 

[dvt]

I'm a self-taught excel wizard, first or second level. I've got two
lists of information. One list has 15,000 records and the second
list has 800 records. The second smaller list has a column that is a
code (i.e. L-1234) that corresponds to the same code in the first,
larger list. Both lists have different information. How can I get
the info from the smaller list into the larger list without manually
cutting and pasting?

Have you tried the LOOKUP functions, VLOOKUP in particular? I'd wager
that's what you need. Post more details of your list and I may be able to
make a more explicit suggestion.

Dave
dvt at psu dot edu

 

[Martha W. Stone]

I have a one list (15,000 records) of names and addresses. I have a second
list (800 records) that tells me wealth information on certain individuals.
Each list has a "list sequence" code. So, John Smith's record on the big
list has the same code as his record on the small list. I want to put both
lists together so I can see John Smith's address as well as his wealth
information on one row.

 

[dvt]

I have a one list (15,000 records) of names and addresses. I have a
second list (800 records) that tells me wealth information on certain
individuals. Each list has a "list sequence" code. So, John Smith's
record on the big list has the same code as his record on the small
list. I want to put both lists together so I can see John Smith's
address as well as his wealth information on one row.

Sounds like VLOOKUP is the answer for you. Here's a quick example.

Table 1:
(A1)John Smith (B1)L-1234 (C1)123 Anystreet, Seattle, WA

Table 2:
(A1000)John Smith (B1000)L-1234 (C1000)$1200

In cell D1 (next to table 1), enter this formula:
=VLOOKUP(B1,B1000:C2000,2)

That looks for the value of B1 (L-1234) in the first column of the range
B1000:C2000 and returns the value that is in the second column said range,
which is 1200 in this case.

Dave
dvt at psu dot edu

 

[Martha W. Stone]

I'm very confused about how to do this between two different excel
spreadsheets. Do I need to put the small one in a new worksheet in the same
document as the large file?

 

[dvt]

I'm very confused about how to do this between two different excel
spreadsheets. Do I need to put the small one in a new worksheet in
the same document as the large file?

No, not necessarily. I've modified the example below.

Table 1 in Sheet1 of file1.xls:
(A1)John Smith (B1)L-1234 (C1)123 Anystreet, Seattle, WA

Table 2 in Sheet1 of file2.xls:
(A1)John Smith (B1)L-1234 (C1)$1200

In cell D1, Sheet 1, file1.xls (next to table 1), enter this formula:
=VLOOKUP(B1,'[file2.xls]Sheet1'!$B$1:$C$5,2)

If you only need to look at a different sheet within the same Excel file,
you can get rid of the [file2.xls] part.

Dave
dvt at psu dot edu

 

[Martha W. Stone]

Dave, I appreciate your willingness to help me with this! I still haven't
gotten it working, but I'm playing with it!