Writing a Macro that looks to a value in a cell and returns a list of items that correspond to that value?

Hi there, I'm working on writing a Macro in excel (don't have any VBA experience). I'm hoping to use the value on tab one in C5 and return the list of all items numbers that correspond to C5 (column C in tab 2). Is this possible to do with a macro?

Thank you!!
Andrea BernardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please describe how you want the macro to work.
Andrea BernardAuthor Commented:
Sorry for not being clear, was hoping the Marco would return the lists of item numbers into E6 on tab one. Thank you!

Do you mean you want all the items into that one cell?

If so, how do you want them delimited?

Or perhaps you mean that, if there are three items (say), then you want them in E6, E7, and E8?


Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Martin LissOlder than dirtCommented:
was hoping the Marco would return the lists of item numbers into E6 on tab one.
I understood that. What I'm asking for is an explanation of how you would like that to happen. In other words do you want to type something into C5 on tab 1 and from that have the macro fill in the list?
Andrea BernardAuthor Commented:
Got it, I was planning to insert a button to run to macro. Eventually C5 will be a drop down list of items, and then I can click the button and in the list would show up in E6 and go down, E7, E8, E9 etc.

Bill PrewIT / Software Engineering ConsultantCommented:
Here's a basic solution to what you originally asked for, macro in attached.



You don't really need VBA for this.

Attached is a pure formula approach, so your file can stay VBA free (xlsx vs xlxm) plus it is self-documenting in terms of audit trail.

Just another option.

Bill PrewIT / Software Engineering ConsultantCommented:
And a little tidier...



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Try this.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

Place the following code on Tab 1 Sheet Module. To do so, right lick the Tab 1 --> View Code --> And paste the code given below into the opened code window --> Close the VB Editor and save your workbook as Macro-Enabled Workbook.

There is a drop down list in cell C5 and once you choose a department, the relevant Item# will get populated starting from E6.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim sws As Worksheet
Dim slr As Long, lr As Long, i As Long, j As Long
Dim x, y()
If Target.Address(0, 0) = "C5" Then
    Set sws = Sheets("tab 2")
    slr = sws.Cells(Rows.Count, 2).End(xlUp).Row
    x = sws.Range("B5:C" & slr).Value
    lr = Cells(Rows.Count, 5).End(xlUp).Row
    If lr > 5 Then Range("E6:E" & lr).Clear
    If Target <> "" Then
        For i = 1 To UBound(x, 1)
            If x(i, 2) = Target.Value Then
                j = j + 1
                ReDim Preserve y(1 To j)
                y(j) = x(i, 1)
            End If
        Next i
        If j > 0 Then
            Range("E6").Resize(j).Value = Application.Transpose(y)
            Range("E6").Resize(j).NumberFormat = "0000-00#"
        End If
    End If
End If
End Sub

Open in new window

Rob HensonFinance AnalystCommented:
You can achieve this without VBA or formulas. You can use a Pivot Table.

See attached. This uses the department as a Page Filter and then it shows the list of item numbers for that department.

If you were to change the data list to an excel Table rather than a standard list the Pivot would adjust automatically for the range of data expanding (or shrinking).

Rob HensonFinance AnalystCommented:
Thanks for the points. Oh wait, everybody but me received them.
Hi Rob,

I have a the remains of a pie here, you're welcome to have.... here, catch!

Oops, sorry.  I'm sure it will come out.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.