+ Reply to Thread
Results 1 to 9 of 9

I Need Help Creating a Complex Formula in Excel

  1. #1
    Registered User
    Join Date
    03-10-2015
    Location
    Chatham
    MS-Off Ver
    2013
    Posts
    3

    Exclamation I Need Help Creating a Complex Formula in Excel

    Hello all,

    I'm in a bit of a bind! I need to create a function that follows this structure:

    "If any duplicate value in column E has any value that is not null in column D, then the remaining fields in column D for that duplicate value in colum E equal the value of column D."

    Can anyone help?!

    Thanks folks
    Last edited by hamilton_00; 03-10-2015 at 02:45 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Complex Conditional Formula

    Hi, welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    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 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: I Need Help Creating a Complex Formula in Excel

    Thanks for the title change, but I hardly see how that is any more descriptive of your problem than the 1st title? Although, from your description, I hardly know how to describe that, myself, so I will leave it as is

    Thanks

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: I Need Help Creating a Complex Formula in Excel

    You can't change the values in Col E using a formula because they are already there. You can use a formula in a blank column and then copy>Paste Special>Values back into column E

    So let's say your data starts in row 2. In F2 copied down

    =IF(COUNTIFS($E$2:$E2, E2)>1, IF(D2<>"", D2, E2), E2)
    You didn't say what to do if the value in E was a duplicate and the value in D was a null set. I assumed use the value in E
    Does that do what you were trying to do? If not, uploading a small workbook (Go Advanced > Manage Attachments) can help us understand what you are looking for.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    03-10-2015
    Location
    Chatham
    MS-Off Ver
    2013
    Posts
    3

    Re: I Need Help Creating a Complex Formula in Excel

    Test.xlsx

    Please take a look at the attached example for reference. Where column A is Date, B is Username and C is Revised Date. When any username in the table has a date listed in the Date column, I want all of the duplicate Username's to reflect the same date. In this instance, all 4 PeterPans would be have a Revised Date of 3/10/2015. All Charles' would have a Revised Date of 2/4/2012, and all Darwins would remain blank.

    In a scenario where I only had 11 contacts, I would just do it all manually, but when I'm dealing with a list that has over 850,000 lines, it doesn't exactly work.

    I really appreciate any and all of your help!

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: I Need Help Creating a Complex Formula in Excel

    The following ARRAYED formula would work although may slow down your workbook quite a bit

    =MAX(IF($B$2:$B$170000=B2, $A$2:$A$170000))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: I Need Help Creating a Complex Formula in Excel

    Perhaps this, copied down?
    =IF(A2="",C1,A2)

    edit: ok, probably way too simple, especially if the names are all mixed around

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: I Need Help Creating a Complex Formula in Excel

    Maybe this?
    =INDEX($A$2:A2,MATCH(B2,$B$2:B2,0))

  9. #9
    Registered User
    Join Date
    03-10-2015
    Location
    Chatham
    MS-Off Ver
    2013
    Posts
    3

    Re: I Need Help Creating a Complex Formula in Excel

    Test 2.xlsx

    Thanks for both of those formulas FDibbins & ChemistB. They both partially work. Take a look at the attached document to see an example of how FDibbins' formula turned out. ChemistB, your fomula turned out the same way.

    Thanks again!

+ 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. Complex Conditional Formatting formula help
    By sean.tapscott in forum Excel General
    Replies: 14
    Last Post: 08-24-2010, 02:10 PM
  2. Complex If then Conditional formula
    By dklein2149 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2009, 12:29 PM
  3. Complex If then Conditional formula
    By dklein2149 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2009, 12:23 PM
  4. [SOLVED] Complex logical/conditional formula
    By Ash in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-12-2006, 06:10 PM
  5. [SOLVED] How do I do a complex conditional in a conditional formatting formula
    By Ray Stevens in forum Excel General
    Replies: 6
    Last Post: 03-12-2006, 06:30 PM

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