Interesting - Send gifts to members :)

[ellen12]

Hi everyone I would like to seek your help in doing a macro. I have four sheets:

1. The first one is a list of all members and their score.
2. Sheet 2 is populated as users fill a optin form on our website. They'll be registering for a free gift which will be sent to them on scoring more than 100.
3. Sheet 3 will be empty and will be filled up by the macro.
4. Sheet 4 is the list of members to whom the gift email has been sent.

I want to send the gift email to the opted members who scored more than 100.

My idea is:

• Macro must first, check if the usernames listed in sheet 2, have scored greater or equal to 100 (in sheet 1).
• Then, check if the username/email is not already listed in sheet 4.
• Finally once the above two conditions are met, copy the filtered rows to 3rd & 4th sheet.

I will send the gift to emails listed in sheet 3 and clear the sheet. Both sheet 1 & 2 will will be updated frequently and emails will be sent after each update.

EXAMPLE:

Sheet 1 (Main database)

Book1
	A	B
1	Username	Score
2	a	150
3	b	40
4	c	90
5	d	100
6	e	110
7	f	105
Sheet1

Sheet 2 (Optin form: Users who have registered for a free gift which will be sent to them on scoring more than 100.)

Book1
	A	B
1	Username	Email
2	a	a@xyz.com
3	b	b@xyz.com
4	d	d@xyz.com
5	e	e@xyz.com
6	z	z@xyz.com
Sheet1

Sheet 3 (Outbox: Gifts must be sent to these members. This list will be cleared once emails are sent.)

Book1
	A	B
1	Username	Email
2
Sheet1

Sheet 4 (Sent Emails: List of members who received the gift.)

Book1
	A	B
1	Username	Email
2	a	a@xyz.com
Sheet1

Macro Result:

Sheet 3

Book1
	A	B
1	Username	Email
2	d	d@xyz.com
3	e	e@xyz.com
Sheet1

Sheet 4 (d & e are newly added)

Book1
	A	B
1	Username	Email
2	a	a@xyz.com
3	d	d@xyz.com
4	e	e@xyz.com
Sheet1

If anyone has a better idea to simplify the process, please share.
Please help me with this, I will be thankful to you forever.

 

[kpark91]

There seems to be one information you have forgotten to mention.

Sheet4 needs to have record of people you already have sent gifts to, so how will the macro recognize the gift has been sent? If sending gift cannot be electronically recognized, you would need to do some manual work :P

But filling up sheet3 won't be a problem.

Lastly, one suggestion would be to merge Sheet1 with Sheet2. So that you have 3 columns consisting of {Username, Score, Email} and have a system where you would put "N/A" for people who haven't filled in their emails. This way, it's easier for human to read and computer to process (less resources needed)

 

[AlphaFrog]

I think you could do this on one sheet with something like this. The red cells are users owed a gift. Put an x in the red cells when the gift is sent.

Excel Workbook
	A	B	C	D
1	Username	Score	Registered Email	Gifted
2	a	150	a@xyz.com	x
3	b	40	b@xyz.com
4	c	90	c@xyz.com
5	d	100	d@xyz.com
6	e	110	e@xyz.com
7	f	105
8	z		z@xyz.com
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	1. / Formula is =AND(B2>=100,C2<>"",D2="")	Abc
D3	1. / Formula is =AND(B3>=100,C3<>"",D3="")	Abc
D4	1. / Formula is =AND(B4>=100,C4<>"",D4="")	Abc
D5	1. / Formula is =AND(B5>=100,C5<>"",D5="")	Abc
D6	1. / Formula is =AND(B6>=100,C6<>"",D6="")	Abc
D7	1. / Formula is =AND(B7>=100,C7<>"",D7="")	Abc
D8	1. / Formula is =AND(B8>=100,C8<>"",D8="")	Abc

 

[PaddyD]

FWIW, if you're emailing a 'gift' you could have excel do that too, in principle...

 

[kpark91]

I think you could do this on one sheet with something like this. The red cells are users owed a gift. Put an x in the red cells when the gift is sent.
http://www.excel-jeanie-html.de/index.php?f=1

Even better! :D

 

[ellen12]

There seems to be one information you have forgotten to mention. Sheet4 needs to have record of people you already have sent gifts to, so how will the macro recognize the gift has been sent?

I haven't sent emails to anyone yet. I will start sending once the macro is ready.

Lastly, one suggestion would be to merge Sheet1 with Sheet2. So that you have 3 columns consisting of {Username, Score, Email} and have a system where you would put "N/A" for people who haven't filled in their emails. This way, it's easier for human to read and computer to process (less resources needed)

How can I merge them?

 

[ellen12]

I think you could do this on one sheet with something like this. The red cells are users owed a gift. Put an x in the red cells when the gift is sent.

Okay. The opted list and the full member list will be on separate sheets. How to merge them (sheet 1 & 2)?

 

[AlphaFrog]

Okay. The opted list and the full member list will be on separate sheets. How to merge them (sheet 1 & 2)?

Sheet1 (Member list with scores and merged data with Sheet2)

Excel Workbook
	A	B	C	D
1	Username	Score	Registered Email	Gifted
2	a	150	a@xyz.com	x
3	b	40	b@xyz.com
4	c	90
5	d	100	d@xyz.com
6	e	110	e@xyz.com
7	f	105
8	z		z@xyz.com
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	1. / Formula is =AND(B2>=100,C2<>"",D2="")	Abc
D3	1. / Formula is =AND(B3>=100,C3<>"",D3="")	Abc
D4	1. / Formula is =AND(B4>=100,C4<>"",D4="")	Abc
D5	1. / Formula is =AND(B5>=100,C5<>"",D5="")	Abc
D6	1. / Formula is =AND(B6>=100,C6<>"",D6="")	Abc
D7	1. / Formula is =AND(B7>=100,C7<>"",D7="")	Abc
D8	1. / Formula is =AND(B8>=100,C8<>"",D8="")	Abc

Sheet2 (Opted List)

Excel Workbook
	A	B
1	Username	Email
2	a	a@xyz.com
3	b	b@xyz.com
4	d	d@xyz.com
5	e	e@xyz.com
6	z	z@xyz.com
7
Sheet