Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Another Match And Replace Problem

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Ok,

I've got a spreadsheet that contains some script names, email subjects and email addresses...

The promblem is the emails are all over the place. I want to sort them into rows and assign them to the correct subject line and script...

I've attached a basic exapmple of how the data is (Unsorted sheet) and how I want it to eventually look (sorted sheet)


Is there a function i can put into the sorted sheet to search each row in the unsorted sheet and then enter the data correctly into the sorted sheet..
I want the email ID and email name to be returned in the G & H columns of the sorted sheet.

Apologies if this doesn't make sense... In truth the sheet I'm working on has 2700+ entries and this would help me out a lot


Similar Excel Video Tutorials

Helpful Excel Macros

Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete

Similar Topics







Hi,

I populate an Excel sheet with data from an SQL database and I want to be able to check rows for certain data.

If the email address is duplicated I need to check the ID and if that is different then I want to highlight that row.

EMail ID ROW
EMail@1 5555 1
Email@2 4444 2
EMail@2 4443 3
EMail@2 4444 4
EMail@3 3333 5
EMail@3 1111 6
EMail@2 4449 7
EMail@4 9999 8
EMail@3 3333 9
Email@1 5555 10

So row 3,6 and 7 need to be highlighted.

One thing of note is the sheet can not be sorted in anyway.

So i'm basically trying to identify anyone who has the same email address as someone else. If it's the same person then I don't want them highlighted.

I hope someone can help and sorry if it's not explained very well.

Thanks.


Hey guys,
Not sure if I'm posting this in the right place but here it goes..

I have an excel spreadsheet which contains scriptnames, subjects and email addresses.

Column A will always be scriptname and column B will always be the subject.
C onwards contain the email addresses.

I'm trying to sort this spread sheet so that each email address will have its own column assigned to it.
Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D.

The problem is I've around 1200 rows of infor and the email addresses are all over the place..

I've no idea how i'd do this and I aint to good on formulas or macros...

Can anhone help?


Hi,
I want to create a macro which will automatically sort the data based on one cell word & move the sorted data from that column to the sheet in which the word is there. As the data is huge & we have to manually sorting & move it to the respective sheets as we have more than 30 sheets which is a time consuming process.
For example:
All data is there in Column A from A2. When I select one word i.e. gmail.com in E1 from the dropdown then all email ids which are ENDING with gmail.com should be sorted out automatically in the Column A in the sheet & these email ids should be moved automatically to the sheet in which we have placed the word gmail.com in cell A1 & the moved email ids in this sheet should be start from A2. After moving the email ids to the respective sheet it should make sure that there should no email ID repeated in that sheet (all email id should be unique no duplicate email id) should be repeated.
After that email ids the blank cells should be automatically filled up i.e. if I move email id from A5 then the email id in A6 should be automatically moved to the A5 cell and so on for the remaining blank cells.
All this I want to create with the help of macro & not with the use of button.
Thanks in Advance.


I have just done a mail merge for my company to 1300 existing clients (not spam honestly!). As most had been neglected for some time, many email addresses are out of date so the emails bounced back. I now have 106 email addresses in an Excel column. In another worksheet, I have all the email addresses for all client contacts and a reference number for each company. Each row contains the reference number in column A and the email addresses in either column V or AB.
I need to find the reference number and enter it against each email address.
I have tried a vlookup but just get errors as I am not that confident it is what I should be using!
What I have:
sheet: email
Column A email addresses
Sheet: data
Column A reference number, Column V: contact1 email, Column AB: Contact2 email
I need to put the reference number in the email sheet in column B against the email address

It's Friday and my brain hurts so any help would be gratefully received.

Thaks
Simon

I've attached a sample workbook.

I have a list of names with some background information. Three sheets, each representing a different time period and different test score.

On the first sheet, I have painstakingly collected the email address of each person. I've attached a few, but it's actually a few hundred.

On the second sheet, I have a few email addresses, but not all of them. However, there is some overlap in the names, so I may have already looked up the email addresses for some of the names in Sheet 1. So as you notice, Jet Li is already on Sheet 1, in Row 6. I would like to arrange it so that in Sheet 2, *if* the name is already in Sheet one, insert the email address. Note that on Sheet 2, Jet Li is on Row 11.

I also have Sheet 3, which may have some overlap with the first two sheets. I want to insert all the email addresses, if available, into the third sheet. So note that on Sheet 3, Jet Li is again there in Row 11. So is Tanya walker, who was in Sheet 1, and Vince Brood, from Sheet 2.

Note that names are in different rows. That's what I'm having trouble getting over, since the IF formula works if it's in the same row.


Hi fellas

I'm using 2007 and automating emails from a sheet. I've got a sub that pastes a range from any given sheet into the body of an email etc. This works well.

I have 97 identical sheets (numbered 1 to 97 as sheet names), and I have a sheet with a range of numbers that correspond to these sheet names. Two cells to the right of each number is a validated cell that allows you to select "EMAIL!" or leave blank as follows:

26 EMAIL! 1 28 2 EMAIL! 3 5 4 EMAIL! 6 7 8 EMAIL! 25

I am trying to put together a sub that loops through each cell in the EMAIL! range - so something like:

For Each cell In Range("H12:H108")
If cell.Value = "EMAIL!" Then...

I would like to activate the sheet that corresponds to the value of the corresponding cell to the left, then run the Macro for each corresponding sheet and return to the range and continue looping through.

I'm grateful as always for any help!
Wilco


I have a database of clients on a spreadsheet, one column contains email addresses which are hyperlinked to send emails when I click on it.

However as I add new clients and sort them alphabetically 'Sort Ascending'. The value of the cell is moved along with the rest of it's row, but the hyperlink for that cell remains the same. So email hyperlinks need to be reformated each time new clients are added and sorted in.

Basically the cell says the correct email but does not link to that email when I click on it.

Any ideas on how I can fix this?


Ok, this is kindof a long explanation:

I have two worksheets with data that kind of looks like this:


Sheet 1
A/B/C/D/E

Sheet 2
A/F/G/H/I

Now what I want to do is combine this sheet into A/B/C/D/E/F/G/H/I

Complicating matters is the fact that I have had to go and custom sort Sheet 1 so now the order of the data in Sheet 1 and 2's 'A' does not line up

What I need is to find a way to go through each cell in Sheet 1, column A and then match it against Sheet 2, column A and then populate the data from F-I into Sheet 1

I'm not sure if a macro or a formula will do it... Help!

-K


Hi Everyone,

I am looking for a bit of help on a macro. I have found one on the web which I need to change slightly. Not being very technical with all this I wonder if someone could tell me how.

Firstly I want to edit this script so it goes to a list of recipients as a BCC not in the "to" field of the email. Also I want to make it go to a list of addresses I will collate in a new worksheet. I will probably have different lists on different worksheets so I need to amend which list it goes to when I send it.

I also use an email to fax service so would therefore like to be able to change the Subject field in the email as this is where I have to enter the passwords for a fax to email service.

Finally I want to set up a seperate button for printing a particular worksheet (not the active one), and change which printer this is sent to.

Sorry for all questions, but really am stuck.

I have uploaded my sample I have made attempts at he http://www.neilfearnley.co.uk/excel/Quote Sheet.xls
The script is run from "Email" button on Materials Sheet (I will eventually add these to the other sheets too)

Thank you for your help in advance.


Neil

PS: I will be needing to run this on XP and Vista machines too.


Sorry, I have to post this again, I could not find away of deleteing the old post. So apologise for the post.

I have looked at this problem and what details i tried to explain and realised that it made not a lot of sense.

I have attached a example spreadsheet with a unsort sheet and a sorted sheet.

I will now try and explain what im looking for.

If you look at the example, the yellow columns represent lock columns. Take into consideration that the spreadsheet is also protected, but with the sort function enabled. So i can olny sort the date, index, start and end columns and also add info and comments, but they need to be sorted at the same time. I can sort the first 4 columns, but the other two need to be sorted aswell. Is there a way of connecting the last to columns so that when the first four are sorted they follow suite.

How this and my example explains bettre what im trying todo

G


Hi,

So I have my email macro working fine, however I am adding another email feature. Currently I use the email function to send the saved output file to HR or Payroll to import into the database. However on my sickness reporting form I want it to send an email to all the staff on the sheet that are reported as sick.

Their email address are held in column 9 in the range("STAFF") I also have a lookup that returns their email addresses on the sheet ("OUTPUT") in column 9.

So I understand how the email works but what I really want to know is how I can make a list from column 9 on the output sheet and send emails to these people. The main issue I can see is where there are multiple lines for 1 employee, I would only want 1 email going to them.

Also is there a way I can insert their name on to the email that is sent to that individual rather than just having a generic email sent out?


Hi guys,

I currently have 2 spread sheets. Spreadsheet A has x amount of emails.

Spreadsheet B has x amount of emails including all of the emails in Spreadsheet A. It also includes information about all of the emails.

I need to find all emails in Spreadsheet A in Spreadsheet B including there relevant data. but i cant seem to do it.

Its a simple task but the only way I can get it working is by using the Find feature in spreadsheet B and typing in the email and copying and pasting the relevant cells. This would be fine but there are several thousand emails in Spreadsheet A and many more in Spreadsheet B

Kind Regards,
Sam


I have an excel spreadsheet that I download from a third party server that contains a single sheet with makes, models, vins and prices of used vehicles sold in my company. I would like to create a script/macro that will take this information and filter it by the make (in column B on the USEDCARS sheet) column and copy those rows that contain that make to a separate sheet that the script will create and label it the name of the make of car. The makes of vehicles that the script needs to filter by are AUDI, BMW, MB, MINI and all other make appearing in that column are copied to a new sheet called OTHER. Then I would like this spreadsheet to compare its rows to another spreadsheet called "sorted used inv" and add any new rows no present in sorted used inv to the sorted used inv other page. Any help would be much appreciated.


hi
i have searched the site for this and have not had much luck
here is my scenario;
i have a workbook with 2 sheets
on Sheet 1 is a list of names(alphabetical) at A4 to A50
B4 to AH4 contain a value, a total of those values is in AP4:AH50.
this is working correctly.
on Sheet 2 i will have a list of the same names in A4:A50
in B4:B50 there will be a Total of C3:T3.(from Sheet 1, AP4:AH50)
i wish to sort the names on Sheet 2 based on the number(highest)
from Sheet 1 AP4:AP50
basically i am trying to make sure the total from Sheet 1 (sorted alphabetically)goes to the
correct person on Sheet 2(sorted by total).
i have just been cutting and pasting this info one row at a time, and would like this to be automatic
any ideas or suggestions would be appreciated.
thanx

Mac


Hi Everyone,

Bit of a tall order here, but hope someone can help.

I am looking for a bit of help on a macro. I have found one on the web which I need to change slightly. Not being very technical with all this I wonder if someone could tell me how.

Firstly I want to edit this script so it goes to a list of recipients as a BCC not in the "to" field of the email so they cannot see who else it has gone to (various suppliers). Also I want to make it go to a list of addresses I will collate in a new worksheet. I will probably have different lists on different worksheets so I need to choose which list it goes to when I send it.

I also use an email to fax service so would therefore like to be able to change the Subject field in the email as this is where I have to enter the passwords for the fax to email service so they know it is genuine.

Finally I want to set up a seperate button for printing a particular worksheet (not the active one), and change which printer this is sent to.(Not necessarily the dafault printer)

Sorry for all questions, but really am stuck and I have had an attempt at it!

I have uploaded my sample I have made attempts at he http://www.neilfearnley.co.uk/excel/Quote Sheet.xls
The script is run from "Email" button on Materials Sheet (I will eventually add these to the other sheets too)

Thank you for your help in advance.


Neil

PS: I will be needing to run this on XP and Vista machines too.


Hi,

I've got a problem with a sheet that I could do with some help on please.......

I have a list of email addresses that have been used on one of our systems. Some of the addresses have been used in an incorrect format that's incompatible with another system. I have a list o f 7000 email addresses (all internal to our organisation), the majority of which are in the correct format (lower case) eg: my.name@yahoo.com. Also included are some incorrect email address formats in upper case eg: My.Name@yahoo.com.
What I'd like to do is sort the list of email addresses (all in Column A) so that the result comes out sorted on the case of the first character of the email address.

If I have these email addresses prior to the sort:

john.smith@msn.com
john.williams@msn.com
Bill.Jones@msn.com
Bob.Brown@msn.com
John.Smith@msn.com
Will.Young@msn.com
Alan.Green@msn.com
alan.brown@msn.com

is there any way I can sort them so the results are shown by case then sorted within the case?

So on the above addresses I'd like to get the results where the same case addresses are together like:

Alan.Green@msn.com
Bill.Jones@msn.com
Bob.Brown@msn.com
John.Smith@msn.com
Will.Young@msn.com
alan.brown@msn.com
john.smith@msn.com
john.williams@msn.com


Many thanks for any help,

Banco


Hi,

I have about 500 sheets in excel. each sheet is named with the managers name. John, Jim, Etc, each sheet has to be emailed as an attachment to the respective manager, That is sheet John has to be email to John@gmail, Sheet Jim has to be emailed to Jim@yahoo.com etc.,

I have all the sheet names and their email address in one master sheet.
So some kind of vlookup has to be done between the sheet names and the email address.

When I run a macro 500 emails has to be sent according to the sheet names.

Appreciate your helps.


My previous two questions have been about ranking numbers and assigning point values to those ranks. But now I want to take some of the data on one sheet and have it show up on another...but there's a catch...

I don't know exactly where I want the data to show up on the second sheet, so I can't just have certain cells on the second sheet point to cells on the first sheet. I have multiple sections on the first sheet that are going to have a different number of entries depending on the test, but I want data from each of the entries from the first sheet to show up on the second sheet in one continuous region that doesn't differentiate from which section the data came from and sorts the data based on one of the columns.

For example: Test #1 has 5 entries in the first section and 12 in the second section. On the second sheet I want 17 rows showing certain data from each entry, that is sorted by the weight of the person. Test #2 has 7 entries in the first section and 15 in the second section. So the second sheet would have 22 rows showing the same data from the new entries, and is also sorted by the weight of the person. And eventually there would be as many as 45 different sections on the first sheet.

Does that make sense?

Thanks for any help.


Guys I know I am going to appear very dim here but i do hope some one can help.

Basically I have a 4 sheet workbook in MS office 2010, contained within each are approx 40 colums and 200 rows with various text and values. What I want to do is to create a sort sheet for each ie: sheet1 will have a sheet1 sorted sheet, sheet 2 will have a sheet2 sorted sheet etc. In effect a dump sheet for the data which automatically updates the sorted duplicate sheet. I wish to sort by 1 column only (column N) omitting the 1st 2 rows form the sort criteria.

I value any help greatly and thankyou in anticipation.

s2m


Some time ago I created an Excel sheet with a column for only email addresses; live hyperlinks ( mailto:..... ). The column contains nearly 2,000 addresses. Last week when I opened the sheet all of the cells in the email column become invisible.
Not all the rows have email addresses.

The mouse recognizes the links and opens Outlook when the seemingly empty cell (with the link) is clicked, so I know the email address are there, I just can't see them. The kicker is that Excel doesn't recognize the cell as being populated; even though the mouse does!

I tried Filter, Data Sort, Replace w/..., Cut/Paste to new sheet & even to Word, Note, etc... I even tried changing text & cell color, but NOTHING works to get the email addresses to appear.

When I right click to Edit Hyperlink, and add a space or any character in the Text to Display box, Email address, or the Subject boxes the full Mailto: xxx@xxx.com hyperlink APPEARS!.

This is driving me nuts...could someone PLEASE offer some advice on how to get ALL the email addresses to appear? I'm not that great with Macro's, but I thinking it's something stupid I did.
Thank you for your help.
Cheers,
TokyoJ


Hope someone is able to advise my excel skills are very basic having spent some time trying to do this manually it is unbelievably time consuming and could do with figuring out how to do automatically, thanks in advance.

Here is my problem.

i have an old database with roughly 7000 of our company contacts on which im trying to clean up before putting onto our crm software. I have run the sheet through an email address checker which has come up with a list of bad email addresses where people have either left the company or the email has been added incorrectly. This is on a separate sheet but contains only the email and not any corresponding information

Basically i want to copy every row that has one of these bad email addresses on the main spreadsheet onto another spreadsheet and then delete these from our main sheet so they can be gone through manually another time.

Is there any way i can come up with some sort of formula or macro that can do this automatically for me.

If anyone can help it would be most appreciated. Please let me know if you need any more info.

Thanks

Chris


Hi Guys,

I need your help, and I hope I'll make myself clear. So, I have to
workshhets. In sheet A there is a database, and one of the columns consists
of different email addresses. In sheet B, there is another database, and one
of the columns is again containing email addresses. Some of them match with
the column from sheet A. Now on sheet 2, to each email address corresponds a
certain PIN number. What I want to do is: search if each of the email
addresses from Sheet A is found on sheet B, and if it is, then I want to
return in a cell the PIN number corresponding to it.

sheet A sheet B

a@a.com m@m.com 434
b@b.com r@r.com 545
c@c.com b@b.com 655
d@d.com a@a.com 434

So, in the above example, I need to see if a@a.com appears in the column
from sheet B, and then to return the # 434 in a cell next to the entry from
sheet A.

I hope I made myself clear enough. Help please!




Hi Guys,

I need your help, and I hope I'll make myself clear. So, I have to
workshhets. In sheet A there is a database, and one of the columns consists
of different email addresses. In sheet B, there is another database, and one
of the columns is again containing email addresses. Some of them match with
the column from sheet A. Now on sheet 2, to each email address corresponds a
certain PIN number. What I want to do is: search if each of the email
addresses from Sheet A is found on sheet B, and if it is, then I want to
return in a cell the PIN number corresponding to it.

sheet A sheet B

a@a.com m@m.com 434
b@b.com r@r.com 545
c@c.com b@b.com 655
d@d.com a@a.com 434

So, in the above example, I need to see if a@a.com appears in the column
from sheet B, and then to return the # 434 in a cell next to the entry from
sheet A.

I hope I made myself clear enough. Help please!




Hi,
I have a vlookup formula in sheet 1 and in sheet 2 with the list of
names column A and column B with their respective email addresses.

I had the email addresses with hyperlinks in sheet 2.
But when I look up a name on sheet 1, it's not hyperlinked.
I tried hyperlinking it on sheet 1 but when I type in a different name
the email address does not change with the corresponding name.

thanks
youth




Hello,

With a bit of luck this will be quite simple!

The problem:
I have 2 spread sheets, on contains unsorted data in various columns and rows, the other sorted data but one column contains references to the cells on the other sheet, Ie A1, G4 etc, what I need is a formula that I can put in the column next to the cell references in the sorted data, I need that formula to retrieve the data from the unsorted data using the reference from the previous Column...

I tried and couldnt find a solution.

Help appreciated,