+ 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
    2013
    Posts
    323

    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
    8,448

    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.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    8,448

    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
    2013
    Posts
    323

    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
    2013
    Posts
    323

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    2013
    Posts
    323

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    2013
    Posts
    323

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    2013
    Posts
    323

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    2013
    Posts
    323

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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)

Similar Threads

  1. [SOLVED] Using IF formula to return a List
    By RichPee in forum Excel General
    Replies: 3
    Last Post: 02-01-2018, 11:48 AM
  2. [SOLVED] Using a Formula to return a list based on a cellls value.
    By JonWilf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2016, 08:12 AM
  3. Data Validation List - Formula to Sort and Return Unique Values in List
    By beewketu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2015, 04:18 PM
  4. [SOLVED] Need a formula/s to return the three most recent dates from a list
    By john dalton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2015, 04:31 AM
  5. [SOLVED] Formula to return name from list - only once.
    By SVTF in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-11-2014, 02:46 AM
  6. [SOLVED] Formula to reference a list and return yes/no
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-03-2014, 02:16 PM
  7. Formula to return a value from a list
    By amandaw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2013, 06:01 AM

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