0

I have a MS Excel Table that has dates in Column A and in Column B has values for attribute on that given date. Each date has multiple entries. E.G. there are three entries/rows for 2022-10-02. I only want to keep the last row for each date. By last I mean the third row of the three entries for a given day. Is there VBA code to do that?

Initial

        A        B   
  |------------|----|
1 | 10/20/2022 | 5  |
  |------------|----|
2 | 10/20/2022 | 23 |
  |------------|----|
3 | 10/20/2022 | 18 |
  |------------|----|

After Code

        A        B   
  |------------|----|
1 | 10/20/2022 | 18 |
  |------------|----|

Thanks

Improve this question
2
  • Is this a one-time cleanup or do you expect this to run all the time?
    – Blindspots
    Commented Nov 8, 2022 at 3:59
  • VBA code can be do almost anything, except write itself. We also do not write code for you. We can help troubleshoot code you did write. consider using formulas or power query as alternatives.
    – gns100
    Commented Nov 8, 2022 at 17:37

2 Answers 2

Reset to default
0

What version of Excel are you using? If it is 365 you have an option to make a table from the data use UNIQUE on column A (you might want to use SORT in front of that to keep things in order then in the column beside use XLOOKUP on the value

enter image description here

Improve this answer
0

Filter data using array formulas

It is a simple matter to filter the data using a formula in reverse order, much more complex to have automatic row removal and determine appropriate logic. I have filtered based on the order (as you wished) however comparing the actual date time would be a more reliable approach to determine the newest entry instead of the order which can be accidentally changed and would be impossible to restore in it's absence unless you add an index of some sort.

MS Excel

 Cell D2:
=SORT(UNIQUE(FILTER(A:A,(A:A<>"")*(A:A<>A1))),1)

 Cell E2:  
=BYROW(FILTER(D:D,D:D<>""),LAMBDA(d, INDEX(B:B,XMATCH(d, A:A,, -1))))

Google Sheets

 Cell D2:
=SORT(UNIQUE(A2:A),1,1)

 Cell E2:  
=BYROW(FILTER(D2:D,D2:D<>""),LAMBDA(d, INDEX(B:B,XMATCH(d, A:A,, -1))))

Sample Data

      A          B     C       D            E
  |------------|-------|---|------------|------------|
1 | Duplicates | Value |   | Unique     | Last Value |
  |------------|-------|---|------------|------------|
2 | 10/1/2022  | 1     |   | 10/1/2022  | 111        |
  |------------|-------|---|------------|------------|
3 | 10/20/2022 | 5     |   | 10/20/2022 | 18         |
  |------------|-------|---|------------|------------|
4 | 10/20/2022 | 23    |   | 10/23/2022 | 23         |
  |------------|-------|---|------------|------------|
5 | 10/20/2022 | 18    |   | 10/27/2022 | 27         |
  |------------|-------|---|------------|------------|
6 | 10/27/2022 | 27    |   |            |            |
  |------------|-------|---|------------|------------|
7 | 10/1/2022  | 11    |   |            |            |
  |------------|-------|---|------------|------------|
8 | 10/23/2022 | 23    |   |            |            |
  |------------|-------|---|------------|------------|
9 | 10/1/2022  | 111   |   |            |            |
  |------------|-------|---|------------|------------|
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .