Using formula to extract info

[KH]

I have a few columns of info which I want to extract (more than a 1000 rows).
111 Data1
112 Data2
112 Data3
113 Data4
113 Data5
114 Data6
114 Data7
114 Data8
How can I extract such that for the same value in the first column, the data
in column 2 is copy to the same row as the first value.
e.g
111 Data1
112 Data2 Data3
113 Data4 Data5
114 Data6 Data7 Data8

 

[Bernie Deitrick]

KH,

Since this is a programming newsgroup, try the macro below.

Assumes your table is in columns A and B starting in row 1, with labels in
row 1, and that there is nothing else on the sheet.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range
Dim mySource As Range
Dim myTarget As Range
Dim myRow As Variant

Set mySource = Intersect(Range("A2:A65536"), ActiveSheet.UsedRange)
Range("D1") = Range("A1").Value

For Each myCell In mySource
myRow = Application.Match(myCell.Value, Range("D"), False)
If Not (IsNumeric(myRow)) Then
Set myTarget = Range("D65536").End(xlUp)(2)
myTarget.Value = myCell.Value
myTarget(1, 2).Value = myCell(1, 2).Value
Else
Cells(myRow, 256).End(xlToLeft)(1, 2).Value = myCell(1, 2).Value
End If
Next myCell
End Sub

 

[KH]

Hi Bernie,
Tried your macro. It did not run as needed. The results was just the same as
the first two column of info.
I would like for the same value in column one cells, the second column data
be appended to the next column.
i.e.
1113 Data4 Data5
1114 Data6 Data7 Data8

Any possibility?

KH

 

[Bernie Deitrick]

KH,

I don't know what to say - I tested my macro on your data set, and got
precisely the output that you indicated in your post, so the code works
fine. If you send me an email, I will send you a working version - remove
the spaces and change the dot to a . in my email address.

HTH,
Bernie
MS Excel MVP