• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Identifying new data removing duplicates.

S

Slimline

Member
Hi Narayan,

Cheers for the above!


If you really don’t mind there is something a little more challenging as I need because I am facing changes & duplication of data.


S0 I want to track & keep history of the changes of ‘salesman’, which may change every week.


FYI - In my weekly reports there is no ‘current salesman’ column. So I manually paste the new data in column B -C, skip D and then paste rest of the data in E-N.


New data = red

Old data = black.

Full Duplicate = Italic red


https://hotfile.com/dl/203207506/7a8f8c8/Advance_Filter_Updated_2_(2).xlsm.html


So when a new set of data is added to the list in ‘Salesrecord’ I want the macro button to do the following on the sheet:

1) Identify the new data added to the list (as it will be lower down in the list)

2) Look/match up the duplicate via REF# with the older data set (which will always be above it in the list)


- If the ‘original salesman’ in red = to the ‘original salesman’ in black, then ‘red’ is a duplicate & the whole red row is deleted.


- If the ‘original salesman’ in red is NOT = black, it is then copied into black ‘Current salesman’ cell and rest of the red row is also copied/ replaces the black row . (skipping ‘original salesman’). This last thing should happen every time incase other changes are made. The ‘originalsalesman’ stays unchanged as I always want to know which salesman it initially started from.


This would be extremely helpful if you can sort this out. Also thanks a lot for your previous work – its smashing!!!! 


Serena
 
Hi Serena ,


Sorry , but there are too many things that I'm unable to handle ! Can you please wait for a day or two ? Otherwise , I hope someone else can jump in and help.


Narayan
 
Hi, Slimline!

This topic is the same as your last question of this, just a few hours of difference:

http://chandoo.org/forums/topic/help-with-this-vba-code-please#post-93770


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the main green sticky post at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."http://chandoo.org/forums/topic/help-with-this-vba-code-please#post-93770


Regards!
 
Hi,


Sorry about the above. I started my own as I was hijacking someone else's post.


Narayan - look forward to hearing from you once you get a chance. Thanks for your help again!


Serena
 
Hi Serena ,


Can you clarify the following ?


1. Will the new data always be entered in RED color ?


2. Is it so that the entries in black can never have duplicates ?


3. How do we identify duplicates ? You have four fields viz. Salesman , Product , Quantity , Ref # ; is the Salesman field alone capable of identifying duplicates ? When ever two rows have identical data in the Salesman column , are the two rows duplicates ?


Narayan
 
Hi Narayan,


1) No- I just used colours to help explain what I wanted done. Everything will be in normal black font.


2)Duplicates will be identified by: Ref# & 'Original Salesman'.


So for the same ref# if OriginalSalesman is same than it is a duplicate and one of them should be deleted. The one lower down the list (which will be the new dataset added) should get deleted (i.e. data in italic RED which I've purposely included as full duplicates).


2nd PART:

However for the same ref# if 'OriginalSalesman' are NOT the same then it means there is a new salesman no 2! So the new salesman should be copied to 'CurrentSalesman' along with rest of the row, but skipping 'Orighinal Salesman'.

The ‘originalsalesman’ stays unchanged as I always want to know which salesman it initially started from.


*I know he the 2nd part is a bit confusing so please let me know if there any other ques. I'll watch this space.


**But if you can at least just do the 1st part of removing duplicates & send me the worksheet (with just that first) that would be really helpful aswell. That way I can start getting rid of the duplicates & adapt the vba to other worksheets.


Thanks!!!
 
You must log in or register to reply here.
Back
Top