1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

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


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

Search every cell value from one column in another column and return output in desire columns

Discussion in 'VBA Macros' started by paneliyadhruv, Feb 14, 2019 at 7:49 AM.

  1. paneliyadhruv

    paneliyadhruv Member

    Dear All,

    Kindly help me to solve below mention problem.
    In column A- master code from A2 to A
    In column B- master list of fruit from B2 to B
    In column C - list of fruits based on user entry

    Procedure - Search column B name in column C, if name of that fruit present in column C then copy name in column E2 and master code of that fruit in column F. So on for searching of all cells of column B in column C and return values in E and F.

    Thanking you in advance.

    Attached Files:

    Last edited: Feb 14, 2019 at 8:10 AM
  2. vletm

    vletm Excel Ninja

    I won't use term 'problem'.
    But did You try to solve something like this?
    > Press [ Do It ]-button in 'D1'-cell

    Attached Files:

  3. Fluff13

    Fluff13 New Member

    Another option
    Code (vb):
    Sub paneliyadhruv()
      Dim Cl As Range
      With CreateObject("scripting.dictionary")
          .CompareMode = 1
          For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
            If Not Cl.Value = "" Then .item(Cl.Value) = Empty
          Next Cl
          For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If .Exists(Cl.Value) Then .item(Cl.Value) = Cl.Offset(, -1).Value
          Next Cl
          Range("E2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
      End With
    End Sub
  4. paneliyadhruv

    paneliyadhruv Member

    Thank you very much sir.
  5. Fluff13

    Fluff13 New Member

    You're welcome & thanks for the feedback
  6. paneliyadhruv

    paneliyadhruv Member

    Dear Vletm and Fluff13,

    Thank you very much for your time and effort. Both codes working perfectly.

Share This Page