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

Find Unique Values based on selected Date

ashish mehra

Active Member
Hi,

Need a formula or code to check the string in Sheet1 column B (Source) & then find the unique values based on the selected date in Sheet2 cell Q2 (range Q1:R2 is the Criteria range).

The string that is already available in Source column in previous dates should not be populated.

Result to be populated in Sheet2.

Regards,
AM:)
 

Attachments

  • Ashish Macro.xlsm
    30.5 KB · Views: 7
Hi Asheesh,

Thanks for your time & effort.

One thing I missedo_O, I develop a vba code using Advance Filter technique to populate the unique values for selected date.

My result & yours are same.;)

But later on the requirement from the user is to populate only values that are unique & should not be repeat from previous dates.

I want a formula or code to look for selected date & based on the selection populate the unique values.

Example:

Week1 (10-Aug) contains url1, url2, url3
Week2 (17-Aug) contains url1, url2, url3, url4
Week3 (24-Aug) contains url1, url2, url3, url4, url5

When I select Week3: url5 is requirement.

I have done some modifications in the Source column to make things look simple.

Regards,
AM:)
 

Attachments

  • Ashish Macro Updated.xlsm
    29.4 KB · Views: 3
Hey Ashish - Check this one...i have done the modifications in the previous attachment..

Note I am using a helper column in the sheet1 to achieve the result...
 

Attachments

  • Ashish Macro.xlsm
    38.9 KB · Views: 9
Hi Ashish,

Just another formula to solve the issue. Note this is an array formula:

=INDEX(Table1[Source],SMALL(IF(IFERROR(MATCH(IF(Table1[Week End Date]=Sheet2!$Q$2,Table1[Source]),Table1[Source],0),0)>=MIN(IF($Q$2=Table1[Week End Date],ROW(Table1[Week End Date])-ROW($A$2)+1)),ROW(Table1[Week End Date])-ROW($A$2)+1),ROWS(T$2:T2)))

Regards,
 
Hello Ashish,

Since you are looking to return, if source are appearing first time in the selected date, you can simplify to,

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Table1[Week End Date]=Q$2,IF(MATCH(Table1[Source],Sheet1!B:B,0)=ROW(Table1[Source]),ROW(Table1[Source]))),ROWS(R$2:R2))),"")

Array Entered.
 
Back
Top