Forum Discussion
Transferring specific numbers from a column to a column
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..
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
20 Replies
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
Thank you very much tauqeeracma. It has perfectly work
I'm surprised - tauqeeracma's formula, however nice, does something completely different from what you asked...
- Thanks but this may only work if each cell has a single number... What is it has multiple numbers?
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
- Let me try it out.. I will inform you if it works
Where are these numbers to start with?
- 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, 7Here 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
