+ Reply to Thread
Results 1 to 15 of 15

A formula to return a list

  1. #1
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    A formula to return a list

    Hi Guys,

    Wondering how I would go about the following.

    Sheet 1 Column A1 Customers Column B1 Products
    A2 Mickey B2 Apple
    A3 Donald B3 Orange
    A4 Daisey B4 Banana


    Sheet 2 Column A1 Manually type in (Customers or Products)
    A2 (Formula) (look up sheet2 A1 in Sheet 1 and return 1 below it)
    A3 (Formula) (look up sheet2 A1 in Sheet 1 and return 2 below it)
    A4 (Formula) (look up sheet2 A1 in Sheet 1 and return 3 below it)

    So if I type "customer" in Sheet 2 A1 I get the list of Mickey, Donald, Daisey in A2 - A4

    And if I type "product" in Sheet 2 A1 I get the list of Apple, Orange, Banana in A2 - A4

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,514

    Re: A formula to return a list

    Hi

    That is a simple combination of the Match and Index Functions.

    Formula For A2

    =INDEX(Sheet1!$A$1:$B$4,ROW(),MATCH($A$1,Sheet1!$A$1:$B$1,0))

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Last edited by mehmetcik; 01-11-2019 at 05:38 PM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: A formula to return a list

    No sample needed for something so simple.

    Sheet2 B2, then drag down.

    =IFERROR(INDEX(Sheet1!$A$2:$B$4,ROWS($A$2:$A2),MATCH($A$1:Sheet1!$A$1:$B$1)),"")

    If you still need it for 2003 then just replace IFERROR with =IF(ISERROR(formula),"",formula) as you have previously.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,514

    Re: A formula to return a list

    LOL

    I was dropping a hint.

  5. #5
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    Re: A formula to return a list

    Hey Guys,

    Thanks for the help. I'm not great on this if error and match stuff. I was just about to post a sample until I read Jasons post. I am trying to manipulate it for 2003 but I'm doing something wrong.I'm assuming this will be array?

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    Re: A formula to return a list

    This is the code I used for reference to try do yours. Is there a way to tweak this code to ignore ""


    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: A formula to return a list

    I had a typo, so that didn't help.

    It's only the selection of Customers / Products that could return an error, so creating a data validation list for that will omit the need for error trapping the formula. (Hopefully 2003 has validation, I can't remember that far back ).

    =T(INDEX('SH1 BEFORE'!$A$2:$B$5,ROWS($A$2:$A2),MATCH($A$1,'SH1 BEFORE'!$A$1:$B$1,0)))

  8. #8
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    Re: A formula to return a list

    Thanks Jason I appreciate that.

    That worked in 2003. You have been a great help (Again)

    Do you know how I can get this to ignore blanks. This creates a list but because of the source it always starts with a blank. The Mega formula you did for me previously fixed that problem before however I don't need the mega formula for this and I wouldn't now where to start altering the mega formula to suit this
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: A formula to return a list

    See if this works

    =IF(ISERROR(INDEX(AN$3:AN$1500,MATCH(0,COUNTIFS(BM$2:BM2,AN$3:AN$1500,BM$2:BM2,"<>"),0))),"",INDEX(AN$3:AN$1500,MATCH(0,COUNTIFS(BM$2:BM2,AN$3:AN$1500,BM$2:BM2,"<>"),0)))

  10. #10
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    Re: A formula to return a list

    Hi Jason,

    That didn't work.

    This is the formula I have in AN
    Please Login or Register  to view this content.
    This is creating a list down AN and generally the fist entry on the list will be "" because the the first line is the opening balance and there is no category to it.

    Then in BM I have
    Please Login or Register  to view this content.
    which is condensing the list of AN. It eliminates all duplicates and mid blanks but not the first blank.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: A formula to return a list

    What do you have in BM1 and BM2?

    To skip the first blank, you need a blank at the top of BM so that it is included in the COUNTIF range.

  12. #12
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    Re: A formula to return a list

    BM1 is empty
    BM2 is
    Please Login or Register  to view this content.
    This is the heading In this case it's Fundraising

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: A formula to return a list

    In that case, expanding the countif range to BM1 should fix it

    =IF(ISERROR(INDEX(AN$3:AN$1500,MATCH(0,COUNTIF(BM$1:BM2,AN$3:AN$1500),0))),"",INDEX(AN$3:AN$1500,MATCH(0,COUNTIF(BM$1:BM2,AN$3:AN$1500),0)))

    As before, you might need ="" in BM1

  14. #14
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    303

    Re: A formula to return a list

    Again,

    Spot on. Worked perfect. Great to have it neat and tidy. Thanks so much for the help. How much to stick a USB key in your ear and download

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: A formula to return a list

    Quote Originally Posted by Nitro2481 View Post
    How much to stick a USB key in your ear and download
    Not recommended, too much corrupt data. In serious need of error check and defragmentation

+ 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