Excel: Find and replace from list

2013-04-05 11:58:23

#1

Dauntless: Admin; +2,249|6959|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:

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

2013-04-05 11:59:06

#2

Uzique The Lesser: Banned; +382|4472

you should PM roc he's an expert bean-counter and went to college especially to become an excel whizz

2013-04-05 12:01:09

#3

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:04:39

#4

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:05:43

#5

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:13:55

#6

Dauntless: Admin; +2,249|6959|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

2013-04-05 12:17:22

#7

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:17:48

#8

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|949

dauntless send me the file and I will do it for you in less than 2 mins

2013-04-05 12:25:46

#9

Uzique The Lesser: Banned; +382|4472

dauntless keeps all of his insider-dealings with the bidet industry under strict confidentiality

2013-04-05 12:30:02

#10

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|949

Jay wrote:
=VLOOKUP(target,A2:B20,2,false)

Fixed it for you

2013-04-05 12:31:32

#11

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:33:28

#12

Dauntless: Admin; +2,249|6959|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

2013-04-05 12:33:39

#13

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|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

2013-04-05 12:33:57

#14

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|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

2013-04-05 12:34:48

#15

13/f/taiwan: Member; +940|5916

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)

2013-04-05 12:38:32

#16

Dauntless: Admin; +2,249|6959|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

2013-04-05 12:39:40

#17

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|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.

2013-04-05 12:41:51

#18

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:41:59

#19

Dauntless: Admin; +2,249|6959|London

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

anyway i pmed you the sheet and the NDA

2013-04-05 12:43:17

#20

Dauntless: Admin; +2,249|6959|London

Jay wrote:
Does european excel use $'s?

lol, yeah it does

would be pretty funny if we changed it to the £ or € tho

2013-04-05 12:47:27

#21

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 12:52:38

#22

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|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!

2013-04-05 12:54:48

#23

Dauntless: Admin; +2,249|6959|London

ah yeah got it

thanks m8 i owe you one

2013-04-05 12:55:54

#24

Jay: Bork! Bork! Bork!; +2,006|5575|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

2013-04-05 13:01:48

#25

KEN-JENNINGS: I am all that is MOD!; +2,978|6849|949

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

BF2S Forums Excel: Find and replace from list

Jay wrote:

KEN-JENNINGS wrote:

Jay wrote:

Jay wrote:

KEN-JENNINGS wrote:

Jay wrote:

Dauntless wrote:

Uzique The Lesser wrote:

Jay wrote:

Board footer