Search code examples
exceltype-conversioncopy-pasteequality

Bizarre Excel value inequality

I get an excel data feed file from an outside source on vendor information. I copy and paste-values into my own XLSM and run some VBA scripts on the data. In short, the script aggregates data removing duplicate rows leaving one list of unique vendor numbers in column A with the aggregated data for each vendor. Then the script uploads the data into an SQL database for other departments.

Yesterday while uploading, my database spit an error claiming an INSERT attempt with a duplicate value where dupes are not allowed. Strange... I went back to the excel data and sorted on vendor number to discover that there are tons of duplicate rows. What? This never happened before.

I highlighted the sorted column A and copied it off to another sheet, then used the Remove Duplicates tool. Excel reported that there are no duplicates when I'm literally staring at easily 10 cases of duplicate vendor numbers. Unfortunately, this is a TEXT column because other vendor numbers have letters in them like AX6058

The top two cells A1 and A2 are showing vendor number 7003 and 7003. As a test, when I say =A1=A2 the test returns FALSE.

I made sure both cells were TEXT... =A1=A2 still returns FALSE

I changed both cells to GENERAL... =A1=A2 still returns FALSE

I TRIM()'ed both cells for spaces... =A1=A2 still returns FALSE

I tested the LEN() of both cells... they are both 4

I forced both cells to be NUMBER with no decimals and =A1=A2 is still FALSE!!

If I add 1 to 7003 = 7004, then subtract 1 to get back to 7003 for both cells, then =A1=A2 finally returns TRUE after the math manipulation.

So now my VBA script has to segregate on textual vendor numbers that are truly numeric and conduct this goofy math work to ensure that the duplicate vendor number are TRUE-ly equal so they can be aggregated.

Has anyone seen this before? Or have some funky trick or setting so I don't have to do the goofy +1 / -1 math in my script?

As I said, I am xlPastingValues:

'copy vendor ID (in column 6)
 ActiveSheet.Columns(6).Copy
 Sheets("Tempcopy").Columns(1).PasteSpecial xlPasteValues

Thanks, John

EDIT: My Excel is 2010, but I don't know the version used to provide the source data.

Solution

  • Using @BrakNicku comments, I added an interim sheet that forces the correct data types, then copies into the worksheet for processing.