• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

compare columns in separate sheets on match copy cell value

M

mdavid

Member
Hi,
I need to compare the value of Sheet1 C with Sheet2 B and for each match copy the value from Sheet1 A to Sheet2 A as in the example below:
Basically I need to correct the value in Sheet2 A

Sheet1.................................Sheet2..........................Sheet2 - output
A............C ..........................A............B....................A.............B
20..........pear.......................10...........apple..............10...........apple
50..........cherry....................10...........pear................20...........pear
10..........apple.....................10...........pear................20...........pear
30..........orange...................20...........plum...............40...........plum
40..........plum......................30..........plum...............40............plum
..........................................40...........cherry.............50...........cherry
..........................................40...........orange............30...........orange
.
Not sure if I need VBA or a Formula - if it's a formula please could you specify exactly how and where to write it so that it will apply to all of column A in Sheet2.

Thanks for any help
David
 
Rather than lay out the problem in text, create an actual excel file and post it. Then anyone wishing to propose a solution doesn't need to type in all the data. You'll get more and better answers by doing so, Also, on your worksheet explain what you expect the answers to be.
 
Hi David,
Thanks - late night not thinking logically, here's the excel version.
As I said I need full explanation how to apply formula
 

Attachments

  • mdavid1.xlsx
    9.4 KB · Views: 5
OK - well it's late here now and I need to go to bed, but somewhere in the world, especially on the Indian Sub-Continent a Ninja is stirring, seeking today's challenge .... You should have some ideas soon.
 
Hi,
Thanks for this, when I past it into sheet2 A2, I'm informed there's a problem with this formula and the inner () are highlighted

=INDEX(Sheet1!A$2:A$6,MATCH(B2,Sheet1!B$2:B$6,0))

Can be I'm doing something wrong - new to this, what do you mean by "formula copied down"?
 
mdavid said:
Hi,
Thanks for this, when I past it into sheet2 A2, I'm informed there's a problem with this formula and the inner () are highlighted

=INDEX(Sheet1!A$2:A$6,MATCH(B2,Sheet1!B$2:B$6,0))

Can be I'm doing something wrong - new to this, what do you mean by "formula copied down"?
Click to expand...

I copied & pasted @bosco_yip 's formula and it worked fine for me. Why don't you post your file with the offending formula?
 
Thanks David, @bosco_yip
It was my problem, I'd changed the "," to "|" as a list separator in settings 'cause of a problem with a csv file, when I changed it back works as expected.
As a matter of interest what would the vba version of this be - are formulas a subset of vba?
Thanks for your help
 
Hi, Sorry I'm having problems with the real sheets I'm working on test2.xlsx.
I've changed the formula range to match these sheets - I think

=INDEX(Sheet1!A$2:A$365,MATCH(B2,Sheet1!B$2:B$365,0))

but I have alot of cells that contain #N/A and sheet2 A from row 199 to end was empty and stayed empty after the formula was applied. All the values in column B exist in both sheets, so why aren't all the values in sheet2!A2:A514 filled?
 

Attachments

  • test2.xlsx
    82 KB · Views: 4
I understand the problem is that the letter case does not match.
Can I ignore case? or how do I match different case?
Thanks for any help
David
 
You have a few problems in your Tables, one of which is the case of the letter. Another problem is that you have trailing spaces in your descriptions, and also occasionally, trailing spaces in your Lookup Table when you don't have them in your data ...
You can get around some of these problems by incorporating TRIM and PROPER into you INDEX/MATCH formula e.g. in A11 the formula is as follows
=INDEX(Sheet1!A$2:A$365,MATCH(TRIM(PROPER(B11)),Sheet1!B$2:B$365,0))

PROPER() causes the first letter of each word to be capitalized. Trim removes extra spaces. The other issue you appear to have is some descriptions have a trailing "." others do not .....

As an example I've created a Table on Sheet 1 which I've renamed to LOOKUP TABLE. In that table I've added a column which "trims" your descriptions of excess spaces - I've named that "Pruned Species" ;).

There are still #N/As appearing - some of these are just down to things such as
Ficus carica L. (Moraceae)
in one table trying to lookup
Ficus Carica L.

(The formula can overcome the lower case "c" it cannot discern the lack of
"(Moraceae)"

Hate to break the news, but this is down to scrappy data entry in the main. The solution is good old fashioned rolling up your sleeves and iteration o_O

Use the search and filter capabilities on LOOKUP TABLE to isolate the errors and fix them.

Hope this goes some way to solving your problems
 

Attachments

  • Species - DME.xlsx
    100.5 KB · Views: 3
Hi David,
Thanks for your time and patience, much appreciated.
A combination of my Excelling ignorance and many people contributing to this project has made it a bit of a mess.
Would it be possible to use your INDEX/MATCH formula on only the 1st 2 words of the Species names (which is the name and genus I believe) to reduce #N/A.

Thanks for any help
David
 
Hi ,

See the attached file , where a few helper columns have formulae in them. See if you can make use of the results of these.

SEARCH is better than MATCH since it is case insensitive and will find a match anywhere in the text , whereas MATCH will find a match only in the left-most characters unless you use wild-cards.

The result of the MATCH is also shown.

Narayan
 

Attachments

  • test2.xlsx
    191.8 KB · Views: 3
Thanks Narayank, I'll check all this a little later.
I was also thinking of using VBA and .FIND to compare the 2 ranges, what do you think?
 
You must log in or register to reply here.
Back
Top