Transferring specific numbers from a column to a column

[Mtumushi]

How do I move the specific numbers to a column that correspond to the numbers.. Kindly assist 

For example

1,2,3,5,9 and they are about 500 and I want to copy for example 9 to column 9..

[#2]

@Mtumushi 

Where are these numbers to start with?

[#3 · Accepted solution]

Hi @Mtumushi 

If I understand the requirement correctly, you need below formula:

=IFERROR(INDEX($A:$A,MATCH(COLUMN(),$A:$A,0),1),"")

Please let me know if it works for you. A sample file is also attached for your reference.

Thanks

Tauqeer

[#4]

The numbers are in Excel in one cell. I have downloaded them from ODK data base and now want to align them to a specific answer in for example transfer 7 to column 7.

1, 2, 7
7
1, 2, 7
1, 7
1, 5, 6
1, 2, 4, 6, 7
1, 2, 5, 7

[#5]

Let me try it out.. I will inform you if it works

[#6]

@Mtumushi 

Here is a macro you can run. It assumes that the values are in column A, starting in A1.

Sub MoveNumbers()
    Dim r As Long
    Dim m As Long
    Dim a() As String
    Dim i As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 1 To m
        a = Split(Cells(r, 1).Value, ", ")
        Cells(r, 1).ClearContents
        For i = 0 To UBound(a)
            c = a(i)
            Cells(r, c).Value = c
        Next i
    Next r
    Application.ScreenUpdating = True
End Sub

[#7]

Do you have some little time we do a video....

[#8]

Thanks but this may only work if each cell has a single number... What is it has multiple numbers?

[#9]

@Mtumushi 

I can't do a video conference, if that's what you mean.

[#10]

@Mtumushi 

Can you confirm what is the range of multple numbers, I mean the range is 1 to 9 (single digit) or it can be any number for example 312, 7123 etc.

Thanks

[#11]

The numbers are 3,4,7,9, in a single cell and basically what I need is to transfer as they are to column 3,4,7 and 9

[#12]

May be you can share your email.. Thanks

[#13]

@Mtumushi 

My macro works for that scenario:

 

[#14]

@Mtumushi 

My email address is hans dot vogelaar at gmail dot com

[#15]

@Mtumushi 

Thank you for your email. Try this macro:

Sub MoveNumbers()
    Dim r As Long
    Dim m As Long
    Dim a() As String
    Dim i As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 3).End(xlUp).Row
    For r = 2 To m
        a = Split(Cells(r, 3).Value, ", ")
        For i = 0 To UBound(a)
            If LCase(a(i)) Like "other*" Then
                Cells(r, 11) = "other"
            Else
                c = a(i)
                Cells(r, c + 3).Value = c
            End If
        Next i
    Next r
    Application.ScreenUpdating = True
End Sub

[#16]

@Mtumushi 

Here is a version of the macro with comments that explain how it works:

Sub MoveNumbers()
    Dim r As Long     ' Row number
    Dim m As Long     ' Last row
    Dim a() As String ' Array to store the individual parts
    Dim i As Long     ' Index for looping through the array
    Dim c As Long     ' Individual part if numeric
    ' Turn off screen updating to speed up execution
    Application.ScreenUpdating = False
    ' Determine the last row in column 3 (column C)
    m = Cells(Rows.Count, 3).End(xlUp).Row
    ' Loop through the rows from row 2 to row m
    For r = 2 To m
        ' Split the value of the cell in row r, column c
        ' with comma plus space as delimiter
        a = Split(Cells(r, 3).Value, ", ")
        ' Loop through the array
        For i = 0 To UBound(a)
            ' Check whether the i-th part begins with "other"
            If LCase(a(i)) Like "other*" Then
                ' If so, write "other" in column 11 (column K)
                Cells(r, 11) = "other"
            Else
                ' Otherwise, it must be a number
                ' Set c to the numeric value
                c = a(i)
                ' Write that number to the appropriate column
                ' 1 in column 4, 2 in column 5 etc.
                Cells(r, c + 3).Value = c
            End If
        Next i
    Next r
    ' Resume screen updating
    Application.ScreenUpdating = True
End Sub

[#17]

 Thank you very much @tauqeeracma. It has perfectly work

[#18]

@Mtumushi 

I'm surprised - tauqeeracma's formula, however nice, does something completely different from what you asked...

[#19]

@Hans Vogelaar I had no idea what it was... Thank you.. You really assisted 

[#20]

Hi @Hans Vogelaar 

Your VBA code was excellent and exactly producing the desired result but @Mtumushi was interested in non-VBA solution and he wanted the solution directly in his file through email.

Thanks

Tauqeer