How to calculate time difference for duplicated entries

Dear Excel expert

Thank you in advance for your help.

Please help me to find the differences in date when d column has value greater than 1, it means that companies in B column appears more than once. I would like to calculate the difference of their respective date (a column) when they appear more than once in e cloumn.

For example b14 company appears two times in the row, in e14 column i have calculated their difference of date manually. When a company appears more than once I have also calculated their difference of date manually to give you an idea what i am looking for.

Please help me.

Thanks
Shams

You'll need to do an array formula, i'll attach a quick demo.

Hi mahershams,

1) how title "countifs" is realted to content of your question. Have you ever tried to use http://www.excelforum.com/search.php ??? Title of the post shall be "a short summary of the problem". See: http://www.excelforum.com/forum-rule...rum-rules.html :

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search.
...
If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

2) it's obvoius in case there are two occurences of one item. But you have not described which dates shall be taken into account when there are 3 or more occurences. I used latest-earliest. If other adjust the formula.
in E2 array (Ctrl+Shift+Enter commited) formula:

Formula:

Please Login or Register  to view this content.

PS. Where is Ausralia? :-P

Hello

Please see attached.

I have used

=IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9=B2,ROW($B$2:$B$9)),2),1)-INDEX($A$1:$A$9,MATCH($B$2,$B$1:B9,0),1),"")

Which is a little bit off, but would be a good starting place.

See if the attached updated workbook is what you are looking for.


Regards, TMS

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Originally Posted by mahershams

Fotis: Thank your continuous guidance in the forum. Hope I will provide a good title next time.

Regards
Shams

What is this that you don't understand in this...????

...Your post title should accurately and concisely describe your problem, not your anticipated solution.

Did your new title describe any problem that i didn't noticed..??

Sorry but i can not do anything more for you. Since yesterday i try to tell you that if you want to be a member here you have to comply with forum rules. You prefer to ignore me and continously break the rules.

In this case soon you'll be a banned user. Good luck with whatever you decide to do.

Hi Shams,

1) What I expect the right title for such thread could be is for instance:

How to calculate time difference for duplicated entries

The title is not really for those who will to answer - They usually read daily number of posts with strange/not adequate titles. It is for those, who use search engine. And the forum is not just to help you (and others) but also to accumulate a knowledge base for active searching users.

2) My (side) question was not: Where is Australia, but where is Ausralia - have a closer look on your profile data, and edit the location: http://www.excelforum.com/profile.php?do=editprofile

Last but not least - thanks for reputation point - I did my best tu guess what result you are really looking for.

Hi Shams,

I agree with Kaper to a large extent. I don't bother too much about titles. If the question makes sense and I have enough information, I'll have a go at at solution. I'm MORE inclined to try and produce a solution if there's a sample workbook. If a title is obviously bad, I'll usually warn the OP that the Moderator(s) might take exception and ask them to change it. In this case, there had already been a few posts, so I didn't bother.

Kaper is also right that the purpose of a good thread title is to make it more meaningful to other people searching the forum or just Googling an answer to their question. In this case, if someone were looking for COUNTIFS or Array Formula, they might come across this thread which probably wouldn't help them.

Though, that said, my solution used:

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

so you never know.

So, Fotis is also correct in asking you to change your thread title. nathansav might have led you down the wrong path inadvertently by suggesting you needed an Array Formula. And, with over 200 posts, you should be expected to know better ... to be an experienced forum user, even if you are not an experienced Excel user

The thing is that I've used COUNTIFS and Array Formula and INDEX/MATCH in this reply, so there's a good chance that anyone searching for those topics might come across this thread anyway.

I think that Kaper's suggested thread title is a very good one ... but a little late in the day.

It's probably best not to keep challenging the Moderator(s) as that probably won't help your case and maybe earn more infractions or get you banned ... not good.

So, just draw a line and, next time, spend a little time trying to think of a thread title that summarises the problem you need to address. It doesn't mean you can't use COUNTIF, but maybe more in context. For example, "COUNTIF produces incorrect results" and the body of the thread explaining the background ... and a sample workbook. That title says you're using COUNTIF and you have a problem. Factual and it doesn't assume a solution.

Thanks again for the rep.

Kind regards, TMS