Unsure of which formula to use and then probably how to use it

G

gizmoNZ

New Member
Joined
Jul 6, 2012
Messages
2
Forgive me as my question may be quite basic but I am trying to automatically create a new list off a master list in excel...

I have a master list of prospects names and addresses from may towns and cities in a single sheet in excel and I am wanting to copy these to a new spreadsheet within the workbook based on the town they are from...

I am trying to create mailing lists in separate sheets based on their towns and cities and want it to automatically update as new ones are added to the master sheet.

I'd appreciate any help on this as I'm completely stuck on how to do it.
Thanks in advance
Malcolm
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
So let's say your Masterlist is in Sheet1 of the workbook and you want to segregate all the names based on the towns in a different workbook? or in the same workbook but in a different sheet or sheets? More importantly how is your master list set up? Do you have the name of the city, town and the person in the same cell or in different cells. Paste an example of your data and an example of your desired output, it should make things clearer for anyone who can help.
 
Upvote 0
Mindpsyche said:
So let's say your Masterlist is in Sheet1 of the workbook and you want to segregate all the names based on the towns in a different workbook? or in the same workbook but in a different sheet or sheets? More importantly how is your master list set up? Do you have the name of the city, town and the person in the same cell or in different cells. Paste an example of your data and an example of your desired output, it should make things clearer for anyone who can help.
Click to expand...

Each piece of information is in a separate cell as per example below.

Here is an example of the Master List (sheet 1)

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]Street[/TD]
[TD]Suburb[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Smith[/TD]
[TD]32 Smith Street[/TD]
[TD]Smithville[/TD]
[TD]Jonestown[/TD]
[/TR]
[TR]
[TD]Gary [/TD]
[TD]Jones[/TD]
[TD]44 Jones Street[/TD]
[TD]Jonesville[/TD]
[TD]Smithtown[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]Brown[/TD]
[TD]122 Brown Drive[/TD]
[TD]Smithville[/TD]
[TD]Jonestown[/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD]Long[/TD]
[TD]43 Carrathas Street[/TD]
[TD]Albany[/TD]
[TD]Longsville[/TD]
[/TR]
</tbody>[/TABLE]










I want to copy the exact data to a new spreadsheet in the same workbook (sheet 2) but only include one particular city... e.g.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]Street[/TD]
[TD]Suburb[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Smith[/TD]
[TD]32 Smith Street[/TD]
[TD]Smithville[/TD]
[TD]Jonestown[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]Brown[/TD]
[TD]122 Brown Drive[/TD]
[TD]Smithville[/TD]
[TD]Jonestown[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









I hope this explains it a bit better.

I thought about copying the complete master list spreadsheet and creating a filter based on the city field in the new one but this would not update automatically when a new person is added to the master sheet.
 
Upvote 0
Hmm, there are a few things I can think off although they are a bit primitive :/

You could link the cells in Sheet2 to Sheet1 excepting for the one column of towns / cities you dont want. Drag those links down to a high number of rows, so when someone enters anything in sheet one it should show up in sheet2 as well. So if your data started From A2:E2 in sheet1.

In sheet2 A2 you can enter the formula: If(Sheet1!A2="","",Sheet1!A2)
This checks if there is anything in sheet1 A2, if it is blank then Sheet2 A2 will give you a blank, if it is not blank Sheet2A2 will give you the same as Sheet1A2. Just drag this across and down. Make adjustments to your links depending on your required city.

Hope i have been clear.
 
Upvote 0
gizmoNZ said:
Each piece of information is in a separate cell as per example below.

Here is an example of the Master List (sheet 1)

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]First Name
[/TD]
[TD]Surname
[/TD]
[TD]Street
[/TD]
[TD]Suburb
[/TD]
[TD]City
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]Smith
[/TD]
[TD]32 Smith Street
[/TD]
[TD]Smithville
[/TD]
[TD]Jonestown
[/TD]
[/TR]
[TR]
[TD]Gary
[/TD]
[TD]Jones
[/TD]
[TD]44 Jones Street
[/TD]
[TD]Jonesville
[/TD]
[TD]Smithtown
[/TD]
[/TR]
[TR]
[TD]Helen
[/TD]
[TD]Brown
[/TD]
[TD]122 Brown Drive
[/TD]
[TD]Smithville
[/TD]
[TD]Jonestown
[/TD]
[/TR]
[TR]
[TD]Terry
[/TD]
[TD]Long
[/TD]
[TD]43 Carrathas Street
[/TD]
[TD]Albany
[/TD]
[TD]Longsville
[/TD]
[/TR]
</tbody>[/TABLE]










I want to copy the exact data to a new spreadsheet in the same workbook (sheet 2) but only include one particular city... e.g.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]First Name
[/TD]
[TD]Surname
[/TD]
[TD]Street
[/TD]
[TD]Suburb
[/TD]
[TD]City
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]Smith
[/TD]
[TD]32 Smith Street
[/TD]
[TD]Smithville
[/TD]
[TD]Jonestown
[/TD]
[/TR]
[TR]
[TD]Helen
[/TD]
[TD]Brown
[/TD]
[TD]122 Brown Drive
[/TD]
[TD]Smithville
[/TD]
[TD]Jonestown
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









I hope this explains it a bit better.

I thought about copying the complete master list spreadsheet and creating a filter based on the city field in the new one but this would not update automatically when a new person is added to the master sheet.
Click to expand...

Try to adapt post #3 in:

http://www.mrexcel.com/forum/showthread.php?307832

to your problem.
 
Upvote 0
Mindpsyche said:
Hi Aladin, I still am not able to understand the lookup with duplicates construct, is there any other link I could use to help me understand?
Click to expand...

Try to rebuild the set up given the link I quoted. Once you have done that, you could pose questions about the parts that are unclear to you.
 
Upvote 0
gizmoNZ said:
Forgive me as my question may be quite basic but I am trying to automatically create a new list off a master list in excel...

I have a master list of prospects names and addresses from may towns and cities in a single sheet in excel and I am wanting to copy these to a new spreadsheet within the workbook based on the town they are from...

I am trying to create mailing lists in separate sheets based on their towns and cities and want it to automatically update as new ones are added to the master sheet.

I'd appreciate any help on this as I'm completely stuck on how to do it.
Thanks in advance
Malcolm
Click to expand...
See if this helps:

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using a set date to prevent access to Excell tabs and macros
Replies
1
Views
180
Micron
M
O
  • Solved
Copy a sheet which is a template based on a drop down list and rename each copied tab with each value in the drop down list
2 3
Replies
21
Views
1K
dmt32
D
Kaz09
  • Question Question
Copy rows from multiple sheets based on data
Replies
1
Views
505
Fluff
Fluff
B
  • Question Question
Need help
Replies
0
Views
264
blackberry
B
K
  • Question Question
Help with VB Code to copy workbook
Replies
0
Views
428
kizzie37
K

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top