+ Reply to Thread
Results 1 to 12 of 12

search for a specific item, return related data

  1. #1
    Registered User
    Join Date
    07-20-2018
    Location
    Philadelphia
    MS-Off Ver
    Professional Plus 2013
    Posts
    9

    search for a specific item, return related data

    Hello,

    Sorry about the title, once someone gives me some guidance on what it should be, i'll change that.

    Here's my issue.

    I've got an excel sheet with 87k rows on it. The important columns for my question are the sales order column, and the items column. Each sales order gets a row for each item, so it looks like this.

    1 AA
    1 BB
    1 DD
    2 AA
    2 FF
    3 CC
    3 DD
    3 EE
    4 TT
    5 AA
    5 ZZ


    What i need to be able to do, is search the sheet for a specific item, "AA" for example, and pull up the sales order, and any other items that are on that order.

    So in this example, i'd need to see...

    1 AA
    1 BB
    1 DD
    2 AA
    2 FF
    5 AA
    5 ZZ

    Sorting and filtering just got me the sales order number with AA, but not the other rows with the other items on it. I was looking at a pivot table, but didn't have much luck there either.

    Any ideas?

    Thank you,
    Last edited by jrhelp; 07-20-2018 at 03:23 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: search for a specific item, return related data

    Hi, welcome to the forum

    Im a little confused as to how searching for AA will bring back all that other data?

    If sorting did not also sort adjacent data, maybe you didnt include it in the sort range?

    When all else fails, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: search for a specific item, return related data

    I'd use advanced filter copy or filter in place.

    Sample for advanced filter copy attached.
    Steps:
    1. Set up list of unique items, and name the range.

    2. Set up Data Validation drop down

    3. Set up range to extract Sales Order that contains Item desired.
    In Sheet2!C2: Confirmed as array (CTRL + SHIFT + ENTER)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy down as far as needed. I've done it up to C8 for demo.

    4. Add all the headers to right of C column (leave some space for readability). It should match exactly the source columns.

    5. Add following code in VBA standard module. Changing range and sheet reference as needed.
    Please Login or Register  to view this content.
    6. Add button and assign the code.

    EDIT: Have a read of link below for more detailed explanation on Advanced Filter. And how to set up Criteria, Extract range etc.
    http://www.contextures.com/exceladvancedfiltervba.html
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: search for a specific item, return related data

    Here's one way.

    With your Order #'s in column A, Items in column B, and search item in E2:

    G2 =IFERROR(INDEX(A:A,SMALL(IF(B$2:B$12=E$2,ROW(B$2:B$12)),ROWS($1:1))),"") Ctrl Shift Enter

    Drag down as far as needed. This column can be hidden.

    C2 =COUNTIF(G:G,A2)

    Drag down column C.

    Filter column C for values of 1.

    See attachment.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-20-2018
    Location
    Philadelphia
    MS-Off Ver
    Professional Plus 2013
    Posts
    9

    Re: search for a specific item, return related data

    I'm probably missing something simple here. But this is what i'm looking at. I need to go through this data, find any orders that have RETURNLABEL on them, and then
    the entire sales order's items. You can see bellow that SO # 100 has a RETURNLABEL on it in addition to 5 other items. Does that make sense?

    100 2/19/2018 0:00 TYYT62118
    100 2/19/2018 0:00 RETURNLABEL
    100 2/19/2018 0:00 TYYT62125
    100 2/19/2018 0:00 DIWIRONICS Clear
    100 2/19/2018 0:00 BAG51
    100 2/19/2018 0:00 DIW1063785
    101 6/19/2018 0:00 TYYT63103
    101 6/19/2018 0:00 TYYT37296
    101 6/19/2018 0:00 BAG52
    101 6/19/2018 0:00 TYYT62739
    102 6/14/2018 0:00 UPMC Interface Item
    102 6/14/2018 0:00 TYYT63029
    103 4/2/2018 0:00 TYYT60148
    103 4/2/2018 0:00 RETURNLABEL
    103 4/2/2018 0:00 TYYT60177
    104 6/1/2018 0:00 TYYT61203
    104 6/1/2018 0:00 RETURNLABEL
    104 6/1/2018 0:00 TYYT61293
    104 6/1/2018 0:00 TYYTED Clear
    104 6/1/2018 0:00 BAG52
    104 6/1/2018 0:00 TYYT37299
    104 6/1/2018 0:00 CRA23

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: search for a specific item, return related data

    Here is the approach from post #4 modified to suit your needs explained in post #5:

    F2 =IFERROR(INDEX(A:A,SMALL(IF(C$2:C$23="RETURNLABEL",ROW(C$2:C$23)),ROWS($1:1))),"") Ctrl Shift Enter
    Dragged down through F23. I hid this column.

    D2 =COUNTIF(F:F,A2)
    Dragged down through D23.

    Filter column D to show only 1's.

    See attachment. If this isn't what you want, consider uploading a small representative sample of your data along with the desired result which you can enter manually.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-20-2018
    Location
    Philadelphia
    MS-Off Ver
    Professional Plus 2013
    Posts
    9

    Re: search for a specific item, return related data

    Okay, i think i successfully uploaded the two sheets.

    Assuming i did...

    Help is what i'm working with, my actual sheet has 87,000 rows, but i didn't include the entire thing for obvious reasons. I also had to remove or change some things for confidentiality issues.

    I need to pull all of the see all of the rows that have the same SO (Column A) and that also have an item, RETURNLABEL, as one of the items.

    Sample results is what i need returned. I manually just used CTRL+F to find those and then copy and pasted them into that document, but that's not really a viable approach for the full sheet.

    Uploading those was successful, but that was a somewhat confusing process.
    Attached Files Attached Files
    Last edited by jrhelp; 07-20-2018 at 02:21 PM.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: search for a specific item, return related data

    What about S.O. #'s 2161295 and 2342368? You left them out in your sample results but they both have "RETURNLABEL" in column O.

    I'll assume this was an error on your part. It's the same exact principal but I'll adapt the formula to what you have in post #7.

    X2 =IFERROR(INDEX(A:A,SMALL(IF(O$2:O$200="RETURNLABEL",ROW(O$2:O$200)),ROWS($1:1))),"") Ctrl Shift Enter
    Drag down until you get blanks. I then hid this column.

    V2 =COUNTIF(X:X,A2)
    Drag down column V.

    Filter column V to only show 1's.

    See attachment.
    Attached Files Attached Files

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: search for a specific item, return related data

    Here's the updated one using Advanced Filter.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-20-2018
    Location
    Philadelphia
    MS-Off Ver
    Professional Plus 2013
    Posts
    9

    Re: search for a specific item, return related data

    Thank you everyone for your help, 63falcondude, your approach worked correctly. I was initially confused because one of my entries in the big sheet of 87k had 2 return labels, but i figured it out.

    CK76, i appreciate the help and i'll check that to see if it works too. Also, i like the laughing man logo.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: search for a specific item, return related data

    You are welcome and thanks for the rep

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: search for a specific item, return related data

    Glad we could help. Thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] What is Nth and how can I describe it?
    By Imran Magsi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2016, 04:02 AM
  2. [SOLVED] I'm not sure how to describe it.
    By Cillendor in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-15-2015, 06:40 AM
  3. Not exactly sure how to describe this
    By zrobby85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2013, 11:24 AM
  4. Don't even know how to describe it quickly....
    By etpools in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-07-2013, 02:44 AM
  5. I'm not even sure how to describe this problem
    By EarlMachen in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-02-2008, 05:29 PM
  6. Can't describe....
    By ChrisMattock in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2007, 11:58 AM
  7. Don't even know how to describe this!
    By gooser60 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2007, 04:49 PM

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