Finding text in column B, then copying corresponding value from column A into new sheet

Originally Posted by antibudger

I have multiple sheets in my workbook. In sheet 2, I would like to identify a text name in column B, and if found I would like to copy the value from its corresponding row in column A to sheet 1 column B when the text is identified in sheet 1 column A. In sheet 1, the text will only appear once. In sheet 2, the text will likely occur many times.

Andrea,

If by identify you mean select:

Please Login or Register  to view this content.

If by identify you mean something else, post back.

Or you could just use a formula in column B of Sheet1 that will pull the last (lowest down the sheet) entry from Sheet2, base on the value in A2 - this needs to be array-entered (enter using Ctrl-Shift-Enter) and skip the macro altogether.

Formula:

Please Login or Register  to view this content.

If I understand correctly, you want to search for a value in sheet2, column B, and write the same line column A value to sheet1. I have based the code on sheet names, not ordinal position, and at the moment it would prompt for the search value. It also checks sheet1 to write to the next available blank line.

This may work for you:

Please Login or Register  to view this content.

That is different, and should not use a macro:

Select the table of Breed vs Disease, and from the insert menu, choose Pivot table.

Select Breed and Disease as row labels and you will get a nice headed list of Breed followed by a list of their diseases, and if you move Disease above Breed, a headed list of Diseases followed by the breeds affected, like so:

PT.jpg

example.xlsx

Hi Bernie & Arkadi -

I don't think the Pivot Table will work because I have a lot of columns in sheet 1, in addition to disease. I have symptoms, medications, etc... and want to list breed predispositions in one of the columns. I also am trying to list all breeds (which may range from 0 to 210) predisposed to said disease into 1 cell on sheet 1, which would correspond to that disease. So the disease is the primary identifier in sheet 1, but the breed is the primary identifier in sheet 2, and I am trying to list all the breeds in sheet in a separate column. I have made a small example - hopefully it uploads correctly.

It uploaded correctly, but does not have all the information that you described and that would be need for a solution. Why not post the entire workbook?

And what cell do you want the list in? in a new blank column next to the disease name? Would you want a drop down on the cell, or have all the breeds for that disease shown as a list in the cell? Or a comment that would pop up when you select the cell with the disease name?

Oh, you don't want the entire workbook - way too much raw data, and it is a legal document so I can't "legally" share it. Did you see both sheet 1 and sheet 2 in the example?

Dear Arkadi,

I am not able to get your macro to work, it keeps sending the 'not found' error. Am I correct in thinking that if a concantenate function were placed during the search, it would place all breeds into one cell?

Thank you for your help!

I only saw Sheet1 with diseases and symptoms. Sheet2 and Sheet3 were entirely blank. And tell me where you want the cell with the breed list....

I will try to attach the full version - thanks for being patient with me.

Example.xlsx

That is the same file - only one sheet, with diseases and symptoms but no breed information.

Example_sheet2.xlsxGood morning!
I am not sure why it isn't loading the second sheet. I will save that as an individual file and see if that provides the information...

None of those diseases listed are found on the first sheet. Will that be typical? Or is there always a match? (Acne, kidney disease...?)

Book2.xlsx

Hi Bernie,
I apologize for the difficulty in loading the excel sheet - it isn't saving it as an excel sheet for some reason but rather as a word file. Is this working now?

Try this file.

THAT WORKED!!!!! THANK YOU SO VERY, VERY, VERY MUCH!!!!!!!!!!!!!!!!!!!!! I am so grateful to you for your help. Can I pay you somehow for your help?