Help. It should be simple, but it is not.

[Guest]

I have a two column list. I am trying to find a way to extract information
from one cell and add it to another based on criterial from the cell in the
first column. I need it to only put information in the last row for the Matl
No. when there are more than one material number. A Matl No. might show up
any number of times. I am having real trouble with the logic for this.

I think it should first evaluate the count for each Matl No. (** I don't
know how to do that except from the Data, Subtotal menu.)
Then run a loop through all the records in that count.

Can anybody help me with this programming dilemma?

Ex.
Desired Result
Matl No. Asset No. Assets
1234 15A65
1234 16B23
1234 16F56 15A65, 16B23, 16F56
1489 25D54 25D54
1389 35Y64
1389 75Y62 35Y64, 75Y62

Mat

 

[Guest]

Give this code a try...

Sub Test()
Dim rngToSearch As Range
Dim rng As Range
Dim strAccumulate As String
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count,
"A").End(xlUp))
End With

For Each rng In rngToSearch
If rng.Value = rng.Offset(1, 0).Value Then
If strAccumulate = "" Then
strAccumulate = rng.Offset(0, 1).Value
Else
strAccumulate = strAccumulate & ", " & rng.Offset(0, 1).Value
End If
Else
If strAccumulate = "" Then
rng.Offset(0, 2).Value = rng.Offset(0, 1).Value
Else
rng.Offset(0, 2).Value = strAccumulate & ", " &
rng.Offset(0, 1).Value
End If
strAccumulate = ""
End If
Next rng
End Sub

 

[Rick Hansen]

Hi Mat, You can also give this code a try. It very similar to Jim's code.
Good Luck and enjoy...

Rick

Sub TestIt()
Dim lrow As Long
Dim x As Long
Dim allAsst As String
Dim ws As Worksheet

Set ws = ActiveSheet
lrow = ws.Range("a2").End(xlDown).Row

With ws
For x = 2 To lrow
If .Cells(x + 1, 1) = .Cells(x, 1) Then
allAsst = IIf(allAsst = "", .Cells(x, 2), allAsst & ", " & .Cells(x,
2))
Else
.Cells(x, 3) = IIf(allAsst = "", .Cells(x, 2), allAsst & ", " &
..Cells(x, 2))
allAsst = ""
End If
Next x
End With
End Sub