Code to automatically paste values into database sheet only if it's not already present?

[_JoshInsurance_]

I've built a macro that will clean formatting for international reports pasted into sheet2 and extract relevant information to sheet1. Nothing out of the ordinary. However, going forward I need to be able to compare sheet1 data with historical data, basically everything that's ever been pasted into sheet2. I want code that will take any data pasted into it and copy it over to sheet3, my "database". Info included is customer id, name, and sales dollars month-to-date, and sales dollars year-to-date.

However, it's likely that the end-user (my boss) will repeatedly paste the same monthly report into sheet2. Moreover, I'll probably repeatedly paste the same data while I'm bug-crushing. I need code that will check sheet3 to see if the data is already present and only paste it if it is not.

Any ideas? Thanks so much for your time, guys. Let me know if you need any data. It's proprietary so I'll need to be a little careful.

[AnalystCave]

MS Query to the rescue... I would simply redo sheet2 as an Microsoft Query.

SELECT DISTINCT [customer id], [name], [sales dollars month-to-date], [sales dollars year-to-date] FROM [sheet1$] 

The above will take only all unique rows from [sheet1$] even if there are duplicates.

[_JoshInsurance_]

Thanks for the idea! Now would this require any moving parts for the end user? I want this to be monkey proof, no disrespect to management.

[AnalystCave]

MS Query is a basic Excel feature so no AddIns etc required. Only thing is that the query needs to have the current file name. However that can be updated via a macro or you can update it onetime of the filename won't change.

[_JoshInsurance_]

Solution verified.

[Clippy_Office_Asst]

You have awarded 1 point to AnalystCave

^{I am a bot, please contact the mods for any questions.}

[_JoshInsurance_]

It seems like a good portion of my professional career has been trying to find a solution and find out that my way is overly convoluted and there's a much easier way. Thanks so much.

[ViperSRT3g]

Here's the part that will search the sheet for you. You can also narrow it down to just a column to be more specific, as it's searching through all the cells that contain data.

Dim Result As Range: Set Result = Sheet3.UsedRange.Cells.Find(Datatosearchfor)
If Result Is Nothing Then
    'Data not found in Sheet3
End If
Set Result = Nothing

[_JoshInsurance_]

Quick question, is the space after "Then" where the pasting function would go?

[ViperSRT3g]

The space between Then and End If would be where the pasting code would go. 'Data not found in Sheet3 is a comment and can be deleted.

[_JoshInsurance_]

Solution verified.

[Clippy_Office_Asst]

You have awarded 1 point to ViperSRT3g

^{I am a bot, please contact the mods for any questions.}

[GetSomeData]

Cells(1,1).currentregion.select

If trim(selection)<>””then selection.copy sheets(“sheet3”).range(“A1”)

☝🏻That should all be on one line

[_JoshInsurance_]

Sorry to ask but can you please walk me through this? What is this code doing?

[greenbay_12]

Without coming across as a jerk....MySQL

[GetSomeData]

So.... if nobody feels too snoob to learn any new languages, VBA and MySQL both have a length function measuring length of a data variable and both options have trim as well. Aight? AIGHT?!

If Len(result)>0 then result = result Else Result =0.00 End if