+ Reply to Thread
Results 1 to 13 of 13

Return multiple lines of info with same reference.

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Return multiple lines of info with same reference.

    Test Data.xlsx



    Hi

    Again I need help, I am not having a good day.

    In the table which I have attached there are a number of rows which have the ticket number 6117, I need a formula that will return all the row information for each row with 6117. If I just use a simple vlookup it will only return the first row information.

    Thank you

    Faye

  2. #2
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Return multiple lines of info with same reference.

    hi fayep1986

    in this workbook where you want to display the results...?
    Regards,
    abdul

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

    Re: Return multiple lines of info with same reference.

    OKay.. use below formula:-


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    enter with key combination : ctrl shift enter

    see attachment:- Test Data(2).xlsx



    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return multiple lines of info with same reference.

    I want them displayed in the table to the right that I have created.

    Thanks

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return multiple lines of info with same reference.

    Hi DILIPandey

    Thank you for the formula, it seems to work for the first two rows but then after that it just displays blank.

    thank you

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return multiple lines of info with same reference.

    Hi

    The formula I have does not seem to be working.

    I have entered some more data to show what i am looking for and that the formula I am using doesn't return the data I need.

    Thank you all for your help.

    FayeTest Data(2) (2).xlsx

  7. #7
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Return multiple lines of info with same reference.

    The formula in the 1st cell will remain unchanged....
    see i have made some changes in the formula from the 2nd cell..

    see the file attatched.

    enjoy...!
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return multiple lines of info with same reference.

    That's amazing - thank you!

  9. #9
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Return multiple lines of info with same reference.

    @fayep1986:- my pleasure...!

  10. #10
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Return multiple lines of info with same reference.

    Hi fayep1986

    Try the following array formula in L3 and copy down (CTRL + SHIFT + ENTER to commit).
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then the following non array formulas
    In P3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In R3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  11. #11
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return multiple lines of info with same reference.

    You are probably all going to be more than sick of me by now.

    Thank you all for your help and everything you have each said has worked up to a point.

    I am attaching exactly what I am attempting to do just without all the confidential info. I am trying to create an invoicing document in excel as it is only a small business so no need for sage. On the first main sheet labelled Template is the invoice itself where I want all the data to feed through to. On the second sheet is the full breakdown of each individual invoice with their appropriate ticket numbers included.

    What I want is that when I copy across all the ticket numbers to the invoice template for it to bring across the item description for each, the qty and the unit price. I will then summarise each ticket at the bottom but the invoice will detail in full also what is being invoiced.

    However, the formulas in my sheet currently work for the first 20 or so ticket numbers. But when I select for example E35:E56 from the Invoice list and copy this into the template, the formulas no longer work correctly. I have done this so that you can see. I need the formulas to work no matter which ticket numbers I choose from my invoice list sheet.

    I hope this makes sense and that someone can help me.

    I appreciate all your help,

    FayeInvoicing Template - Test Data.xlsx

  12. #12
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Return multiple lines of info with same reference.

    MY RECOMMENDATIONS:-
    DON'T GIVE THE REFERENCE OF WHOLE SHEET IN THE FORMULA AS LIKE I HAVE MADE BOLD

    =IF(ISBLANK($B19),"",IFERROR(INDEX('Invoice List'!$A:$H,SMALL(IF('Invoice List'!$E:$E=B19,ROW('Invoice List'!$E:$E),""),IF(COUNTIF($B$17:B18,B19)<1,1,COUNTIF($B$17:B18,B19)+1)),6),""))

    ANYWAYZ I HAVE CORRECTED YOUR SHEET & ATTATCHED BELOW..IT WILL WORK PROPERLY NOW...

    ENJOY...!
    Attached Files Attached Files

  13. #13
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Return multiple lines of info with same reference.

    Hi fayep1986

    Have a look at the attached file, see if this is of any use.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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