Dauntless
Admin
+2,249|6712|London

I think this is probably pretty simple but I can't find a clear example of how to do this

I have a list with two columns and about about 75 rows

looks like this:

https://i.imgur.com/wTL95zt.png

I have another sheet with about 5000 rows, I want something to find the number on the left (above) and replace it with the number on the right.
So 5 would become 2 and 10 would become 8 etc.

One thing I need to keep in mind is that I need I would need all values replaced at the same time, because if it did it one after the other then all of the 1's would become 2's and then those 2's would turn into 3's


Does that make sense?

If you help me with this i'll give you a karma and i'll owe you one

and i always pay the ones i owe
https://imgur.com/kXTNQ8D.png
Uzique The Lesser
Banned
+382|4224
you should PM roc he's an expert bean-counter and went to college especially to become an excel whizz
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
=VLOOKUP(target,A2:B20,2,)
"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
You would have to create another column next to the one you want to match and it will spit out the number you want. So, you say you have 5000 rows, lets call that column A on sheet 1. In column B on sheet 1 you put the above formula using A1, A2, A3 etc instead of 'target' with the array call 'A2:B20' going to your reference sheet that you're showing in the OP.
"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
Or you could just use 'replace'
"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Dauntless
Admin
+2,249|6712|London

ah yeh gotcha

i had a look at replace but i couldn't get it to work

this vlookup seems to be the ticket
https://imgur.com/kXTNQ8D.png
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
vlookup is the best excel function ever.
"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

dauntless send me the file and I will do it for you in less than 2 mins
Uzique The Lesser
Banned
+382|4224
dauntless keeps all of his insider-dealings with the bidet industry under strict confidentiality
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

Jay wrote:

=VLOOKUP(target,A2:B20,2,false)
Fixed it for you
Jay
Bork! Bork! Bork!
+2,006|5328|London, England

KEN-JENNINGS wrote:

Jay wrote:

=VLOOKUP(target,A2:B20,2,false)
Fixed it for you
Nevermind. Brain fart.

true or false would work for him though, he's not dealing in fractions.

Last edited by Jay (2013-04-05 12:33:27)

"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Dauntless
Admin
+2,249|6712|London

this is almost working but i'm getting an #N/A on a some of the values...

I need to leave soon, i'll have to take a look at it tomorrow

thanks for the offer ken, i might take you up on it tomorrow
https://imgur.com/kXTNQ8D.png
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

Jay wrote:

KEN-JENNINGS wrote:

Jay wrote:

=VLOOKUP(target,A2:B20,2,false)
Fixed it for you
Nevermind. Brain fart.

true or false would work for him though, he's not dealing in fractions.
exact match = false

closest match = true

I always default to 'false' just by habit
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

Dauntless wrote:

this is almost working but i'm getting an #N/A on a some of the values...

I need to leave soon, i'll have to take a look at it tomorrow

thanks for the offer ken, i might take you up on it tomorrow
seriously, i can have it to you in less than 2 mins
13/f/taiwan
Member
+940|5668

Uzique The Lesser wrote:

dauntless keeps all of his insider-dealings with the bidet industry under strict confidentiality
i remember seeing dauntless stay up until the morning on many occasions while he had his teamspeak going. whenever i would ask him why he stayed up that long he would nonchalantly reply with: "business"



https://upload.wikimedia.org/wikipedia/en/2/2a/The_Wire_The_Greek.jpg

Last edited by 13/f/taiwan (2013-04-05 12:37:42)

Dauntless
Admin
+2,249|6712|London

i've literally never replied with "business"

i hearby request a delete to all posts that are non pertinent to the excel matter at hand
https://imgur.com/kXTNQ8D.png
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

dauntless, as a fine upstanding and well-respected member of the community it is with my utmost displeasure and disgust that I have to deny your request.
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
Does european excel use $'s?
"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Dauntless
Admin
+2,249|6712|London

wait till tucker hears about this! she'll have your head

anyway i pmed you the sheet and the NDA
https://imgur.com/kXTNQ8D.png
Dauntless
Admin
+2,249|6712|London

Jay wrote:

Does european excel use $'s?
lol, yeah it does

would be pretty funny if we changed it to the £ or € tho
https://imgur.com/kXTNQ8D.png
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
I am mildly surprised that it's on your keyboards tbh
"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

sorry, i was on a call.  it's done now.  you want to select the whole columns (A:B) instead of (A2:B73) because as you drag the formula down, you also drag the range down, meaning any values in the earlier range (for example, four cells down the range would be A6:B79, meaning it wouldn't look up anything in cells A:3-5 and B74-78) would not return anything.  That's why the N/A was happening.  Jay is an excel noobie!
Dauntless
Admin
+2,249|6712|London

ah yeah got it

thanks m8 i owe you one
https://imgur.com/kXTNQ8D.png
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
You just use $ in front of the array calls silly. $A$2:$B$73

Last edited by Jay (2013-04-05 12:56:12)

"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
KEN-JENNINGS
I am all that is MOD!
+2,973|6602|949

extra work adding the dollar signs in to lock the range when you can just click the whole cells.

Board footer

Privacy Policy - © 2024 Jeff Minard