RemoveDuplicates: Removes non-duplicates..

[helgeroe]

Hi
The RemoveDuplicates function is very useful, but I had a really weird experience with it today.

RemoveDuplicates removes the last entry in this list:
<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=80><COLGROUP><COL style="WIDTH: 60pt" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=80>BV650550050</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>BV650550030</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>BV6505500</TD></TR></TBODY></TABLE>
None of these are duplicates, so does anybody know why this happens?

Helge

 

[Jerry Sullivan]

Hi Helge,

I didn't get that result when running remove duplicates on just those 3 values.

When you ran the remove duplicates operation, did you have more than one column of data selected?

In the example below, if you apply remove duplicates and only select Column K as the column that contains duplicates, it will remove the last row.

Excel Workbook
	K	L
1	A	BV650550050
2	B	BV650550030
3	A	BV6505500
Sheet

 

[helgeroe]

Hi Helge,

I didn't get that result when running remove duplicates on just those 3 values.

When you ran the remove duplicates operation, did you have more than one column of data selected?

In the example below, if you apply remove duplicates and only select Column K as the column that contains duplicates, it will remove the last row.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 121px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>A</TD><TD>BV650550050</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>B</TD><TD>BV650550030</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>A</TD><TD>BV6505500</TD></TR></TBODY></TABLE>

When I first discovered the problem a mocro ran the RemoveDeplicates on a table with a little more than 300 entries. The numbers did not add up, so I exstracted the code that was removed together with similar codes for a test. Then it was easy to see what happened, as described above.

But when I tested with the 3 entry table above today, the last entry was not removed, so it worked like it should.

A bit scary to know that bugs like this can happen in Excel, but I have included extra checks in the model that will reveal if this problem occurs again.

The problem might have something to do with the fact that the macro are using RemoveDuplicates repeatedly (7 times) on a large data material (up to ~250k rows).

 

[Jerry Sullivan]

When I first discovered the problem a mocro ran the RemoveDeplicates on a table with a little more than 300 entries. The numbers did not add up, so I exstracted the code that was removed together with similar codes for a test. Then it was easy to see what happened, as described above.

Are you saying that you do understand what happened when you ran your macro on the table?

What happened, and what was the "bug"?

 

[helgeroe]

Are you saying that you do understand what happened when you ran your macro on the table?

What happened, and what was the "bug"?

No, I have still not any clue why this happened. My only explanation is that it is some kind of bug that occurs under special conditions (what conditions I do not know).

But it was easy to observe what was happening (the last entry removed from the list when running RemoveDuplicates) when I exstracted the small table (3 entries) from the large table (>3000 entries).

I did the test both with macro and w/o macro with same result (that was when I ran it yesterday).

Yesterday I also tried to change the order in the macro from RemoveDuplicate-Sort to Sort-RemoveDuplicate, but that only made it worse: then the 2 other entries was removed.

Really weird stuff!

 

[Jerry Sullivan]

I'd be interested in looking at your workbook and macro to try to figure out that mystery. If you can post it to a hosting site, or send me a PM with your email address, I'd be glad to take a look.

 

[helgeroe]

I'd be interested in looking at your workbook and macro to try to figure out that mystery. If you can post it to a hosting site, or send me a PM with your email address, I'd be glad to take a look.

Would have been great if you could have checked it out, but unfortuneately the model includes all stock transactions for all our customers for a month and I feel that it is not appropriate to share this information on the internet.