Excel Help

[Verdandi]

I know there's lots of Excel adepts out there, so I'm hoping someone can answer this!

I have an Excel file with two worksheets. One of the worksheets contains, among other things, a column of numbers which represent zones. The other has two columns: one with the same numbers and one with the corresponding text names of the zones. Can Excel change the numbers of the zones in the first worksheet to the names they correspond to in the second worksheet? It seems like the sort of thing it ought to be able to do, but I can't figure out how.

If I'll need a macro, I might need more help than it's worth. I've only dabbled with them, and in this case can't get it to go down the list in the second worksheet. It wants to use just the cells I used when I recorded it.

[Itek]

far from an Excell guru (that's more TheWife) ....

But here's what i would try ...

slap all of sheet one into sheet three (or a new book)

MOVE the "zone numbers column" to the last column on the right

(I'm presuming they are in ascending / descending numerical order, with no skips, already... if not... this would be the time for SORT button)

ADD the two column from the second worksheet (with the two number columns next to each other and the names column now the last column on the right.

Check that the two number columns match up and zone names are correctly aligned with the numbers.

Delete one or more numbers column and re-arrange as need where columns go. (e.g. move zone names to the far left column)

As I said, that's just what I would try first to see if the "almost brute force" method would work well enough.

[Twistagain]

I know there's lots of Excel adepts out there, so I'm hoping someone can answer this!

I have an Excel file with two worksheets. One of the worksheets contains, among other things, a column of numbers which represent zones. The other has two columns: one with the same numbers and one with the corresponding text names of the zones. Can Excel change the numbers of the zones in the first worksheet to the names they correspond to in the second worksheet? It seems like the sort of thing it ought to be able to do, but I can't figure out how.

If I'll need a macro, I might need more help than it's worth. I've only dabbled with them, and in this case can't get it to go down the list in the second worksheet. It wants to use just the cells I used when I recorded it.

Sure thing. Let's assume your data on sheet one looks like this (with the zone number in column A):

Column A   Column B
1          Roots
1          Pod of Water
2          Roots
3          Pod of Water

And that your second sheet looks like this:

Column A   Column B
1          Plane of Knowledge
2          Kaladim
3          Plane of Time

Simply add another column to the first sheet (let's say column c) and use the "VLOOKUP" formula.

The syntax is:

=VLOOKUP({SEARCH STRING},{RANGE},{COLUMN TO RETURN},{EXACT MATCH?})

where SEARCH STRING is what to search for, RANGE is where to search for it, COLUMN TO RETURN determines which column in the RANGE to return as the result, and EXACT MATCH is true/false determining whether to go for closest or exact (FALSE is exact, btw.) The RANGE must have the searched column in the left-most column and must contain at least the number of columns needed to get your returned value. For instance, if you used a RANGE of "A:A" and then used a COLUMN TO RETURN of 2, you'd get an error...since your range is only 1 column wide.

So, in this case, you'd want (in c1...then copy down):

=VLOOKUP(A1,{The name of the 2nd sheet}!A:B,2,FALSE)

which would search for the value in A1 (a value of 1) in the A column of the 2nd sheet and return the value in the 2nd (B) column of that sheet for the first row that exactly matches. In this case, it would return "Plane of Knowledge".

Your sheet would then look like this:

Column A   Column B       Column C
1          Roots          Plane of Knowledge
1          Pod of Water   Plane of Knowledge
2          Roots          Kaladim
3          Pod of Water   Plane of Time

[Ngreth Thergn]

Sure thing. Let's assume your data on sheet one looks like this (with the zone number in column A):

Column A   Column B
1          Roots
1          Pod of Water
2          Roots
3          Pod of Water

And that your second sheet looks like this:

Column A   Column B
1          Plane of Knowledge
2          Kaladim
3          Plane of Time

Simply add another column to the first sheet (let's say column c) and use the "VLOOKUP" formula.

The syntax is:

=VLOOKUP({SEARCH STRING},{RANGE},{COLUMN TO RETURN},{EXACT MATCH?})

where SEARCH STRING is what to search for, RANGE is where to search for it, COLUMN TO RETURN determines which column in the RANGE to return as the result, and EXACT MATCH is true/false determining whether to go for closest or exact (FALSE is exact, btw.) The RANGE must have the searched column in the left-most column and must contain at least the number of columns needed to get your returned value. For instance, if you used a RANGE of "A:A" and then used a COLUMN TO RETURN of 2, you'd get an error...since your range is only 1 column wide.

So, in this case, you'd want (in c1...then copy down):

=VLOOKUP(A1,{The name of the 2nd sheet}!A:B,2,FALSE)

which would search for the value in A1 (a value of 1) in the A column of the 2nd sheet and return the value in the 2nd (B) column of that sheet for the first row that exactly matches. In this case, it would return "Plane of Knowledge".

Your sheet would then look like this:

Column A   Column B       Column C
1          Roots          Plane of Knowledge
1          Pod of Water   Plane of Knowledge
2          Roots          Kaladim
3          Pod of Water   Plane of Time

I use vlookup extensively

you can also do this without having to type much out

go to your column C put the cursor in the first row.
type =vloookup(
click on first row colum A (A2 if you have a title row) this will automatically put in the cell reference.
type ,
click on the second sheet
Hold down the mouse and drag it over the zone number zone name reference table. This will automatically add your "range". You con do this specifying only the table, or by draging on across the excell headers for the row which will take the whole row.
Hit F4 (before you hit comma) this will tell excell to "lock" that range (this will come into effect later)
Hit ,2,1)<enter> this will finish off the vlookup (making it check the second column, and forcing it to find matches... it will give N/A in a cell if it cannot find a match)

this will give you
Now that you have done the hold down the mouse on column C, and drag down tot he end of your table.

Hit CTRL-D this will copy the formula down so you do not have to keep typing that out. the "lock" makes sure that while excell will automatically change the search string... the "range" is locked becuase of the $'s you will see in the vlookup.

=VLOOKUP(A1,{The name of the 2nd sheet}!A$:B$,2,FALSE)

I this particular case it was not important... but say you had a small portion of a table...

=VLOOKUP(A1,{The name of the 2nd sheet}!A$1$:B$5$,2,FALSE)

if you copied down without the "lock" it would also change your range in the copy down, giving you data you do not want.

[Verdandi]

Woot, it works! That is a HUGE help, thank you Twist and Ngreth!

(And thanks to you, too, Itek! As in Twist's example, I have multiple entries for each zone number though, so just a raw matching wouldn't have worked. But I appreciate the attempt!)

Ahhh, a well-formatted and versatile spreadsheet is a thing of beauty. *tries to refrain from stroking her screen admiringly*

With any luck, this project will ultimately result in more accurate vendor supply information, particularly for those ingredients that have been added to EQ since the database was first created. Hopefully . . ..

[Twistagain]

No problem. Glad it worked for ya. I use VLOOKUP all the time (and sometimes HLOOKUP...the horizontal equivalent.) I don't want to try to imagine some of my work without them....well, actually, I'd just pop everything in a SQL DB and run a query...but this is generally easier.

[euridice]

vlookup is crap and dangerous
use instead INDEX({COLUMN TO RETURN},MATCH({SEARCH STRING},{RANGE},0))
vlookup is not case sensitive and there are several know bugs I don't remember exactly.
INDEX(xxx,MATCH(y,zzz,0)) is the way to go, much more robust and flexible (works for columns and rows)

[Ngreth Thergn]

vlookup is crap and dangerous
use instead INDEX({COLUMN TO RETURN},MATCH({SEARCH STRING},{RANGE},0))
vlookup is not case sensitive and there are several know bugs I don't remember exactly.
INDEX(xxx,MATCH(y,zzz,0)) is the way to go, much more robust and flexible (works for columns and rows)

I always forget the index syntax

and I am usually working with numbers like she is... so case sensitivity is not an issue

[Itek]

*Itek is revealed as a n00b*

Glad you found a way, and now I have at least two more things to learn about in Excel.

(Would rather program in Java than work in Office Suite. And I've previously been quoted as saying "I'd rather gnaw my own arm OFF and _Beat_Myself_To_Death_With_It_ than learn any more Java language then I already know.")