Excel transfer specific row selection from a column to neighboring column

[dnjl01]

As already mentioned in the title, I would like to transfer a selection from a column to the neighboring column in Excel.
The problem besides holding down the classic shift is that I have only selected individual rows of the column (where a certain value is in it) and only want to transfer this selection to the next column in order to assign a value to these specific rows there.
Exaample shown in the picture below, were I want a similar row selection of the "a" rows from column "A" at column "B". Per

It would be perfect if only the corresponding rows in column B were selected in order to be able to enter a new value there directly. Thanks in advance for Help.

 

[kevin9999]

If you were OK with a VBA solution, then try running this from the active sheet (assumes you only have constants in column A)

Sub One_Column_Right()
    Dim r As Range
    Set r = Range("A:A").SpecialCells(xlCellTypeConstants)
    r.Offset(, 1).Value2 = r
End Sub

 

[kevin9999]

Or if there's nothing below the last value in Column A then this will be much faster if you have thousands of rows:

Sub Fast_One_Column_Right()
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Range("B1")
End Sub

 

[dnjl01]

Or if there's nothing below the last value in Column A then this will be much faster if you have thousands of rows:

Sub Fast_One_Column_Right()
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Range("B1")
End Sub

Thank you very much for your help so far. The line will be copied, but ALL, which means that the unselected rows get copied also.
Is there a way to really ONLY copy the selected lines, so that if there is anything in the unselected lines, they are not copied?

 

[kevin9999]

If there are only blank cells in the rows between the cells you want to copy, then the code in post #2 should achieve that.

 

[dnjl01]

Yes this works fine. But

If there are only blank cells in the rows between the cells you want to copy, then the code in post #2 should achieve that.

I'm sorry I was incomprehensible, but sometimes there is content in the unselected cells, which is why I attach so much importance to only transferring the selected cells. Is there a solution for this as well?

 

[kevin9999]

Before I can do any more on this, I'll need to know the logic behind which cells are selected for copying & which cells are excluded. How do you determine this? It would also help if you could provide a sample of your actual data using the XL2BB add in.

 

[dnjl01]

Thank you for answering so far :D
So, I have created an example file that better illustrates the target. The idea is to use these Yes/No columns for later expolaritve analyses in RStudio. These are huge Excel sheets.
Unfortunately, the arrangements of the variables a/b/c are not in the same order. So far I wanted to select all "a" of the "Measures" column via Search and then wanted to transfer this selection to the column of "Measures_a" in order to be able to insert "yes" there and fill the rest with "no" using the blank selection, like shown at the column "measure_c".
(Sorry, my Excel blocks the XL2BB add in, I hope this simple table works with this here)

project_name	measures	measure_a	measure_b	measure_c
xyz	a, b, c			yes
xyz	c, b			yes
xyz	a			no
xyz	b			no
xyz	a, b			no
xyz	a			no
xyz	a, c			yes
xyz	c			yes
xyz	b, c, a			yes
xyz	b			no
xyz	c, a			yes
xyz	c			yes
xyz	a, b, c			yes
xyz	a			no
xyz	b			no
xyz	b			no
xyz	c, b			yes
xyz	c			yes
xyz	a			no
xyz	a, b, c			yes
xyz	b			no
xyz	b, a			no
xyz	c			yes
xyz	a			no
xyz	b			no
xyz	a, c, b			yes
xyz	a, b			no
xyz	c, a, b			yes
xyz	a, c			yes

 

[kevin9999]

I think I understand what you want now. Try the following on a copy of your data (change the sheet name to suit). I tested it on 100k rows, came in at under 0.8 secs.

Option Explicit
Sub dnjl01()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<< **Change to actual sheet name**
    
    Dim ArrIn, ArrOut, alpha, i As Long, j As Long, s As String
    alpha = Array("a", "b", "c")
    ArrIn = ws.Range("B2", Cells(Rows.Count, "B").End(xlUp))
    ReDim ArrOut(1 To UBound(ArrIn), 1 To 3)
    
    For i = 1 To UBound(ArrIn)
        s = CStr(ArrIn(i, 1))
        For j = 1 To 3
            If InStr(1, s, alpha(j - 1)) > 0 Then ArrOut(i, j) = "yes" Else ArrOut(i, j) = "no"
        Next j
    Next i
    ws.Range("C2").Resize(UBound(ArrIn, 1), 3).Value = ArrOut
End Sub

 

[dnjl01]

I think I understand what you want now. Try the following on a copy of your data (change the sheet name to suit). I tested it on 100k rows, came in at under 0.8 secs.

Option Explicit
Sub dnjl01()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<< **Change to actual sheet name**
   
    Dim ArrIn, ArrOut, alpha, i As Long, j As Long, s As String
    alpha = Array("a", "b", "c")
    ArrIn = ws.Range("B2", Cells(Rows.Count, "B").End(xlUp))
    ReDim ArrOut(1 To UBound(ArrIn), 1 To 3)
   
    For i = 1 To UBound(ArrIn)
        s = CStr(ArrIn(i, 1))
        For j = 1 To 3
            If InStr(1, s, alpha(j - 1)) > 0 Then ArrOut(i, j) = "yes" Else ArrOut(i, j) = "no"
        Next j
    Next i
    ws.Range("C2").Resize(UBound(ArrIn, 1), 3).Value = ArrOut
End Sub

Can't believe this code does everything automatically. It works wonderfully. Thanks so much.