Unique value formula required

Unique value formula required

Post by Avinas » Sun, 03 Jun 2012 02:29:41



Hi Excel's,

I want to retrieve unique records from the data. 1st preference will be
formula.

otherwise if anyone can help me with the macro or function will also
welcome.

please find attached sheet for better understanding.

Regards,

Avinash

  unique designations.xlsx
13K Download
 
 
 

Unique value formula required

Post by NOORAIN ANSAR » Sun, 03 Jun 2012 02:38:22


Dear Avinash,

Please try it with ctrl+shfit+Enter
*=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),ROW(A1))-1)*

See attached sheet

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com


> Hi Excel's,

> I want to retrieve unique records from the data. 1st preference will be
> formula.

> otherwise if anyone can help me with the macro or function will also
> welcome.

> please find attached sheet for better understanding.

> Regards,

> Avinash

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


  Copy of unique designations(Solved).xlsx
15K Download

 
 
 

Unique value formula required

Post by NOORAIN ANSAR » Sun, 03 Jun 2012 02:47:27


Dear Avinash,

Please try this macro to create unique list

*Sub Unique_List()
Dim rng As Range
Set rng = Application.InputBox("Select a range", , , , , , , 8)
rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
Unique:=True
End Sub
*

See attached sheet..
--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com


> Dear Avinash,

> Please try it with ctrl+shfit+Enter
> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),ROW(A1))-1)*

> See attached sheet

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com


>> Hi Excel's,

>> I want to retrieve unique records from the data. 1st preference will be
>> formula.

>> otherwise if anyone can help me with the macro or function will also
>> welcome.

>> please find attached sheet for better understanding.

>> Regards,

>> Avinash

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to


--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

  Copy of unique designations(Macro).xls
54K Download
 
 
 

Unique value formula required

Post by Avinas » Sun, 03 Jun 2012 04:20:55


Hi NOORAIN,

Thanks for your help man both the formula and macro works fine.

you are just awesome man simply great only 5 lines of code and doing
exceptional task.

salute for your logic and your knowledge.

not just words.... ! :)

simply great.

Regards,

Avinash


> Dear Avinash,

> Please try this macro to create unique list

> *Sub Unique_List()
> Dim rng As Range
> Set rng = Application.InputBox("Select a range", , , , , , , 8)
> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
> Unique:=True
> End Sub
> *

> See attached sheet..
> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com


>> Dear Avinash,

>> Please try it with ctrl+shfit+Enter
>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),ROW(A1))-1)*

>> See attached sheet

>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.com


>>> Hi Excel's,

>>> I want to retrieve unique records from the data. 1st preference will be
>>> formula.

>>> otherwise if anyone can help me with the macro or function will also
>>> welcome.

>>> please find attached sheet for better understanding.

>>> Regards,

>>> Avinash

>>> --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 4) Acknowledge the responses you receive, good or bad.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------

>>> To unsubscribe, send a blank email to

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com


> Dear Avinash,

> Please try this macro to create unique list

> *Sub Unique_List()
> Dim rng As Range
> Set rng = Application.InputBox("Select a range", , , , , , , 8)
> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
> Unique:=True
> End Sub
> *

> See attached sheet..
> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com


>> Dear Avinash,

>> Please try it with ctrl+shfit+Enter
>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),ROW(A1))-1)*

>> See attached sheet

>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.com


>>> Hi Excel's,

>>> I want to retrieve unique records from the data. 1st preference will be
>>> formula.

>>> otherwise if anyone can help me with the macro or function will also
>>> welcome.

>>> please find attached sheet for better understanding.

>>> Regards,

>>> Avinash

>>> --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 4) Acknowledge the responses you receive, good or bad.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------

>>> To unsubscribe, send a blank email to

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

 
 
 

Unique value formula required

Post by Mahesh para » Mon, 04 Jun 2012 05:23:06


Hi  Avinash

Perhaps ..! you can use below Array formula with dynamic name range
and without any helper column

=IFERROR(INDEX(DESIG,MATCH(0,COUNTIF($F$1:F1,DESIG),0)),"")

DESIG refers to Dynamic Name Range : =OFFSET(Sheet1!$C$2, 0, 0,
COUNTA(Sheet1!$C:$C), 1)

Use ctrl+shfit+Enter to enter formula

HTH
Mahesh


> Hi NOORAIN,

> Thanks for your help man both the formula and macro works fine.

> you are just awesome man simply great only 5 lines of code and doing
> exceptional task.

> salute for your logic and your knowledge.

> not just words.... ! :)

> simply great.

> Regards,

> Avinash


>> Dear Avinash,

>> Please try this macro to create unique list

>> *Sub Unique_List()
>> Dim rng As Range
>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>> Unique:=True
>> End Sub
>> *

>> See attached sheet..
>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>



>>> Dear Avinash,

>>> Please try it with ctrl+shfit+Enter
>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),ROW(A1))-1)
>>> *

>>> See attached sheet

>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>


>>>> Hi Excel's,

>>>> I want to retrieve unique records from the data. 1st preference will be
>>>> formula.

>>>> otherwise if anyone can help me with the macro or function will also
>>>> welcome.

>>>> please find attached sheet for better understanding.

>>>> Regards,

>>>> Avinash

>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)

>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.

>>>> 2) Don't post a question in the thread of another member.

>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.

>>>> 4) Acknowledge the responses you receive, good or bad.

>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.

>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.

>>>> ------------------------------**------------------------------**
>>>> ------------------------------**------------



>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>


>> Dear Avinash,

>> Please try this macro to create unique list

>> *Sub Unique_List()
>> Dim rng As Range
>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>> Unique:=True
>> End Sub
>> *

>> See attached sheet..
>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>



>>> Dear Avinash,

>>> Please try it with ctrl+shfit+Enter
>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),ROW(A1))-1)
>>> *

>>> See attached sheet

>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>


>>>> Hi Excel's,

>>>> I want to retrieve unique records from the data. 1st preference will be
>>>> formula.

>>>> otherwise if anyone can help me with the macro or function will also
>>>> welcome.

>>>> please find attached sheet for better understanding.

>>>> Regards,

>>>> Avinash

>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)

>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.

>>>> 2) Don't post a question in the thread of another member.

>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.

>>>> 4) Acknowledge the responses you receive, good or bad.

>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.

>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.

>>>> ------------------------------**------------------------------**
>>>> ------------------------------**------------



>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


  unique designations_Solution.xls
26K Download
 
 
 

Unique value formula required

Post by GetExcel Busisof » Mon, 04 Jun 2012 14:46:48


submit your any query at  http://www.getexcel.in/feedback


> Hi Excel's,

> I want to retrieve unique records from the data. 1st preference will be
> formula.

> otherwise if anyone can help me with the macro or function will also
> welcome.

> please find attached sheet for better understanding.

> Regards,

> Avinash

 
 
 

Unique value formula required

Post by NOORAIN ANSAR » Tue, 05 Jun 2012 18:15:53


Nice Solution Mahesh.........

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

On Sun, Jun 3, 2012 at 1:53 AM, Mahesh parab <mahes...@gmail.com> wrote:
> Hi  Avinash

> Perhaps ..! you can use below Array formula with dynamic name range
> and without any helper column

> =IFERROR(INDEX(DESIG,MATCH(0,COUNTIF($F$1:F1,DESIG),0)),"")

> DESIG refers to Dynamic Name Range : =OFFSET(Sheet1!$C$2, 0, 0,
> COUNTA(Sheet1!$C:$C), 1)

> Use ctrl+shfit+Enter to enter formula

> HTH
> Mahesh

> On Sat, Jun 2, 2012 at 12:50 AM, Avinash <avinash007pa...@gmail.com>wrote:

>> Hi NOORAIN,

>> Thanks for your help man both the formula and macro works fine.

>> you are just awesome man simply great only 5 lines of code and doing
>> exceptional task.

>> salute for your logic and your knowledge.

>> not just words.... ! :)

>> simply great.

>> Regards,

>> Avinash

>> On Friday, June 1, 2012 11:17:27 PM UTC+5:30, NOORAIN ANSARI wrote:

>>> Dear Avinash,

>>> Please try this macro to create unique list

>>> *Sub Unique_List()
>>> Dim rng As Range
>>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>>> Unique:=True
>>> End Sub
>>> *

>>> See attached sheet..
>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>> On Fri, Jun 1, 2012 at 11:08 PM, NOORAIN ANSARI <
>>> noorain.ans...@gmail.com> wrote:

>>>> Dear Avinash,

>>>> Please try it with ctrl+shfit+Enter
>>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),
>>>> ROW(A1))-1)*

>>>> See attached sheet

>>>> On Fri, Jun 1, 2012 at 10:59 PM, Avinash <avinash007pa...@gmail.com>wrote:

>>>>> Hi Excel's,

>>>>> I want to retrieve unique records from the data. 1st preference will
>>>>> be formula.

>>>>> otherwise if anyone can help me with the macro or function will also
>>>>> welcome.

>>>>> please find attached sheet for better understanding.

>>>>> Regards,

>>>>> Avinash

>>>>> --
>>>>> FORUM RULES (986+ members already BANNED for violation)

>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>> Advice will not get quick attention or may not be answered.

>>>>> 2) Don't post a question in the thread of another member.

>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>> measure.

>>>>> 4) Acknowledge the responses you receive, good or bad.

>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>> in signatures are prohibited.

>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>> Forum owners and members are not responsible for any loss.

>>>>> ------------------------------**------------------------------**
>>>>> ------------------------------**------------
>>>>> To post to this group, send email to excel-macros@googlegroups.com

>>>>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>>>>> googlegroups.com <excel-macros%2Bunsubscribe@googlegroups.com>

>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>> On Friday, June 1, 2012 11:17:27 PM UTC+5:30, NOORAIN ANSARI wrote:

>>> Dear Avinash,

>>> Please try this macro to create unique list

>>> *Sub Unique_List()
>>> Dim rng As Range
>>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>>> Unique:=True
>>> End Sub
>>> *

>>> See attached sheet..
>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>> On Fri, Jun 1, 2012 at 11:08 PM, NOORAIN ANSARI <
>>> noorain.ans...@gmail.com> wrote:

>>>> Dear Avinash,

>>>> Please try it with ctrl+shfit+Enter
>>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),
>>>> ROW(A1))-1)*

>>>> See attached sheet

>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>> On Fri, Jun 1, 2012 at 10:59 PM, Avinash <avinash007pa...@gmail.com>wrote:

>>>>> Hi Excel's,

>>>>> I want to retrieve unique records from the data. 1st preference will
>>>>> be formula.

>>>>> otherwise if anyone can help me with the macro or function will also
>>>>> welcome.

>>>>> please find attached sheet for better understanding.

>>>>> Regards,

>>>>> Avinash

>>>>> --
>>>>> FORUM RULES (986+ members already BANNED for violation)

>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>> Advice will not get quick attention or may not be answered.

>>>>> 2) Don't post a question in the thread of another member.

>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>> measure.

>>>>> 4) Acknowledge the responses you receive, good or bad.

>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>> in signatures are prohibited.

>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>> Forum owners and members are not responsible for any loss.

>>>>> ------------------------------**------------------------------**
>>>>> ------------------------------**------------
>>>>> To post to this group, send email to excel-macros@googlegroups.com

>>>>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>>>>> googlegroups.com <excel-macros%2Bunsubscribe@googlegroups.com>

>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>  --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com

>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com
 
 
 

Unique value formula required

Post by Marie » Tue, 05 Jun 2012 19:49:32


Hi,

Also try below array formula,

=IFERROR(INDEX($C$2:$C$25,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$25),0)),"")

On Mon, Jun 4, 2012 at 2:15 AM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:

> Nice Solution Mahesh.........

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

> On Sun, Jun 3, 2012 at 1:53 AM, Mahesh parab <mahes...@gmail.com> wrote:

>> Hi  Avinash

>> Perhaps ..! you can use below Array formula with dynamic name range
>> and without any helper column

>> =IFERROR(INDEX(DESIG,MATCH(0,COUNTIF($F$1:F1,DESIG),0)),"")

>> DESIG refers to Dynamic Name Range : =OFFSET(Sheet1!$C$2, 0, 0,
>> COUNTA(Sheet1!$C:$C), 1)

>> Use ctrl+shfit+Enter to enter formula

>> HTH
>> Mahesh

>> On Sat, Jun 2, 2012 at 12:50 AM, Avinash <avinash007pa...@gmail.com>wrote:

>>> Hi NOORAIN,

>>> Thanks for your help man both the formula and macro works fine.

>>> you are just awesome man simply great only 5 lines of code and doing
>>> exceptional task.

>>> salute for your logic and your knowledge.

>>> not just words.... ! :)

>>> simply great.

>>> Regards,

>>> Avinash

>>> On Friday, June 1, 2012 11:17:27 PM UTC+5:30, NOORAIN ANSARI wrote:

>>>> Dear Avinash,

>>>> Please try this macro to create unique list

>>>> *Sub Unique_List()
>>>> Dim rng As Range
>>>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>>>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>>>> Unique:=True
>>>> End Sub
>>>> *

>>>> See attached sheet..
>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>> On Fri, Jun 1, 2012 at 11:08 PM, NOORAIN ANSARI <
>>>> noorain.ans...@gmail.com> wrote:

>>>>> Dear Avinash,

>>>>> Please try it with ctrl+shfit+Enter
>>>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),
>>>>> ROW(A1))-1)*

>>>>> See attached sheet

>>>>> On Fri, Jun 1, 2012 at 10:59 PM, Avinash <avinash007pa...@gmail.com>wrote:

>>>>>> Hi Excel's,

>>>>>> I want to retrieve unique records from the data. 1st preference will
>>>>>> be formula.

>>>>>> otherwise if anyone can help me with the macro or function will also
>>>>>> welcome.

>>>>>> please find attached sheet for better understanding.

>>>>>> Regards,

>>>>>> Avinash

>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)

>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.

>>>>>> 2) Don't post a question in the thread of another member.

>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.

>>>>>> 4) Acknowledge the responses you receive, good or bad.

>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.

>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.

>>>>>> ------------------------------**------------------------------**
>>>>>> ------------------------------**------------
>>>>>> To post to this group, send email to excel-macros@googlegroups.com

>>>>>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>>>>>> googlegroups.com <excel-macros%2Bunsubscribe@googlegroups.com>

>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>> On Friday, June 1, 2012 11:17:27 PM UTC+5:30, NOORAIN ANSARI wrote:

>>>> Dear Avinash,

>>>> Please try this macro to create unique list

>>>> *Sub Unique_List()
>>>> Dim rng As Range
>>>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>>>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>>>> Unique:=True
>>>> End Sub
>>>> *

>>>> See attached sheet..
>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>> On Fri, Jun 1, 2012 at 11:08 PM, NOORAIN ANSARI <
>>>> noorain.ans...@gmail.com> wrote:

>>>>> Dear Avinash,

>>>>> Please try it with ctrl+shfit+Enter
>>>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),
>>>>> ROW(A1))-1)*

>>>>> See attached sheet

>>>>> --
>>>>> Thanks & regards,
>>>>> Noorain Ansari
>>>>> www.noorainansari.com
>>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>>> On Fri, Jun 1, 2012 at 10:59 PM, Avinash <avinash007pa...@gmail.com>wrote:

>>>>>> Hi Excel's,

>>>>>> I want to retrieve unique records from the data. 1st preference will
>>>>>> be formula.

>>>>>> otherwise if anyone can help me with the macro or function will also
>>>>>> welcome.

>>>>>> please find attached sheet for better understanding.

>>>>>> Regards,

>>>>>> Avinash

>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)

>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.

>>>>>> 2) Don't post a question in the thread of another member.

>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.

>>>>>> 4) Acknowledge the responses you receive, good or bad.

>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.

>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.

>>>>>> ------------------------------**------------------------------**
>>>>>> ------------------------------**------------
>>>>>> To post to this group, send email to excel-macros@googlegroups.com

>>>>>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>>>>>> googlegroups.com <excel-macros%2Bunsubscribe@googlegroups.com>

>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>>  --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 4) Acknowledge the responses you receive, good or bad.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com

>>> To unsubscribe, send a blank email to
>>> excel-macros+unsubscribe@googlegroups.com

>>  --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

...

read more »

  Unique Extract.xlsx
15K Download
 
 
 

Unique value formula required

Post by joseph.cam.. » Fri, 15 Jun 2012 10:12:35


Hi Noorain, Maries and Mahesh,

Please explain in detail how each of your formula works.

Thanks,
Joseph
Sent on my BlackBerry? from Vodafone

-----Original Message-----
From: Maries <talk2mar...@gmail.com>

Sender: excel-macros@googlegroups.com
Date: Mon, 4 Jun 2012 03:49:32
To: <excel-macros@googlegroups.com>
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Unique value formula required

Hi,

Also try below array formula,

=IFERROR(INDEX($C$2:$C$25,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$25),0)),"")

On Mon, Jun 4, 2012 at 2:15 AM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:

> Nice Solution Mahesh.........

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

> On Sun, Jun 3, 2012 at 1:53 AM, Mahesh parab <mahes...@gmail.com> wrote:

>> Hi  Avinash

>> Perhaps ..! you can use below Array formula with dynamic name range
>> and without any helper column

>> =IFERROR(INDEX(DESIG,MATCH(0,COUNTIF($F$1:F1,DESIG),0)),"")

>> DESIG refers to Dynamic Name Range : =OFFSET(Sheet1!$C$2, 0, 0,
>> COUNTA(Sheet1!$C:$C), 1)

>> Use ctrl+shfit+Enter to enter formula

>> HTH
>> Mahesh

>> On Sat, Jun 2, 2012 at 12:50 AM, Avinash <avinash007pa...@gmail.com>wrote:

>>> Hi NOORAIN,

>>> Thanks for your help man both the formula and macro works fine.

>>> you are just awesome man simply great only 5 lines of code and doing
>>> exceptional task.

>>> salute for your logic and your knowledge.

>>> not just words.... ! :)

>>> simply great.

>>> Regards,

>>> Avinash

>>> On Friday, June 1, 2012 11:17:27 PM UTC+5:30, NOORAIN ANSARI wrote:

>>>> Dear Avinash,

>>>> Please try this macro to create unique list

>>>> *Sub Unique_List()
>>>> Dim rng As Range
>>>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>>>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>>>> Unique:=True
>>>> End Sub
>>>> *

>>>> See attached sheet..
>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>> On Fri, Jun 1, 2012 at 11:08 PM, NOORAIN ANSARI <
>>>> noorain.ans...@gmail.com> wrote:

>>>>> Dear Avinash,

>>>>> Please try it with ctrl+shfit+Enter
>>>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),
>>>>> ROW(A1))-1)*

>>>>> See attached sheet

>>>>> On Fri, Jun 1, 2012 at 10:59 PM, Avinash <avinash007pa...@gmail.com>wrote:

>>>>>> Hi Excel's,

>>>>>> I want to retrieve unique records from the data. 1st preference will
>>>>>> be formula.

>>>>>> otherwise if anyone can help me with the macro or function will also
>>>>>> welcome.

>>>>>> please find attached sheet for better understanding.

>>>>>> Regards,

>>>>>> Avinash

>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)

>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.

>>>>>> 2) Don't post a question in the thread of another member.

>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.

>>>>>> 4) Acknowledge the responses you receive, good or bad.

>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.

>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.

>>>>>> ------------------------------**------------------------------**
>>>>>> ------------------------------**------------
>>>>>> To post to this group, send email to excel-macros@googlegroups.com

>>>>>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>>>>>> googlegroups.com <excel-macros%2Bunsubscribe@googlegroups.com>

>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>> On Friday, June 1, 2012 11:17:27 PM UTC+5:30, NOORAIN ANSARI wrote:

>>>> Dear Avinash,

>>>> Please try this macro to create unique list

>>>> *Sub Unique_List()
>>>> Dim rng As Range
>>>> Set rng = Application.InputBox("Select a range", , , , , , , 8)
>>>> rng.AdvancedFilter Action:=xlFilterCopy, Copytorange:=Range("G2"),
>>>> Unique:=True
>>>> End Sub
>>>> *

>>>> See attached sheet..
>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>> On Fri, Jun 1, 2012 at 11:08 PM, NOORAIN ANSARI <
>>>> noorain.ans...@gmail.com> wrote:

>>>>> Dear Avinash,

>>>>> Please try it with ctrl+shfit+Enter
>>>>> *=INDEX($D$2:$D$21,SMALL(IF($A$2:$A$21=1,ROW($D$2:$D$21),""),
>>>>> ROW(A1))-1)*

>>>>> See attached sheet

>>>>> --
>>>>> Thanks & regards,
>>>>> Noorain Ansari
>>>>> www.noorainansari.com
>>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>>> On Fri, Jun 1, 2012 at 10:59 PM, Avinash <avinash007pa...@gmail.com>wrote:

>>>>>> Hi Excel's,

>>>>>> I want to retrieve unique records from the data. 1st preference will
>>>>>> be formula.

>>>>>> otherwise if anyone can help me with the macro or function will also
>>>>>> welcome.

>>>>>> please find attached sheet for better understanding.

>>>>>> Regards,

>>>>>> Avinash

>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)

>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.

>>>>>> 2) Don't post a question in the thread of another member.

>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.

>>>>>> 4) Acknowledge the responses you receive, good or bad.

>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.

>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.

>>>>>> ------------------------------**------------------------------**
>>>>>> ------------------------------**------------
>>>>>> To post to this group, send email to excel-macros@googlegroups.com

>>>>>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>>>>>> googlegroups.com <excel-macros%2Bunsubscribe@googlegroups.com>

>>>> --
>>>> Thanks & regards,
>>>> Noorain Ansari
>>>> www.noorainansari.com
>>>> www.excelmacroworld.blogspot.**com<http://www.excelmacroworld.blogspot.com>

>>>>  --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 4) Acknowledge the responses you receive, good or bad.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com

>>> To unsubscribe, send a blank email to
>>> excel-macros+unsubscribe@googlegroups.com

>>  --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of

...

read more »

 
 
 

1. Formula for Unique Values with Condition

Hi All,

In the attached file I have names of the people who left the company.

I need a formula to Extract the names of the Team Leader's and Team
Manager's, where in Column "C" have Agent.

I mean, I want to get only those Names, where they have attrition for
"Agent"

Request you to please help me with a Formula not a Macro..
--
Thanks & Regards,
Kiran

  Unique.xlsx
31K Download

2. Urgent Need for Sr. BI Consultant (Business Intelligence)

3. Formula to Extract Unique Values :

4. US modems in Brazil?

5. Formula required for finding Consumption value for giving cons. Qty.

6. FA: 72pin SIMMs, add tons of memory to your system

7. Excel: Excluding zero values from range of values used in formula

8. Serial line with 115200 bps on Indy?

9. Formula to LOOKUP a value in a table and return the table header value

10. Required Unique Words.

11. Unique column value in Excel

12. Unique values from array

13. any 'simple' way to select count of unique values in a cell range?