0

I am seeking for help I have thousands of unit ID that I need to copy and paste transpose manually. Here is the situation if column C Seq no. is the same for example Seq 1 then I need to copy column B unit ID and paste transpose to column D and I need to do that to the rest of the column

enter image description here

Improve this question
2

1 Answer 1

Reset to default
2

Try this:

Sub TransposeUnitID()
    Dim data As Variant, seq_number As Integer, rw as Long

    data = Range("B2:C21").Value '~~>Update as necessary
    seq_number = data(1, 2)
    rw = 2

    For i = 1 To UBound(data)

        If data(i, 2) <> seq_number Then
            seq_number = data(i, 2)
            rw = i + 1
        End If

        Range("B" & rw).End(xlToRight).Offset(0, 1) = data(i, 1)
    Next i
End Sub

Notes:

  • Reads your data in as an array
  • Assume Seq is ordered (as per example)
  • Loop over array and keep track of Seq to print UnitID to correct cell
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@ alex P thank you so much this really helps i just want to ask how can i extend the range data = Range("B2:C99").Value tried to maximize to thousand but got error i want to extend on the whole column c
Just replace the range with Range(“B2:C99”).value.
Im getting error if i change to more than thousand Range(“B2:C1900”).value
It says run time error 1004: apllication- defined or object defined error
i got error on this line Range("B" & rw).End(xlToRight).Offset(0, 1) = data(i, 1)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.