Adding data to multiple specific rows at once

[zarafe]

Hi! I work for a company that manages at large database. Currently I am updating a few fields in the database by name and have a question I am struggling to figure out.

I am searching in an excel list of 4000+ rows for specific people and updating a certain column (let's call it column C). Ideally, I would like to search by the persons email address and then update row C with a certain piece of data.

That sounds confusing so let me explain in this way. I have 4,000 emails in this sheet and would like to be able to search for 20 at a time and instantly replace a different cell in the same row. For example, if I had [email protected], [email protected], and [email protected], I would want to search those email addresses then instantly replace the cell in row C of whatever row those email addresses are in with the word "attended."

Is there an easy way to do this or do I really just have to cntl + F each email address then replace then update each row individually?

Thank you so much!

[dilipandey]

Hi zarafe,

welcome to the forum.

Post a sample data along with your expected results.. may be 2 sheets which show before / after scenarios. thanks.


Regards,
DILIPandey
<click on below * if this helps>

[zarafe]

ID # Name Phone Email Attended
2387532 Joe Smith 2326277 [email protected]
1234215 Sarah H 2742788 [email protected]
2627347 Beyonce 3583573 [email protected]
135262 Jay Z 4586448 [email protected]
272621 Barack Obama 4686421 [email protected]



in theory, i'd be able to search all these emails at once and immediately add data to the attended column.

There are 4000 rows such as this and I'd like to be able to take a chunk of emails and change their attended status without searching each individual email.

[tigeravatar]

zarafe,

Welcome to the forum!
Attached is an example workbook based on the criteria you described.
There are two worksheets, the first one is named "Database", the second one is named "Update List"

In sheet "Database":

• Row 1 is a header row so actual data starts on row 2
• Column C is what will be updated to the word "Attended" for matching emails"
• Column F contains email addresses
• The ones that will be updated are highlighted yellow to make them easier to find by hand
• You'll notice there is a button with the text "Run Macro", that button will run the macro code provided below

In sheet "Update List":

• This sheet is intended only to be used for the entry of email addresses that you want to find in sheet "Database"
• As such, there is no header row and data starts on row 1
• Column A contains the list of email addresses to find so that their respective column C entries can be updated to "Attended"

The "Run Macro" button will run this code when clicked:

Please Login or Register  to view this content.

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one tgr)

[dilipandey]

Hi Zarafe,

You need to replace the email with world "attended" OR you need a "yes"or "no" in column with heading "attended" ?
Also what I need to now if your data is in column A,b,c,d,e where emails are in column d - so from where you are matching i.e., there could be emails in column lets say in column H and if those emails found in column D then replace those emails or write "yes"under column "attended".

Regards,
DILIPandey
<click on below * if this helps>

[zarafe]

The emails are in column L and the "attended" column is column D.

I want to find emails in column L (all at once, ideally separated by a comma) and then automatically input "yes" in to column D. Does this make sense?

Thanks again!!

[dilipandey]

Still not clear to me... you may have 4000 email addresses.. now what all emails to be found in the list of 4000 emails ? OR you are finding any email here -which may relate to a case => if character "@" and ".com" found in a text then this can confirm that as an email and hence proceed with the status as "email found"

Above is just an example - to make my understanding clear and I know email can be end up with .com, .org, .co.uk, .edu etc.... thanks.

Regards,
DILIPandey
<click on below * if this helps>

[zarafe]

So the problem is that it varies for event. Basically I have about 25 different events and I am trying to label each one.

Let me know if this makes sense to you. My programming knowledge is limited but here what i'd love to do:

If value in column L= "[email protected] OR [email protected] OR [email protected]" <----list all email addresses I am searching for here
then assign the cell number (with email address) to CELLNUMBER <----program would save cell number with email address here
then input "valley dinner" to CELLNUMBER <----program would then put name of event attended into cell number

the program would do this for every email address I initially put in.

Does this make any sense?

[tigeravatar]

zarafe, have you had a chance to look at my suggestion? It does basically exactly what you've described (though I guessed column F was where the email addresses were instead of column L, should be easy enough to adjust)

[zarafe]

I tried to run to module to see what would happen after changing the row letter but it didn't run and keeps telling me to debug.

[tigeravatar]

Can you upload a sample file that is experiencing the error?

[zarafe]

It's difficult because the database has a lot of personal information. I am trying to come up with a way to demonstrate what I mean without showing the list.

[tigeravatar]

Can't you just alter the sample file I already provided?

[zarafe]

So sorry! I ran your test module and it did exactly what I wanted. How would I use that module to to input the email addresses I wanted to search?

I can put the email addresses on a separate excel sheet that I wanted to modify in the big one. Would that help?

[tigeravatar]

Ideally, you would be able to add the code to your own workbook. In your main workbook you would add a sheet named "Update List" and in there starting in A1 and going down is where you would enter the email addresses you want to update.
Here's the code, slightly altered to accept input for the word you want to update the rows to (so it will ask and you can type in Attended or Valley Dinner), with comments to help you adjust it to your needs:

Please Login or Register  to view this content.

[zarafe]

I've got it running but not operating. The cell is blank in the sheet "database" does this matter?

[zarafe]

Whoops. Nevermind. Thank you and you are a livesaver!!!!

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
How to mark a thread Solved
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix"
Change to "Solved"
Click Save