• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A little help on dates Please

G

guitarman

Member
Hi There

It is me again. I would like a bit of help on including a date in my formula.I work out the pairings for our golf club. I have sorted out the formula for doing that with all the help from all you kind people in the past.=COUNTIF($F$2:$F$550,12) but what i want to do is include the date in the formula when these pairings took place. In this example I have Player(1) and Player(2) in this example it looks like 12 but it works. So how would I include the date into this formula or would it have to be seperate?

Mike
 
Hi Mike,


It looks like you are counting 12 (number of time it appears) in the range F2:F550.


Now, do you want to count 12 in the above specified range for a particular date? If yes,then countifs should do the job.


Assume your data ranges as follows from Col A to Col B:


12 9/14/2012

10 9/15/2012

12 9/14/2012

10 9/15/2012

11 9/16/2012

12 9/14/2012

14 9/18/2012

12 9/15/2012


Now, at C1 write: =COUNTIFS(A1:A8,12,B1:B8,"9/14/2012")


This sould give you the count 3 as for "9/14/2012" we have three 12 in the range at Col A .


Please let us know if this is what you are looking for or I have misunderstood your query, by any chance..


Regards,

Kaushik
 
Hi There kaushik03


I think maybe you may have misunderstood as I explained first off 12 in the formula is actually Player(1) and Player(2). But it is entered as (12) and the next one is (37) which is actually player(3) and player(7). And player(10) and player(11) is entered 1011 and it all works well in the Formula I just want to be able to add the date in the next column to each one of when they actually played together.

Mike
 
Hi Mike,


Sorry as I misunderstood your query.


But again I am not able to understand when you say "I just want to be able to add the date in the next column to each one of when they actually played together".


You mean to say that you want to add date to the corresponding cell(in the next column) of 12 (entered for Player(1) and Player(2)), where the date should be same for Player(1) and Playe(2)[both the players played together on the same date].


If this is the case....then which date you are talking about? Do you have a specific list of dates which you want to lookup and add.


Can you please upload a sample workbook here for better data visualization?


Regards,

Kaushik
 
Hi kaushik03

Yes your right I have a list of dates and I want excel to sort them into their respective orders because in future when I list down the pairings in the spreadsheet I want it to include the date. For example player(1)& player(2) which is (12) in the formula last played together on May 12th 2012 and this was their seventh time of being drawn together So when they are drawn together next time it will automatically insert the date and the number (8).

Mike
 
Hi, guitarman!

From your second post above this I conclude that player 1 and player 12 would be read the same as player 11 and player 2: 112. If I'm right I'd suggest you to split data in 2 columns or to enter data as nn,mm where nn is the integer part and mm the decimal part, so the first e.g. would become 1,12 and 11,2 avoiding any confusion.

BTW, I agree with Faseeh about the sample file.

Regards!
 
Hi SirJB7

I must apologise for taking so long to answer but I have been in a stupid meeting that lasted forever. Anyway just to verify things player(1) and player(2) are entered in the formula as (12)=COUNTIF($F$2:$F$550,12) And (79) is Player(7) and player(9)and (35) is player(3)and player(5) and so on.The above formula gives me the amount of times they have played together but I would like the date in the next column when they last played together to stop any moaning about they only played together a couple of weeks ago.

F G H

players Times Date

7 9 8 ?

3 5 4 ?

4 5 2 ?

1 5 3 ?

So all I really want to know is how to alter the formula to include the date.

Mike
 
Hi Mike,


How the dates are aligned in your raw data which you want to lookup?


Is this in the following format? (for example)


Player 7 9/14/2012

Player 7 9/15/2012

Player 7 9/16/2012

Player 9 9/14/2012

Player 9 9/17/2012

Player 9 9/18/2012


For 79 you want to look up 9/14/2012 as this is the date when Player 7 and Player 9 (i.e. 79) played together.


OR it is in some other format?


However, again, it would be really great if you could upload your workbook here..

http://chandoo.org/forums/topic/posting-a-sample-workbook


Kaushik
 
Hi kaushik03

That is right 79. player(7) player(9) =COUNTIF($F$2:$F$550,79).So when I enter the details it needs to look like this

F>>>>>>>G>>>>>>>H

Player>>Times>>>Date

7/9>>>>>8>>>>>>>14/9/2012

is that clearer now because uploading this workbook would be a hassle because it is massive it goes back to 1982. so when I have got the formula for adding the dates everything will be fine. As I have said this formula gives me the times they have all played together but no dates.

Mike
 
Hi, guitarman!


Don't worry about the delay, the world's still spinning round.


I don't know if I explained myself correctly so I'll try again:


- I was not intending to solve your date issue, just bringing attention to your player combination's notation


- e.g.: player 1 vs. player 12 and player 11 vs. player 2 are both expressed as 112


- my suggestion was to change the XXYY notation (XX for 1st player, YY for 2nd player) to something like XX.YY or XX_YY or... so as to avoid wrongly retrieved data: 112 will retrieve the number of times of both previous combinations which I think they differ from what you want


Hope it helps.


Regards!
 
Hi There SirJB7 and kaushik03


Thank you for all your help and time but I have resolved the problem with a macro and it has done exactly what I wanted it to so everything is now up to date. Once again thank you for time and patience. Have a great day and a brilliant life

Mike
 
Glad to hear you were able to solve it guitarman. For future readers, would you mind posting the macro you came up with?
 
Hi, guitarman!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top