# I Need Help Creating a Complex Formula in Excel

1. ## 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

2. ## Re: Complex Conditional Formula

Hi, welcome to the forum

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.

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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. ## Re: I Need Help Creating a Complex Formula in Excel

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

9. ## 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!

There are currently 1 users browsing this thread. (0 members and 1 guests)