Importing, matching?

  • Thread starter Thread starter dm brown
  • Start date Start date
D

dm brown

In excel 97...
I have a worksheet with about 1000 unique names (column a), each with a
unique number in a seperate column (column b). Is there a quick way to
associate name and number so when the name is used in another column
and row, say column x, it's associated number automatically appears on
the same row in column y?

2nd question, on the same worksheet, I get tab delimited text file
updates which have the unique number to identify which name the data
belongs to. Is there a way to import this file so the data goes to the
right name/row. Ie, the worksheet has 1000 names, but the txt file
would only list whats being updated for maybe 100 random names.

Thanks for any help.
 
First answer:

Y1: =VLOOKUP(X1, A:B, 2, FALSE)

will return the value in column B corresponding the the cell in column A
that matches the value in X1.
 
Hi brown...

ok, for question 1, lets say you write the name you want in cell D1, then you can use the formula in cell E1 : "=LOOKUP(D1;A:A;B:B)" (using this formula requires that colum A is sorted ascending to the formula works properly) or you can use the formula : "=SUMPRODUCT((A:A=D1)*B:B)" then you dont have to get colum A sorted.

for question 2, you could use a macro to import then update the datas of the names... or you can import the txt to another worksheet and then use a similar formula to question 1 to update the datas.

regards, Ale.
 
Hi dm,

For the first question. Say you enter the name in X1. In
Y1 enter one of these formulas:

=VLOOKUP(X1,A1:B1000,2,0)

=INDEX(B1:B1000,MATCH(X1,A1:A1000,0))

Sorry, can't help with the second question.

Biff
 
Back
Top