Pulling out the latest records ONLY. VBA

[Lifeseeker]

Hi there,

I have attached the file.

What I would like to do:

For each record in column A of "test" worksheet, I want to keep the the ones with the latest date. So in "output" worksheet, you will see ONLY those records with the latest date.

Sometimes, there can be up to 3 or 4 lines for the same record, with each line showing different dates, but I only want the latest date, which is the last one for the record.

Can this be done using a macro of some sort?

Guidance or comment is much appreciated.

Thanks

[mike7952]

Give this a try

Please Login or Register  to view this content.

[Lifeseeker]

wow...amazing...it worked.

If you don't mind walking me through the code...I would like to see if I fully understand how the code works. I am new to the scripting.dictionary...

Please Login or Register  to view this content.

. What does 1 represent? 1 is just the normal default code used in this example?

What does

Please Login or Register  to view this content.

mean?
In this code,

Please Login or Register  to view this content.

, what does "item" mean? and how does ";" work?

in the next line, what does "Split" work?

I think if I can understand some of these keywords, then I can find my way through easier.

Hope you can help.
Thanks!

[mike7952]

What does 1 represent? 1 is just the normal default code used in this example?

If the CompareMode is TextCompare (1) the comparison is case-insensitive, while it is case-sensitive if the CompareMode is BinaryCompare (0).

What does .exists(a(i,1)) mean?

Use it to check if a given key is already in use in the dictionary

.Item(a(i, 1)) = a(i, 1) & ";" & a(i, 2)

.item(key) = a(i, 1) & ";" & a(i, 2) Value for that key. ";" Is used like columns. "AB1/12";"1-Jul-12"




Here's an good link
http://www.vbusers.com/code/codeget....D=524&PostID=1

[Lifeseeker]

Hi,

I still can't seem to wrap my head around the use of ";" in there.

Please Login or Register  to view this content.

Are we treating values in two columns together as one object? "AB1/12" together with "1-Jul-12"?

[Lifeseeker]

Anybody can help answer my question?

thanks

[Lifeseeker]

Hi, can anybody help out?

[mike7952]

See if this help

Please Login or Register  to view this content.

[JieJenn]

Here's a different approach

Please Login or Register  to view this content.

[mike7952]

Yet anoher way

Please Login or Register  to view this content.

[Lifeseeker]

Hi,
So it does look like we are kind of merging the cell in

Please Login or Register  to view this content.

One more question: what does

Please Login or Register  to view this content.

does?

I always have problems visualizing how Resize() function does. I understand that we are outputting the results starting at A2.

Thanks

[mike7952]

.Cells(i + 1, 1).Resize(, 2) = Split(a(i), ";")

When we split a(i) we will have 2 columns. So Resize(0 Rows,2 Columns)
1 = AB1/12 ";" 2 = 7/1/2012


And here's another good link

http://www.experts-exchange.com/Soft...ss-in-VBA.html

[Lifeseeker]

In

Please Login or Register  to view this content.

, why are we offsetting at the end?

I can see that this scripting dictionary object is powerful. It's helpful to master it.

Thanks

[mike7952]

There reason for offset at the end is to make the Range("a2:b and whatever is the last row is in column A). I didnt know if column A and B would have the same last row so I get the lastrow in column A and use offset(0 rows, 1 column)

Please Login or Register  to view this content.

If Column A and Column B would have the same last row then we could use

Please Login or Register  to view this content.

[Lifeseeker]

If I have columns in between A and C, and there are other columns like shown in this attached file, and I still want to achieve the same thing, how will that change the code? Are we talking about significant changes?

Thanks

[mike7952]

And what would you want the output to be? The same columns as before or include all columns?

[Lifeseeker]

Include all columns please.

The Col A and Col C are really just there to help identify which records we want to keep and which records we want to delete. So it's the same principle as before, but has more columns to be put into the array.

[mike7952]

Something like this. Not to much modifictions

Please Login or Register  to view this content.

[Lifeseeker]

In

Please Login or Register  to view this content.

what does that little 3 in the bracket mean?

[mike7952]

Please Login or Register  to view this content.

Actually the 3 needs to be 2 my bad. Because when you split the item it is 0 based array.

0 ; 1 ; 2 ; 3 ; 4
AB1/12;names;29-Jul-12;gender;test

(0) = AB1/12
(1) = names
(2) = 29-Jul-12
(3) = gender
(4) = test

[mike7952]

I noticed also you have this in the worksheets module. I would remove it from there and place it into a standard Module.

[Lifeseeker]

so if the actual date column is in E, then it would be (4), correct?

[mike7952]

Yes your correct the split would be

Please Login or Register  to view this content.

[Lifeseeker]

I really appreciate your help and taking your time to explain everything to me. Thank you!