min max dates

[sachinkapasi]

Hi,


I have a series of data as follows:


Id Date

1 2/12/2013

2 3/12/2013

2 1/12/2013

1 4/1/2013


I have many such ids and dates.


I want to find the minimum and maximum date for each distinct uid


So the output can be like this:


Id Min Max

1 2/12/2013 4/1/2013

2 1/12/2013 3/12/2013


Can someone please suggest a macro code or a pseudo code even to do this


Thanks

 

[sachinkapasi]

Can someone please look at this?

 

[shrivallabha]

Test this on a backup copy


Assuming your data is in column A[ID] & B[Dates].

[pre]

Public Sub GetMinMax()
Dim lngLastRow As Long

lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("C:E").Delete

Range("A1:A" & lngLastRow).AdvancedFilter _
xlFilterCopy, , Range("C1"), True

Range("D1:E1").Value = Array("Min", "Max")

With Range("C2")
With .Offset(0, 1)
.FormulaArray = "=MIN(IF($A$1:$A$" & lngLastRow & "=C2,$B$1:$B$" & lngLastRow & "))"
.NumberFormat = "m/d/yyyy"
End With
With .Offset(0, 2)
.FormulaArray = "=MAX(IF($A$1:$A$" & lngLastRow & "=C2,$B$1:$B$" & lngLastRow & "))"
.NumberFormat = "m/d/yyyy"
End With
End With

Range("D2:E" & Range("C" & Rows.Count).End(xlUp).Row).FillDown

End Sub

[/pre]

 

[sachinkapasi]

Thanks for the macro but I am facing errors 1004 at line


.FormulaArray = "=MAX(IF($A$1:$A$" & lngLastRow & "=C2,$B$1:$B$" & lngLastRow & "))"


Earlier it was giving error at Range("C:E").Delete


but I commented that line and now it is giving 1004 at the line mentioned above

 

[Faseeh]

Hi sachinkapasi,


Assuming your data is in Cell A2:B5, try this:


For Unique Numbers:

=INDEX($A$2:$A$5, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$5), 0))

For Max: [code]=MAX(IF(($A$2:$A$5=E2),($B$2:B$5)))

For Min: =MIN(IF(($A$2:$A$5=E2),($B$2:B$5)))[/code]


For All these press Ctrl+Shift+Enter.


Or see this: http://dl.dropbox.com/u/60644346/Unique%20List%20-%20Formula%20Solution.xlsx


Regards,

 

[shrivallabha]

I have specified the conditions for keeping your data. If your setup is different then the macro will need range adjustments.


Download the file:

https://www.box.com/s/bgonh63zvbmp22o07k96

 

[sachinkapasi]

Yes that array max min worked...thank you so much

 

[guna_sekar87]

Dear shrivallabha


Can u give ur email id or send me a confirmation message to my email id guna_sekar87@yahoo.co.in

 

[Debraj]

Hi Guna,


Just to inform you, I personally send Shrivallabha, a huge list of mails, regrading his

* Lottery Prize of 1,500,000 GBP,

* Free I-Pod by signing to my website.

* Forward my mail, and you will get $20 per forward..


But this man never replies..

So, I suggest you, Please don't choose this route to send him your mail..


Please stop collecting mail-Id's.. all have their own life..


SirJB7 we are missing you..


http://chandoo.org/forums/topic/data-inside-a-text-into-a-excel#post-84028

http://chandoo.org/forums/topic/data-extract-from-a-text-file-into-a-excel-file#post-83996

http://chandoo.org/forums/topic/min-max-dates#post-85041

http://chandoo.org/forums/topic/re-excel-dashboards-graphs#post-84843

http://chandoo.org/forums/topic/trace-precedents#post-84842

http://chandoo.org/forums/topic/using-data-lables-from-a-third-data-column-in-an-chart#post-84841


Regards,

Deb

 

[aparvez007]

Nice workout Deb...


Regards,

Pavi

 

[Faseeh]

Hi guna_sekar87,


I suggest you to start a new thread and post whatever you want, you will get quick replied no need to worry about email ids dear!


Cheers & Regards,

Faseeh

 

[shrivallabha]

Hi Guna_Sekar87,


People answer on forums on their personal time and can decide what to reply and what not to. A feature which I really like.


Besides Chandoo, I frequent:

http://www.vbaexpress.com/forum/forumdisplay.php?f=17

http://www.mrexcel.com/forum/excel-questions/


They are all good and you get the best help that you can get for free. So post your question here or any of these, you will get good answer.


I guess, in that case, you will not need my email


Whatever Deb has said is exactly other way round...LOL