+ Reply to Thread
Results 1 to 7 of 7

Capture all instances of single name from list of all names

  1. #1
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Capture all instances of single name from list of all names

    Workbook contains spreadsheets for product test results. Details of all products to be tested in the stated month are entered on Main Page. There exists spreadsheets for each product. I want to list all instances of each product name and batch number from main page to specific spreadsheet with no gaps in rows. I tried using "INDEX" function but get either blanks or #Value errors. I am trying to use a modification of the following formula:=IF(B12>$G$3,"",INDEX(Sorted!$B$3:$B$50,ROW(Sorted!$B$3:$B$50)-3,ROW(1:1))). This formula was taken from a previous thread with similar properties. Unfortunately I am not that versed to fully understand the syntax in order to effectively edit the formula for this application.
    Workbook attached.
    Main Page: Data entered in columns C to H; Data in columns C, D, and E are candidates for capture.
    Sorted: Picks the product from Main Page
    APF Gels: Captures Product Name and Batch Code. After all data are entered on Main Page the document is printed and data are manually recorded on individual spreadsheets, then transferred back to workbook for the final calculations.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Capture all instances of single name from list of all names

    Maybe this array formula in APF gels, C12, copied across to D and then down:

    =IFERROR(INDEX('Main Page'!C:C,SMALL(IF('Main Page'!B11:$B$50='APF Gels'!$D$3,ROW('Main Page'!B11:$B$50)),ROWS(C$12:C12))),"")

    use similar formulae for the remaining sheets.

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    You no longer require column B and, so far as I can tell, the sheet "Sorted".
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Capture all instances of single name from list of all names

    With slight change the ARRAY formula by Glenn Kennedy can be used in C12 of any product sheet. Then Dragged across.

    =IFERROR(INDEX('Main Page'!C:C,SMALL(IF('Main Page'!B11:$B$50=$D$3,ROW('Main Page'!B11:$B$50)),ROWS(C$12:C12))),"")
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Capture all instances of single name from list of all names

    Thank you for the prompt reply. unfortunately, the formula appears to omit 1 of the items in each category. I tried to see a pattern, thinking maybe if the same product name was listed consecutively, even with other products from different groups in between that the formula would omit the first or second instance, but that didn't appear to be the case. Any ideas? The example in your reply has only 2 items listed for APF Gels. There should be 3.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Capture all instances of single name from list of all names

    Corrected formula here

    =IFERROR(INDEX('Main Page'!C:C,SMALL(IF('Main Page'!$B$11:$B$50=$D$3,ROW('Main Page'!$B$11:$B$50)),ROWS(C$12:C12))),"")
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Thumbs up Re: Capture all instances of single name from list of all names

    That did the trick. I'm always amazed how a misplaced comma or a missing symbol can have a dramatic effect on whether the code works or whether it gives the correct answer.
    Thanks again.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Capture all instances of single name from list of all names

    Welcome. Thanks for feed back.

+ 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. extracting a single list of names with a formula
    By andrewc in forum Excel General
    Replies: 1
    Last Post: 11-19-2015, 06:00 PM
  2. [SOLVED] separate list of names in same cell separated by semicolon into one single list
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2014, 11:18 AM
  3. [SOLVED] Copying single instances of multiple names
    By andrewc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-01-2013, 04:56 AM
  4. Reduce multpiple names to single list
    By papah73 in forum Excel General
    Replies: 2
    Last Post: 08-03-2013, 07:05 AM
  5. Replies: 3
    Last Post: 01-09-2012, 09:46 PM
  6. help finding single names from large list
    By dew23456 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2006, 09:10 PM
  7. Find Multiple instances of Single Criterion in Row & Return To a Single Col
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-09-2006, 10:10 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