How to Make Cell Values Blank When Another Dependent Field is Duplicate

**dts10** · 03-12-2019, 01:21 PM

Hello fellows,

Looking for some help in deciphering the problem below. I've also included a file to point out the issue:

I have the initial data set in columns A and B (sample). I need a way to take column B into the final state scenario which should be the same as column H based on the hierarchy/priority table in columns D and E.

--

Further info:

Column A has duplicates. For each duplicate Fruit, I need to return only one value in the color section based on the table hierarchy so that it ends up like column H. Another potential issue may be that for Apples for example, there are two "Red" denominated for which in the final state, one must read "Red" and the next must read blank.

Best,

**sintek** · 03-12-2019, 02:05 PM

Hi dts10

Welcome to the Forum...

Not mentioned...but if your initial data is always going to be in order of color ranking then this works...

Please Login or Register  to view this content.

**xladept** · 03-12-2019, 02:19 PM

Hi dts,

Try this:

Please Login or Register  to view this content.

**dts10** · 03-12-2019, 02:56 PM

Wow this works perfectly! Thanks.

Could you please provide me with how I should modify to fit my spreadsheet? The duplicate values are in cell C and the values I need filtered are in Column J. The Reference table is in N and O.

**sintek** · 03-12-2019, 03:01 PM

Provide a sample of your actual file setup scenario so that we may try and assist...

**dts10** · 03-12-2019, 03:11 PM

Please find attached.

**sintek** · 03-12-2019, 03:21 PM

As per post 2...

Not mentioned...but if your initial data is always going to be in order of color ranking then this works...

None of the above code works for your actual file setup...In future it is advisable always to upload a sample depicting your actual scenario...
Back to drawing board...

Edit...Not much time left...will have a look at it in the morning...

**sintek** · 03-12-2019, 04:18 PM

I am slightly confused...According to your values in your data...
Apples & Oranges Priority is = Red
Pears & Watermelon Priority is = Orange

I don't get your Result...Why is Pears Blue if there is a corresponding value of Orange...
How is it that the Result table is sorted...Initially rows 2 & 3 were Orange...Why now are they red...

Perhaps I am missing something...

See attached Sintek result...Perhaps someone else can simply...

**dts10** · 03-12-2019, 04:27 PM

Hi Sintek - you are right. You got the right values! I hadn't realized there was an "Orange" value.

I'm trying to use the macro you implemented but getting a bud. Any idea why?

**sintek** · 03-12-2019, 04:44 PM

See Post 8...New sample file...

Please Login or Register  to view this content.

**dts10** · 03-12-2019, 05:30 PM

Works perfect in the example you provided! For some reason, I am getting the attached error message (Type Mismatch).

The only thing different between the file I gave you as an example and the real file I need to modify is that the real file is 15000 records vs the sample had only 100.

Any ideas?

**sintek** · 03-12-2019, 11:31 PM

I am making use of Column P as a helper column...Has your initial file got values in column P...Can only assume it is the size of the array...

**dts10** · 03-12-2019, 11:37 PM

No, values in column P.

Values go from column A to column L.

**sintek** · 03-12-2019, 11:51 PM

Is your content of a sensitive nature...Any chance you can desensitize and upload...
Assuming the array only has 1 value...

**dts10** · 03-13-2019, 09:53 AM

This is the best I can do. It has the exact format I am using. The only important content columns as mentioned before are C and H. Thank you so much for the help.

**sintek** · 03-13-2019, 10:45 AM

You must have changed something as the error you previously mentioned did not come up...Run-time error '1004': AutoFilter method of range class failed was new error...

Your reference table setup was different to initial file...Column J was hidden, no value in header row hence cells(1)current region code not working...

Changed reference table back to initial setup...Inserted a header in Col J...

Made use of Column M as helper instead of Col K...

This solves...

**dts10** · 03-13-2019, 12:40 PM

Getting the same error: For i = 2 To UBound(Val)

I realized the second tab (reference table) that you sent in the new file is different than the one I provided in post 15 (may have something to do with this?).

**dts10** · 03-13-2019, 12:45 PM

Could also be the following. In my Workbook. The 'Step 4 Errors' sheet is tab #4 and the 'Reference Table' sheet is tab #6. All of the other sheets 1-3 and 5 are irrelevant.

**sintek** · 03-13-2019, 12:48 PM

Does the file in post 16 work for you...

'Reference Table' sheet is tab #6

Yes this does effect...

Please Login or Register  to view this content.

You need to understand the importance of exact setup...

Code is written to the specifics and cannot accomodate changes that you might make or not incorporate in sample uploads...

**dts10** · 03-13-2019, 12:49 PM

Tried changing sheets and it seems like the execution progressed to: Cells(1).CurrentRegion.AutoFilter 13, ">" & mn. There it hit an error: Runtime error 1004, Auto Filter on Range Class failed.

**dts10** · 03-13-2019, 12:49 PM

See above. Still running into this error when executing.

**sintek** · 03-13-2019, 12:55 PM

I cannot assist further unless you are more forethcoming with your file...Can go around in circles forever...So far all the code provided works for your uploads provided...Only reason it will not work is if your actual file scenario is different...

**dts10** · 03-13-2019, 01:27 PM

Sintek - this is the format of the file. Please see. The Step4_Errors and Reference table have exact format of the sheets/workbook. The other sheets are placeholders (for this exercise we do not use these tabs).

**sintek** · 03-13-2019, 01:36 PM

This works....

How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Re: How to Make Cell Values Blank When Another Dependent Field is Duplicate

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Remove duplicate values in Field cell from same column

Bookmarks

Bookmarks

Posting Permissions