+ Reply to Thread
Results 1 to 8 of 8

Complicated IF function...

  1. #1
    Registered User
    Join Date
    01-17-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2013
    Posts
    4

    Complicated IF function...

    I'm having difficulty with constructing a proper function. My data looks like this:

    A B C
    1 Place Publisher [function/output goes here]

    I'd like to produce the following output in C1:

    (Place: Publisher)

    However, not every entry will have place or publisher. So, in those cases I'd like:

    (Place)
    (Publisher)
    [blank if there is neither]

    I know how to create simple IF True or False arguments, and how to do AND and OR ones. Also, I know how to handle ISBLANK arguments. But still I'm failing to be able to string things together correctly.

    Can anyone help with this?

    Much thanks,
    John

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Complicated IF function...

    Hi, welcome tyo the forum

    Are you saying you want to take a 3-column data set and transpose that into 1 column?
    A
    B
    C
    1
    From this…
    2
    aa bb cc
    3
    dd ee ff
    4
    5
    To this…
    6
    aa
    7
    bb
    8
    cc
    9
    dd
    10
    ee
    11
    ff
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Complicated IF function...

    Assuming my guess in #2 is correct...
    A
    B
    C
    D
    1
    From this…
    2
    aa bb cc aa
    3
    dd ee ff bb
    4
    cc
    5
    To this… dd
    6
    aa ee
    7
    bb ff
    8
    cc
    0
    9
    dd
    0
    10
    ee
    0
    11
    ff


    D2=OFFSET($A$2,INT(ROW(A3)/3)-1,MOD(ROW(A2)-2,3))
    copied down

  4. #4
    Registered User
    Join Date
    01-17-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2013
    Posts
    4

    Re: Complicated IF function...

    Sorry, FDibbins, that is not what I meant. I'll attach what I am looking to produce. The data is in columns A and B and the function and output would be in column C. Thanks so much!Place_Publisher.xls

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Complicated IF function...

    And what is your expected outcome?

  6. #6
    Registered User
    Join Date
    01-17-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2013
    Posts
    4

    Re: Complicated IF function...

    Column C is the expected outcome.

  7. #7
    Registered User
    Join Date
    01-17-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2013
    Posts
    4

    Re: Complicated IF function...

    Oh, I think I just solved it. Here's what I came up with. There may be a more elegant way to get the desired results, but this did the trick:

    =If(and(isblank(A5),isblank(B5))=false, "(","")&If(and(isblank(A5))=false,A5,"")&If(or(isblank(A5),isblank(B5))=false,": ","")&If(and(isblank(A5),isblank(B5))=false,B5,"")&If(and(isblank(A5),isblank(B5))=false, ")","")

    Thanks, FDibbins, for your attention. I appreciate it.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Complicated IF function...

    OK, sxorry, misunderstood. Try this...
    =A1&IF(OR(B1="",A1=""),"",": ")&B1

+ 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] Complicated IF function
    By maidenhel in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-11-2014, 05:16 AM
  2. Complicated IF function
    By ruthyeh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2013, 10:59 AM
  3. Help with a complicated IF function
    By este994 in forum Excel General
    Replies: 17
    Last Post: 04-20-2011, 07:10 AM
  4. A Complicated Look up Function?
    By screamnyak in forum Excel General
    Replies: 2
    Last Post: 02-24-2011, 01:01 PM
  5. Complicated function; Can it be done?
    By The Boy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2007, 08:10 AM

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