Formula or macros to pull data into new sheet based on "yes/no" criterion

[daniellelp]

Hi all,

Thank you in advance for any guidance you may provide. I have a spreadsheet with these headers and these fake data:

Unique ID	Open House Date	Attended?	lastName	firstName	email	address1	address2	city	state	country
1	6/1/13	yes	morgan	piers	pm@xx.com	101st street		New York	NY	USA
2	6/1/13	yes	walters	barbara	bw@xx.com	east street		Philadelphia	PA
3	6/1/13	no	stewart	jon	js@xx.com	west st	apt. 5	Arlington	VA
4	6/1/13	no	o'brien	conan	cob@xx.com	north st		Birmingham	AL	USA
5	6/1/13	yes	beck	glenn	gb@xx.com	1st ave		New Orleans	LA
6	6/1/13	yes	degeneres	ellen	ed@xx.com	elm street	9B	St. Louis	MO
7	6/1/13	no	colbert	stephen	sc@xx.com	broadway	apt. 2c	Honolulu	HI	United States
8	6/1/13	yes	winfrey	oprah	ow@xx.com	maple lane		Saddle River	NJ
9	6/1/13	no	handler	chelsea	ch@xx.com	columbus circle	Apt 105	San Diego	CA
10	6/1/13	no	ferguson	craig	cf@xx.com	love lane		Detroit	MI	US

<tbody>
</tbody>

The data get updated every couple weeks and are assigned a new, sequential, unique identifier (the spreadsheet has unique IDs listed 1-900, the new data is copy-pasted at the bottom of the existing data; not ideal, but the way the boss wants it). This unique ID is used in other vlookup formulas in the same file.

My question is: I would like to automatically pull the firstName, lastName, address1, address2, city, state, and country into another sheet ("Mailings") in the same workbook IF the individual has a "yes" in the "Attended?" column. IF the individual has a "no" in the "Attended?" column, I would like to pull that person's firstName, lastName, and email into another sheet in the same workbook ("Email_blast").

The catch is that I want only information for "yes" folks in the Mailings sheet, and only information for "no" folks in the Email_blast sheet, with no empty spaces or empty rows of formulas. I would also like this to update every time I add in new data. I have tried, in the "Mailings" sheet, an =IF, which looks for a yes in the "Attended?" column, and if found, pulls in the needed information. If not found, it pulls in "" (blank cells). The problem is that there are now a ton of seemingly blank cells. My ideal final "Mailings" sheet would look like this (separate sheet in same workbook):

morgan	piers	101st street		New York	NY	USA
walters	barbara	east street		Philadelphia	PA
beck	glenn	1st ave		New Orleans	LA
degeneres	ellen	elm street	9B	St. Louis	MO
winfrey	oprah	maple lane		Saddle River	NJ

<tbody>
</tbody>

And "Email blast:"

stewart	jon	js@xx.com
o'brien	conan	cob@xx.com
colbert	stephen	sc@xx.com
handler	chelsea	ch@xx.com
ferguson	craig	cf@xx.com

<tbody>
</tbody>

I hope this is clear. If anyone has thoughts, or if I'm missing something super obvious, please do let me know. Thank you so much!!!
Danielle

 

[L. Howard]

Hi Danielle,

Give this a try, copy and paste in the worksheet module of the sheet with the data on it.
Code assumes there are headers on each sheets "Mailings" & "Email_blast".

Regards,
Howard

Option Explicit

Sub ColCYesNo()
'// w/much help from Mickg @ this forum

Dim ShtTargetYs As Worksheet
Dim ShtTargetNo As Worksheet
Set ShtTargetYs = Sheets("Mailings")
Set ShtTargetNo = Sheets("Email_blast")
Dim NameA As Variant
Dim YesNoCol As Range
Dim lstRow As Long
Dim c As Range
Dim yArry, nArry

lstRow = Range("C" & Rows.Count).End(xlUp).Row
Set YesNoCol = Range("C2:C" & lstRow)

For Each c In YesNoCol
   If c = "Yes" Then
        NameA = c.Offset(, 1).Resize(, 8).Value
        yArry = Application.Index(NameA, 1, Array(1, 2, 4, 5, 6, 7, 8))
        ShtTargetYs.Range("A" & Rows.Count).End(xlUp)(2).Resize(, 7) = yArry
     ElseIf c = "No" Then
        NameA = c.Offset(, 1).Resize(, 3).Value
        nArry = Application.Index(NameA, 1, Array(1, 2, 3))
        ShtTargetNo.Range("A" & Rows.Count).End(xlUp)(2).Resize(, 3) = nArry
   End If
Next

End Sub

 

[daniellelp]

Hi Howard,

Thank you so much for your help. I apologize for my delayed response. I copy-pasted that and tried to run it and nothing happened. Other macros work in this spreadsheet, but this one doesn't result in any data being moved over to the other sheets. However, it doesn't throw an error either-- it's just that nothing happens. If you have any thoughts, I would love to hear them.

Thanks again!!
Danielle

Hi Danielle,

Give this a try, copy and paste in the worksheet module of the sheet with the data on it.
Code assumes there are headers on each sheets "Mailings" & "Email_blast".

Regards,
Howard

Option Explicit

Sub ColCYesNo()
'// w/much help from Mickg @ this forum

Dim ShtTargetYs As Worksheet
Dim ShtTargetNo As Worksheet
Set ShtTargetYs = Sheets("Mailings")
Set ShtTargetNo = Sheets("Email_blast")
Dim NameA As Variant
Dim YesNoCol As Range
Dim lstRow As Long
Dim c As Range
Dim yArry, nArry

lstRow = Range("C" & Rows.Count).End(xlUp).Row
Set YesNoCol = Range("C2:C" & lstRow)

For Each c In YesNoCol
   If c = "Yes" Then
        NameA = c.Offset(, 1).Resize(, 8).Value
        yArry = Application.Index(NameA, 1, Array(1, 2, 4, 5, 6, 7, 8))
        ShtTargetYs.Range("A" & Rows.Count).End(xlUp)(2).Resize(, 7) = yArry
     ElseIf c = "No" Then
        NameA = c.Offset(, 1).Resize(, 3).Value
        nArry = Application.Index(NameA, 1, Array(1, 2, 3))
        ShtTargetNo.Range("A" & Rows.Count).End(xlUp)(2).Resize(, 3) = nArry
   End If
Next

End Sub

 

[L. Howard]

Here is a link to a workbook that works for me.

https://www.dropbox.com/s/h3e6wqmo6rmahbd/VBA%20code%20For%20Each%20And%20Resize%20copy%20to%20new%20sheets%20Drop%20Box.xlsm

Howard

 

[msn1712]

@ daniellelp

I have made an excel file. Just go to the below link. Its based on formula.

https://www.dropbox.com/s/bc9f6gqs1sd9xxj/Test.xlsx

Let me know if this works.

Cheers!
Mohan

 

[daniellelp]

Woohoo, thanks Howard! That worked great in my workbook. I'm sure it was the exact same thing, but for some reason I actually did it properly this time around-- my fault.

Thanks again!
Danielle

Here is a link to a workbook that works for me.

https://www.dropbox.com/s/h3e6wqmo6...h And Resize copy to new sheets Drop Box.xlsm

Howard

 

[daniellelp]

Dear Mohan,
Thank you! Figured it out with Howard's version, but I appreciate your efforts.
Danielle

 

[msn1712]

@ daniellelp

Thank you!!
We are all here to learn

Cheers!
Mohan

 

[L. Howard]

You're welcome and thanks for the feed back.

Regards,
Howard