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

[antibudger]

Hello!

I would greatly appreciate guidance in trying to solve a problem that I anticipate will be occurring frequently in my huge data sheet.

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.

I will try to clarify by providing the example:

In sheet 1, I have "diseases" listed alphabetically in column A. In sheet 2, I have breeds of dogs listed alphabetically in column A and diseases that they are predisposed to listed in column B. I would like identify all breeds in sheet 2 that have a disease, and list all those breeds in sheet 1 under column B of the corresponding disease. I hope that makes more sense.

I hope this makes sense and is feasible to program? I thank you in advance for any help!


Andrea

[Bernie Deitrick]

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:

Sub test()
Dim lngR As Long
lngR = Application.Match(ActiveCell.Value, Sheets("Sheet1").Range("A:A"), False)
Sheets("Sheet1").Range("B:B").Cells(lngR).Value = ActiveCell.Offset(0, -1).Value
End Sub

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.

=INDEX(Sheet2!$A$1:$A$1000,MAX(IF(Sheet2!$B$1:$B$1000=Sheet1!A2,ROW($A$1:$A$1000))))

[Arkadi]

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:

Sub vlook()
Dim i, last_read_row, writerow As Integer
Dim target As String
target = InputBox("Please enter the text you wish to find") 'to get the search value
last_read_row = Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row ' last row with data on sheet2
writerow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1 'finds last row with data on sheet1 and will start writing at next cell.  Only bad thing, it will not use A1 but start at A2

For i = 1 To last_read_row
    If CStr(Worksheets("Sheet2").Cells(i, 2).Value) = target Then
        Worksheets("Sheet1").Cells(writerow, 1).Value = Worksheets("Sheet2").Cells(i, 1).Value 'copy value to sheet2 if match found
        GoTo found 'stop checking after value found
    End If
Next i
MsgBox ("Sorry could not find the search value.")
found:
End Sub

[Bernie Deitrick]

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

[antibudger]

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.

[Bernie Deitrick]

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?

[antibudger]

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!

[antibudger]

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?

[Bernie Deitrick]

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....

[antibudger]

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

Example.xlsx

[Bernie Deitrick]

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

[antibudger]

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...

[Bernie Deitrick]

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

[antibudger]

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?

[Bernie Deitrick]

Try this file.

[antibudger]

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?

[Bernie Deitrick]

THAT WORKED!!!!! THANK YOU SO VERY, VERY, VERY MUCH!!!!!!!!!!!!!!!!!!!!!

Great!

Can I pay you somehow for your help?

No - but thanks for thinking that way