+ Reply to Thread
Results 1 to 9 of 9

Get Info from cells using if, index, small and concatenate

  1. #1
    Registered User
    Join Date
    02-27-2015
    Location
    Wtby, CT
    MS-Off Ver
    2010
    Posts
    7

    Get Info from cells using if, index, small and concatenate

    I have a sheet that lists first name, last name, and a column with a letter (A, B, C). I'm trying to retrieve the First and Last Name IF it contains B or C. If it contains A, it's supposed to go to the next row. I've tried many different ways and it comes up differently each time.

    1) =IF(ISBLANK(Details!$B89),"",IFERROR(INDEX(CONCATENATE(Details!B89:$B$200&", "&Details!C89:$C$200),SMALL(IF((Details!$E$2:Details!$E$200)={"B","C"},ROW(Details!$E$2:Details!$E$200)-ROW(Details!E$2)+1),ROW(B$1:B90)))," ")) Returns Last Name, First Name but it starts at the first person with the letter B and then EVERYONE after that and then just the "," until it hits the blank.

    2) =IF(ISBLANK(Details!$B95),"",IFERROR((SMALL(IF((Details!$E$2:Details!$E$200)={"B","C"},CONCATENATE(Details!B95:$B$200&", "&Details!C95:$C$200),ROW(Details!$E$2:Details!$E$200)-ROW(Details!E$2)+1),ROW(B$1:B96)))," ")) Returns a 1

    3) =IF((Details!$E$2:Details!$E$200)<>"A",IF(ISBLANK(Details!$B97),IFERROR(INDEX(CONCATENATE(Details!B97:$B$200&", "&Details!C97:$C$200),SMALL(IF((Details!$E$2:Details!$E$200)<>"A",ROW(Details!$E$2:Details!$E$200)-ROW(Details!E$2)+1),ROW(B$1:B99)))," "))) Returns FALSE

    4) =IF(OR(ISBLANK(Details!B93:$B$200),ISBLANK(Details!C93:$C$200)),"",IFERROR(INDEX(CONCATENATE(Details!B93:$B$200&", "&Details!C93:$C$200),SMALL(IF((Details!$E$2:Details!$E$200)<>"A",ROW(Details!$E$2:Details!$E$200)-ROW(Details!E$2)+1),ROW(B$1:B92))),"")) Returns EVERYONE

    5) =IF(ISBLANK(Details!$B101),"",IF((Details!$E$2:Details!$E$200)<>"A",CONCATENATE(Details!B99:$B$200&", "&Details!C99:$C$200),SMALL((ROW(Details!$E$2:Details!$E$200)-ROW(Details!E$2)+1),ROW(B$1:B96)))) Returns blank cells

    Does anyone have a solution for me? Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,814

    Re: Get Info from cells using if, index, small and concatenate

    Can you please post a sample workbook as it is difficult to relate your formulae without having actual data. Thank you.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,814

    Re: Get Info from cells using if, index, small and concatenate

    Is this what you need to do?

    Data starting row 2


    Col A is First Name
    Col B is Surname
    Col C is "A","B" or "C"



    formula in D2 entered with Ctrl+Shift+Enter


    =INDEX($A$2:$A$10&" " & $B$2:$B$10,SMALL(IF($C$2:$C$10<>"A",ROW($C$2:$C$10)-1,""),ROW(1:1)))


    will produce list in D of "B" & "C" names

  4. #4
    Registered User
    Join Date
    02-27-2015
    Location
    Wtby, CT
    MS-Off Ver
    2010
    Posts
    7

    Re: Get Info from cells using if, index, small and concatenate

    Thanks John. That is how my data is setup except its last name and first name. I have it so that it has "," between the last and first name. After the last person with "B" or "C", it produces a comma for a while then gives me #REF. I added IFERROR and removed the #REF but can't figure out how to remove the commas in the other cells.

    =IF(ISBLANK(Details!$B2),"",IFERROR(INDEX(Details!B2:$B$200&", "&Details!C2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1))),""))

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,814

    Re: Get Info from cells using if, index, small and concatenate

    The B & C columns need to be $B$2 and $C$2 in the INDEX part.

    To remove the "," ......

    =IF(ISBLANK(Details!$B2),"",IFERROR(IF(INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))),""))

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,814

    Re: Get Info from cells using if, index, small and concatenate

    sorry ... you can remove the first test ...

    =IFERROR(IF(INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))),"")

  7. #7
    Registered User
    Join Date
    02-27-2015
    Location
    Wtby, CT
    MS-Off Ver
    2010
    Posts
    7

    Re: Get Info from cells using if, index, small and concatenate

    Thank you very much! That worked perfectly. I'll probably be back as I think I still have a few more quirks to work out.

  8. #8
    Registered User
    Join Date
    02-27-2015
    Location
    Wtby, CT
    MS-Off Ver
    2010
    Posts
    7

    Re: Get Info from cells using if, index, small and concatenate

    So this worked:
    =IFERROR(IF(INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(Details!$E$2:Details!$E$200<>"A",ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))),"")

    But now I need to add an OR to it. I've tried this:
    =IFERROR(IF(INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(OR(Details!$E$2:Details!$E$200<>"A",Details!$E$2:Details!$E$200<>"X"),ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(OR(Details!$E$2:Details!$E$200<>"A",Details!$E$2:Details!$E$200<>"X"),ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))),"")

    And this:
    =IFERROR(IF(INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(OR(Details!$E$2:Details!$E$200<>{"A","X"}),ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(OR(Details!$E$2:Details!$E$200<>{"A","X"}),ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))),"")

    And this:
    =IFERROR(IF(INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(OR(Details!$E$2:Details!$E$200={"B","C"}),ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!$B$2:$B$200&", "&Details!$C$2:$C$200,SMALL(IF(OR(Details!$E$2:Details!$E$200={"B","C"}),ROW(Details!$E$2:Details!$E$200)-1,""),ROW(1:1)))),"")

    And nothing works. It still gives me all 4 instead of only the two I need.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,814

    Re: Get Info from cells using if, index, small and concatenate

    Try .....

    =IFERROR(IF(INDEX(DETAILS!$B$2:$B$200&", "&DETAILS!$C$2:$C$200,SMALL(IF((DETAILS!$E$2:DETAILS!$E$200<>"A")+(DETAILS!$E$2:DETAILS!$E$200<>"X"),ROW(DETAILS!$E$2:DETAILS!$E$200)-1,""),ROW(1:1)))=", "," ",INDEX(DETAILS!$B$2:$B$200&", "&DETAILS!$C$2:$C$200,SMALL(IF((DETAILS!$E$2:DETAILS!$E$200<>"A")+(DETAILS!$E$2:DETAILS!$E$200<>"X"),ROW(DETAILS!$E$2:DETAILS!$E$200)-1,""),ROW(1:1)))),"")

    As requested earlier it would help if you could post a sample file.

+ 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. Small/Index to Pull Specific Instances and Leave out cells unpopulated.
    By donnieboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2015, 06:08 AM
  2. Problem with Index and Small functions to lookup and display multiple cells
    By oneblondebrow in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-22-2013, 11:41 AM
  3. Replies: 2
    Last Post: 06-20-2012, 12:22 PM
  4. Replies: 0
    Last Post: 10-27-2011, 06:46 AM
  5. Replies: 1
    Last Post: 07-12-2006, 08:10 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