Results 1 to 7 of 7

Thread: Sort Data According to date/lowest balance:
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Sort Data According to date/lowest balance:

    Sample Data:

    ID # Date Balance
    3605944 2/15/2012 15:20 $1.19
    3605944 2/15/2012 15:20 $2.19
    3605944 2/15/2012 15:21 $3.19
    3605944 2/15/2012 15:21 $2.94
    3605944 2/15/2012 15:21 $2.69 ***
    3605972 2/12/2012 9:05 $10.42
    3605972 2/13/2012 9:05 $9.42
    3605972 2/14/2012 9:05 $8.42 ***
    3605981 2/14/2012 10:58 $1.07
    3605981 2/14/2012 10:58 $0.82
    3605981 2/14/2012 10:58 $0.57
    3605981 2/14/2012 10:59 $0.32
    3605981 2/14/2012 10:59 $0.07***



    How do I sort the data so that only the latest date is left over with it's respective (lowest) balance and card number?

    So that I would end up with the data below.

    3605944 2/15/2012 15:21 $2.69
    3605972 2/14/2012 9:05 $8.42
    3605981 2/14/2012 10:59 $0.07

    thanks,
    Bruce

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Data According to date/lowest balance:

    Well first off, sorting won't do it by itself.
    You'll really need some kind of filtering.
    But before we get to that, how do we differentiate between entries that appear to have identical date and time, such as items 3, 4, and 5, or items 12 and 13 ?
    Is it sufficient to rely on the sequence that they are already in, and simply take whichever is the last entry for each item ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Data According to date/lowest balance:

    Gerald, if an entry has an identical date, then I need the one with the lowest $ balance.

  4. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Data According to date/lowest balance:

    OK. Is the lowest $ balance always the one at the bottom (for that ID / date / time combination), or could it be higher up ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    New Member
    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Sort Data According to date/lowest balance:

    The $ Balance is at the bottom because that is the last time that the specific ID Card was used.

    i.e.

    3605981 2/14/2012 10:59 $0.07***

    The last time ID# 3605981 was used on the 2-14 at 10:59pm and the remaining balance was 7 cents.

  6. #6
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Data According to date/lowest balance:

    OK. Here's what I would do, there may be better ways.
    I'm assuming we can rely on the data already being in the correct sequence, which I think is what you are saying.

    I would use a helper column to identify which rows to highlight, like this.
    Let's assume
    ID # is in Col A
    Date and time is in Col B
    Balance is in col C

    On your first row, let's assume it's row 2
    =if(A2=A3,0,1)
    and copy this down as far as required.
    Then, use Data Filter to show only items with a 1 in this helper column.
    If you want, you can then copy the filtered data to another range / worksheet.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  7. #7
    New Member
    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Data According to date/lowest balance:

    Perfect,
    Thank you Gerald.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •