One Column of Email Addresses from Three Columns

do you want to have email address from alternative and personal opt out with N in front of them

Hello Find attached !

Formula will work for 2000 rows of sheet1 you can change the references to as many rows as you want!

To enter the formula in you actual sheet---->go to sheet 2 select cell A2 press F2 and copy the whole formula go to your actual sheet select A2 and copy paste same then hold control and shift together and Hit enter key, then release all three keys and drag down! do similarly for other columns !

Hello Find attached !

Hope this helps


if this helps click"*" add rep icon in the bottom left post of my corner !

Sub OneToThreeColumn()

Sheets("sheet1").Range("C2").Select
x = 0
Do While ActiveCell.Value <> ""

    If ActiveCell.Offset(0, -1) = "N" And ActiveCell.Offset(0, -2) <> "" Then
    x = x + 1
    Sheets("Sheet2").Range("A" & x).Value = ActiveCell.Value
    Sheets("Sheet2").Range("B" & x).Value = ActiveCell.Offset(0, 1).Value
    Sheets("Sheet2").Range("C" & x).Value = ActiveCell.Offset(0, -2).Value
    End If

    If ActiveCell.Offset(0, 5) = "N" And ActiveCell.Offset(0, 4) <> "" Then
    x = x + 1
    Sheets("sheet2").Range("A" & x).Value = ActiveCell.Value
    Sheets("sheet2").Range("B" & x).Value = ActiveCell.Offset(0, 1).Value
    Sheets("sheet2").Range("C" & x).Value = ActiveCell.Offset(0, 4).Value
    End If

    If ActiveCell.Offset(0, 7) = "N" And ActiveCell.Offset(0, 6) <> "" Then
    x = x + 1
    Sheets("sheet2").Range("A" & x).Value = ActiveCell.Value
    Sheets("sheet2").Range("B" & x).Value = ActiveCell.Offset(0, 1).Value
    Sheets("sheet2").Range("C" & x).Value = ActiveCell.Offset(0, 6).Value
    End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

To use code in your actual workbook. Open the workbook then press alt+F11, new window will open then go to --->insert(at the top of window) --->module and copy paste above code there. To run the code press alt and F8 together and select the macro onetothree column

Thanks for the feedback and Rep!