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

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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]morgan[/TD]
[TD]piers[/TD]
[TD]101st street[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]walters[/TD]
[TD]barbara[/TD]
[TD]east street[/TD]
[TD][/TD]
[TD]Philadelphia[/TD]
[TD]PA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]beck[/TD]
[TD]glenn[/TD]
[TD]1st ave[/TD]
[TD][/TD]
[TD]New Orleans[/TD]
[TD]LA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]degeneres[/TD]
[TD]ellen[/TD]
[TD]elm street[/TD]
[TD]9B[/TD]
[TD]St. Louis[/TD]
[TD]MO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]winfrey[/TD]
[TD]oprah[/TD]
[TD]maple lane[/TD]
[TD][/TD]
[TD]Saddle River[/TD]
[TD]NJ[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And "Email blast:"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]stewart[/TD]
[TD]jon[/TD]
[TD]js@xx.com[/TD]
[/TR]
[TR]
[TD]o'brien[/TD]
[TD]conan[/TD]
[TD]cob@xx.com[/TD]
[/TR]
[TR]
[TD]colbert[/TD]
[TD]stephen[/TD]
[TD]sc@xx.com[/TD]
[/TR]
[TR]
[TD]handler[/TD]
[TD]chelsea[/TD]
[TD]ch@xx.com[/TD]
[/TR]
[TR]
[TD]ferguson[/TD]
[TD]craig[/TD]
[TD]cf@xx.com[/TD]
[/TR]
</tbody>[/TABLE]

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