You are here:

Excel/lookup a single criteria and return multiple rows

Advertisement


Question
Hi, let' say I have the following data in my excel in Sheet 1 and there are more than 1,000 rows:

Code    ID      Name    Age     Profession
12345   246     Adam    24      Engineer
34567   357     Tom     37      Doctor
12345   135   Bryan   30   Engineer
23456   468   Gina   21   Teacher
34567   579   Sylvia   26   Nurse
12345   680   Jarod   29   IT Specialist
23456   791   Stacy   32   Counselor
23456   802   Robert   42   Lawyer
45678   913   Tricia   37   Manager
.
.
.
.
.

In Sheet 2 on the other hand, I have the following:
Code   ID   Name   Age   Profession
12345
45678
.
.
.
.
.

What I would like to do is to lookup the values(criteria) in the A column in Sheet 2 and get that data in Sheet 1 which meets the criteria and at the same time automatically insert in new rows since there might be more that 1 rows to be returned for each criteria, like so:

Code   ID   Name   Age   Profession
12345   246     Adam    24      Engineer
12345   135   Bryan   30   Engineer
12345   680   Jarod   29   IT Specialist
45678   913   Tricia   37   Manager

The purpose of Sheet 2 is to look up Codes that does not necessarily include every single Codes in Sheet 1, therefore filtering and sorting is not the problem. Just that I would like to be able to lookup certain Codes that are required to be extracted for reporting. I greatly appreciate if you could help or advice the formulas that I could use for this! Thanks!

Answer
This can't be done via formulas. You need VBA. Press Alt/F11, then Insert/Module, then paste in this code:

Sub Fillit()
   i = Cells(Rows.Count, 1).End(xlUp).Row
   With Sheet1
       n = .UsedRange.Rows.Count
ag:
       .Range("$A$1:$E$9").AutoFilter Field:=1, Criteria1:=Cells(i, 1).Value
       .Range("A2:E" & n).SpecialCells(xlCellTypeVisible).Copy
       Cells(i, 1).Select
       ActiveSheet.Paste
       i = i - 1
       If i > 1 Then
         m = Application.CountIf(Sheet1.Columns(1), Cells(i, 1).Value)
         If m > 1 Then
         Rows(i + 1).Resize(m - 1).Insert
         End If
         GoTo ag
       End If
       .Cells.AutoFilter
   End With
End Sub


When Sheet2 is active, run the macro (Alt/F8). Or, if you know how to assign a macro to a button, you can do that. Reminder: this workbook must be saved as an xlsm or the macro will disappear when you save it.

HTH
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.