+ Reply to Thread
Results 1 to 6 of 6

Display all rows of data which show specific data in specified column

  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    3

    Display all rows of data which show specific data in specified column

    I work in property for 23 different hand receipts. Currently I have one spreadsheet that has the information for all 23 hand receipts in one big list. What I want to do is set up multiple tabs which would all reference data in the main tab. Each tab would only pull data which has a specific customer ID, and would do this automatically. That way I can pull up the main list for all 10,000+ lines, or just click on the hand receipt tab which I want to view. I understand that I can just filter a column to do the same thing. But, my concern is to screw up the main tab accidentally by doing so.

    Here's an example of what I'm talking about. Below is what is in my main tab.

    Column A Column B Column C
    12345 Red Pen Y86ABZ
    34565 Blue Pen Y86ACO
    23409 Black Pen Y86ABZ

    What I want to be able to do is search Column C for "Y86ABZ". If the cell has that information then in a seperate tab it would display the information from Column A and Column B for that row.

    Issue is, how do I do a line by line cell search for Y86ABZ out of the other possible 23 codes. to display the information i want. What I mean is since Row 1 has the correct answer it would display that information in the first row of the new tab. Since row two does not have the correct answer it would be skipped. Which then means the main row 3's information would be displayed in the new tab on row 2. It's confusing I know. I can picture it my head. I just cannot figure out the formula.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Display all rows of data which show specific data in specified column

    Hi muaddib87,

    Welcome to the forum.

    See the attached workbook where sheet 1 has the scenario shared by you... on sheet 2 I have used below formula to achieved the desired results:-
    Display all rows of matching data.xlsx


    {=IFERROR(INDEX(Sheet1!$A$1:$C$4,SMALL(IF(Sheet1!$C$2:$C$4=Sheet2!$A$2,ROW(Sheet1!$C$2:$C$4),""),ROW(Sheet2!$A1)),COLUMN(Sheet2!A$1)),"")}

    If this approach appeals, go ahead and download the attached workbook... thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-21-2012
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Display all rows of data which show specific data in specified column

    I'm having difficulties adapting the formula to meet my requirements. I've attached an excel spreadsheet that is part of what I'm trying to pull. What I am trying to do is to search the Customer ID (Column J) for a specific ID. Then have the data for that row entered into another worksheet specifically for the ID. That way Sheet 2 would only show the information for the clinic ID in D2.
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Display all rows of data which show specific data in specified column

    Hi muaddib87,

    That formula is an array formula and need to be entered with Ctrl + Shift + Enter key combination.. rest all looks fine

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    06-21-2012
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Display all rows of data which show specific data in specified column

    Are there any limitations to how many columns or rows I can reference? I'm curious because the master field that I'm referencing has columns A to BE. And the columns which I'm pulling information from are not next to each other.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Display all rows of data which show specific data in specified column

    Hi muaddib87,

    You can use multiple references in arrays.. but please share the query using the sample workbook.. thank.s

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1