Comparing Cells and if equal then copy different cell to different worksheet

**Joelogic** · 08-31-2010, 06:11 PM

Sorry if this is in the wrong forum and worded incorrectly, just a newb here. I was unsure of what to search for so I am just going to post my question.

I have two worksheets with similar data. I want to compare the cells in one column in worksheet A to a column in worksheet B. If the values are the same then I want to copy a cell from the row with the similar value in worksheet A to a cell in worksheet B

Example workbook. If A1 of worksheet B is equal (fuzzy equal) to A1 of worksheet A then copy E1/F1 from worksheet B to E1/F1 of worksheet A. And then repeat down each row until the end.

Thanks a lot.

**teylyn** · 08-31-2010, 06:56 PM

What do you mean by "fuzzy equal"?

Why a macro?

You can use a simple vlookup like this in E1 of sheet A. Copy to F1 and then down.

=VLOOKUP(LEFT($A1,FIND("#",$A1&"#")-2),B!$A$1:$F$30,COLUMN(E1),FALSE)

**Joelogic** · 08-31-2010, 10:42 PM

Thanks for the quick response.

By fuzzy I mean that the two cells will not be exact. One cell will be 100 Main St. and the other cell will be 100 Main St. #101 or 100 Main St.

Not sure I need a macro.

Wow, that vlookup looks complicated. Could you please please give me a simple break down of what it does so that I can replace the variables.

Basically what I want to do is compare two almost similar cells and if they are the same then add data to that row.

**teylyn** · 08-31-2010, 10:54 PM

If your cells are the same before the # sign, then the formula will work.

It looks up the part before the # sign

LEFT($A1,FIND("#",$A1&"#")-2)

in your sheet B and returns the values for column E or column F respectively.

It worked fine on your data sample. Have you tried it?

**Joelogic** · 09-01-2010, 02:08 AM

I havent tried it yet, but I will first thing in the morning. Thanks again.

**Joelogic** · 09-01-2010, 02:08 PM

That's Awesome. It works great. I appreciate the help. Would you mind helping me break down the formula so that I can change the variables to work with my acutal project.
=VLOOKUP(LEFT($A1,FIND("#",$A1&"#")-2),B!$A$1:$F$30,COLUMN(E1),FALSE)
Is the ($A1, FIND) working on the first or second sheet?
What about the ("#",$A1&"#")?
For the (B!$A1:$F$30) is there a way to make the excel sheet auto enter the last row in place of the $30?
Sorry for all the newb questions I am totally in above my head on this. The extent of my excel use before this was =sum(A1:A30)

**teylyn** · 09-01-2010, 06:01 PM

step through the formula with the Evaluate Formula tool. That will break down the calculation steps

The formula is in sheet A and the Find command is working on A1 in sheet A to arrive at a text to search for in sheet B. Let's break it down:

=VLOOKUP(LEFT($A1,FIND("#",$A1&" #")-2),B!$A$1:$F$30,COLUMN(E1),FALSE)

FIND("#",$A1) returns the position of the # sign in A1. If there is no # sign, it will return an error. To avoid the error, I make sure that the text to search does have a # sign by concatenating it to A1

FIND("#",$A1&" #")

The actual value for the Vlookup ends two characters before the #

FIND("#",$A1&" #")-2 will return the position of the character two to the left of the # sign

LEFT($A1,FIND("#",$A1&" #")-2) will return the leftmost part of A1 up to two characters before the # sign.

For simplicity, let's just call this "SearchText" in the following Vlookup:

=VLOOKUP(SearchText,B!$A$1:$F$30,COLUMN(E1),FALSE)

So Vlookup tries to locate the search text in a table on Sheet B that extends from A1 to F30. If Vlookup finds SearchText in column A, then it will return the value in the fifth column of the table.

Column(E1) returns 5

Copy =column(E1) to the right and it will turn into Column(F1), which returns 6.

Instead of hard coding the column to return, using the Column(cell) method makes it possible to use just one formula and copy it right. The first formula returns the value from the fifth column, and when copied right, the formula returns the value from the sixth column.

If you inspect the formula in this post, you may notice that I also added a space in
FIND("#",$A1&" #")
before the # sign. This is to ensure that the text value is not truncated when cell A1 does not have a # in the first place.

Hope that makes it clearer.

cheers

**nicheller** · 10-19-2011, 12:27 PM

Hey! I'm replying to this thread years later because I have a similar question. I'm trying to compare two cells in Sheet1 to hard data in Sheet3 and when two rows of match, I want an entry from the same row in Sheet3 to populate the row in Sheet1.

See attached.

I would like Cell D1 on the Mileage Sheet to report the information in Column C from the Route Calculations Sheet.

Any thoughts, I've tried imbedding LOOKUP functions in IF functions and haven't gotten the results I need. HELP if possible.

-nic

**teylyn** · 10-19-2011, 06:17 PM

nicheller, welcome to the forum. However, I'm afraid,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Comparing Cells and if equal then copy different cell to different worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Re: Comparing Cells and if equal then copy different cell to different worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions