Extract Names from different sheet into one sheet

Extract Names from different sheet into one sheet

Post by ashish kou » Fri, 08 Jun 2012 02:05:29



try this updated one

On Wed, Jun 6, 2012 at 10:26 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:

> Hi Hilary,

> Please check this one.

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

> www.excelmacroworld.blogspot.com

> On Wed, Jun 6, 2012 at 10:12 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:

>> Nice One****

>> ** **

>> * *

>> *Regards*

>> *Rajan verma*

>> *+91 7838100659 [IM-Gtalk]*

>> ** **

>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *ashish koul
>> *Sent:* 06 June 2012 10:00
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Extract Names from different sheet into
>> one sheet****

>> ** **

>> please find the updated file as it will treat first country name as field
>> name . so i have added header in each sheet.****

>> ** **

>> Thanks ****

>> ** **

>> ** **

>> ** **

>> On Wed, Jun 6, 2012 at 9:43 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>
>> wrote:****

>> Hi hilary,****

>> ** **

>> Sorry, I miss understood, Please use it****

>> ** **

>> Sub extract_uniquelist()****

>> Dim ws As Worksheet****

>> Dim i As Integer****

>> Dim j As Integer****

>> Dim rng As Range****

>> Set ws = ActiveWorkbook.Sheets(1)****

>> For i = 2 To Sheets.Count****

>> Set rng = Sheets(i).Range("B4:B" &
>> Sheets(i).Range("B65536").End(xlUp).Row)****

>> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1****

>> rng.Copy ws.Cells(j, "B")****

>> Next i****

>> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1***
>> *

>> End Sub****

>> ** **

>> ** **

>> ** **

>> On Wed, Jun 6, 2012 at 9:26 PM, dguillett1 <dguille...@gmail.com> wrote:*
>> ***

>> Why not use a macro to gather all and then do a unique on the gathered
>> list?****

>>  ****

>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com****

>>  ****

>> *From:* hilary lomotey <resp...@gmail.com> ****

>> *Sent:* Wednesday, June 06, 2012 10:39 AM****

>> *To:* excel-macros <EXCEL-MACROS@googlegroups.com> ****

>> *Subject:* $$Excel-Macros$$ Extract Names from different sheet into one
>> sheet****

>>  ****

>> Hello Experts, ****

>>  ****

>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
>> possible to have a formula to extract all these names into one column in
>> sheet 1. thanks ****

>> --
>> 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****

>> ****

>> ** **

>> ****

>> ** **

>> --
>> 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****

>> ****

>> ** **

>> -- ****

>> *Regards*****

>> * *****

>> *Ashish Koul*****

>> *http://www.excelvbamacros.com/*****

>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/> ****

>>  ****

>> P Before printing, think about the environment.****

>>  ****

>> ** **

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

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

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

--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

  Extract names from other sheets to one sheet.xlsm
31K Download
 
 
 

Extract Names from different sheet into one sheet

Post by hilary lomote » Fri, 08 Jun 2012 02:09:21


THanks Ashish,

Very grateful for your assistance. working now

On Wed, Jun 6, 2012 at 5:05 PM, ashish koul <koul.ash...@gmail.com> wrote:
> try this updated one

> On Wed, Jun 6, 2012 at 10:26 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:

>> Hi Hilary,

>> Please check this one.

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

>> www.excelmacroworld.blogspot.com

>> On Wed, Jun 6, 2012 at 10:12 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:

>>> Nice One****

>>> ** **

>>> * *

>>> *Regards*

>>> *Rajan verma*

>>> *+91 7838100659 [IM-Gtalk]*

>>> ** **

>>> *From:* excel-macros@googlegroups.com [mailto:
>>> excel-macros@googlegroups.com] *On Behalf Of *ashish koul
>>> *Sent:* 06 June 2012 10:00
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ Extract Names from different sheet into
>>> one sheet****

>>> ** **

>>> please find the updated file as it will treat first country name as
>>> field name . so i have added header in each sheet.****

>>> ** **

>>> Thanks ****

>>> ** **

>>> ** **

>>> ** **

>>> On Wed, Jun 6, 2012 at 9:43 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>
>>> wrote:****

>>> Hi hilary,****

>>> ** **

>>> Sorry, I miss understood, Please use it****

>>> ** **

>>> Sub extract_uniquelist()****

>>> Dim ws As Worksheet****

>>> Dim i As Integer****

>>> Dim j As Integer****

>>> Dim rng As Range****

>>> Set ws = ActiveWorkbook.Sheets(1)****

>>> For i = 2 To Sheets.Count****

>>> Set rng = Sheets(i).Range("B4:B" &
>>> Sheets(i).Range("B65536").End(xlUp).Row)****

>>> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1****

>>> rng.Copy ws.Cells(j, "B")****

>>> Next i****

>>> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1**
>>> **

>>> End Sub****

>>> ** **

>>> ** **

>>> ** **

>>> On Wed, Jun 6, 2012 at 9:26 PM, dguillett1 <dguille...@gmail.com> wrote:
>>> ****

>>> Why not use a macro to gather all and then do a unique on the gathered
>>> list?****

>>>  ****

>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com****

>>>  ****

>>> *From:* hilary lomotey <resp...@gmail.com> ****

>>> *Sent:* Wednesday, June 06, 2012 10:39 AM****

>>> *To:* excel-macros <EXCEL-MACROS@googlegroups.com> ****

>>> *Subject:* $$Excel-Macros$$ Extract Names from different sheet into one
>>> sheet****

>>>  ****

>>> Hello Experts, ****

>>>  ****

>>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is
>>> it possible to have a formula to extract all these names into one column in
>>> sheet 1. thanks ****

>>> --
>>> 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****

>>> ****

>>> ** **

>>> ****

>>> ** **

>>> --
>>> 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****

>>> ****

>>> ** **

>>> -- ****

>>> *Regards*****

>>> * *****

>>> *Ashish Koul*****

>>> *http://www.excelvbamacros.com/*****

>>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/> ****

>>>  ****

>>> P Before printing, think about the environment.****

>>>  ****

>>> ** **

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

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

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

> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

> P

...

read more »