• 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.

Live Data (need help)

A

ashish mehra

Active Member
Hi all

I have encounter duplicate entries in my report.

To start with I have prepared a sample data with reduced number of fields.

First Sheet Data: It contains data with Duplicacy.

Second Sheet Output: It contains exact data which is required.

Any suggestion/question would be welcome!

Thanks in advance for help!;)

Regards,
AM:)
 

Attachments

  • Sample Data AM.xlsx
    15.7 KB · Views: 7
Hi Ashish ,

Will the duplicate entries be only in one or some columns ? If so , which column(s) ?

Do you need a formula solution or will VBA be OK ?

Narayan
 
Sir,

Duplicate entries have been generated in few rows with respect to unique items Enquiry No & Product Column.

I am fine with Function or VBA Solution.

Regards,
AM:)
 
Last edited:
Hi Ashish,

If that is the criteria of duplicates than why you left sr. no. 16 & 17?

Edited: I got it.

See the attached file green cells. Just apply the solution to your live data.

Regards,
 

Attachments

  • Sample_Data_AM.xlsx
    18.8 KB · Views: 7
Hi SM,

Sr. no. 16 & 17 belongs to same Enquiry# 306 with different Product BF-55 & BF-56.

There goes my Sunday to close this issue.;)

Regards,
AM:)
 
Hi SM,

Thanks for your reply! Your solution is providing the requirement which I was looking for!

I have tested your code by adding new enquiry# 307 for same company R Exports with different Product Code P-86. I have edit your formula to accommodate the updated enquiry.

My data will keep on updating everyday.

Can you please check the attached sample workbook & tweak your formula to make it work dynamically.

Regards,
AM:)
 

Attachments

  • Sample_Data_AM (2).xlsx
    25.8 KB · Views: 4
@ashish mehra

Just go through the attached file. SM sheet is with updated formula. Nothing Big is done only changed ranges in formula to DATA TABLE nomenclature.

Regards,
 

Attachments

  • Sample_Data_AM_(2).xlsx
    25.7 KB · Views: 3
Hi all,

I need solution through VBA due to heavy size of the file.
My Data is dynamic in nature with Fixed Columns.;)

Thanks!

Regards,
AM:)
 
@ashish mehra

See the attached file.

Changes Made:

  1. Inserted a macro. Recorded a macro for extracting unique record using advanced filter. You can either hit the button on OutputSheet or press Ctrl+Shift+E (keyboard shortcut alloted to Macro).
  2. Changed your source data to a table.
  3. Remove SNo column from source data as this was creating problem in extracting unique record.
Just run the macro on original data and see if the performance is good or Not. I had put a single formula in output sheet Column A to generate SNo.

Also go through the comments left in macro code, which will enable you to change it as per your data range.

Just advise if any issue.

Regards,
 

Attachments

  • Sample_Data_AM.xlsm
    328.6 KB · Views: 12
Last edited:
Hi SM,

Glad you have given me chance to learn. Thanks for your extended help!

Sorry:( for replying late. I was testing, developing code. Its working fine in Live Data.

Lastly, VBA coding is fun as compared to Formula Merging.o_O

Regards,
AM:)
 
Hi Ashish,

I also learned something in solving this. VBA is a whole new WORLD, in which you can do anything, but FORMULAS have their own special importance and they do MAGIC sometimes, Believe me :).

You are always Welcome.

Regards,
 
You must log in or register to reply here.
Back
Top