Have a look at example:
Dim dt As DataTable = New DataTable("Table1")
dt.Columns.Add(New DataColumn("Item", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Description", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Size", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Bin", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("MoveQty", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("ToBin", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Comment", Type.GetType("System.String")))
dt.Rows.Add(New Object(){"AD1952203863", "ADVANTA", "120/116Q E", "AQ042", "38", "AF118", "Reduced Bin AQ042"})
dt.Rows.Add(New Object(){"AD1952203863", "ADVANTA", "120/116Q E", "AQ042", "38", "AF118", "Reduced Bin AQ042"})
dt.Rows.Add(New Object(){"AD1952203863", "ADVANTA", "120/116Q E", "AQ042", "2", "AX050", "Cleared Bin AQ042"})
dt.Rows.Add(New Object(){"AD1952204763", "ADVANTA", "120/116Q E", "AK088", "16", "AI110", "Reduced Bin AK088"})
dt.Rows.Add(New Object(){"AD1952204764", "ADVANTA", "120/116Q E", "AK088", "16", "AI110", "Reduced Bin AK088"})
Dim clearedBins = dt.AsEnumerable() _
.GroupBy(Function(b) b.Field(Of String)("Bin")) _
.Select(Function(g) New With _
{ _
Key .Bin = g.Key(), _
Key .Comments = String.Join("|", g.Select(Function(c) c.Field(Of String)("Comment"))) _
}) _
.Where(Function(a) a.Comments.Contains("Cleared")) _
.ToList()
Returns
List(Of AnonymousType(Of String, Of String))
:
Bin | Comments |
---|
AQ042 | Reduced Bin AQ042|Reduced Bin AQ042|Cleared Bin AQ042 |
[EDIT]
If you want to get 2 or 3 rows with Bins which were cleared, try this:
Dim clearedBins = (From d1 In dt.AsEnumerable() _
.GroupBy(Function(b) b.Field(Of String)("Bin")) _
.Select(Function(g) New With _
{ _
Key .Bin = g.Key(), _
Key .Comments = String.Join("|", g.Select(Function(c) c.Field(Of String)("Comment"))) _
}) _
.Where(Function(a) a.Comments.Contains("Cleared")) _
Join d2 In dt.AsEnumerable() _
.Select(Function(a) a) On d1.Bin Equals d2.Field(Of String)("Bin")) _
.Select(Function(b) b.d2) _
.Distinct() _
.ToList()
Distinct()
method eliminates duplicates.
Result:
AD1952203863 ADVANTA 120/116Q E AQ042 38 AF118 Reduced Bin AQ042
AD1952203863 ADVANTA 120/116Q E AQ042 2 AX050 Cleared Bin AQ042
[/EDIT]
BUT!!! i need to warn you:
you should work on data, not on data stored in DataTable
object!
I would strongly recommend to create class as follow:
Public Class BinData
Private sItem As String = String.Empty
Private sDescription As String = String.Empty
Private sSize As String = String.Empty
Private sBin As String = String.Empty
Private sMoveQty As String = String.Empty
Private sToBin As String = String.Empty
Private sComment As String = String.Empty
Public Property Item As String
Get
Return sItem
End Get
Set (ByVal value As String)
sItem = value
End Set
End Property
Public Property Description As String
Get
Return sDescription
End Get
Set (ByVal value As String)
sDescription = value
End Set
End Property
Public Property Size As String
Get
Return sSize
End Get
Set (ByVal value As String)
sSize = value
End Set
End Property
Public Property Bin As String
Get
Return sBin
End Get
Set (ByVal value As String)
sBin = value
End Set
End Property
Public Property MoveQty As String
Get
Return sMoveQty
End Get
Set (ByVal value As String)
sMoveQty = value
End Set
End Property
Public Property ToBin As String
Get
Return sToBin
End Get
Set (ByVal value As String)
sToBin = value
End Set
End Property
Public Property Comment As String
Get
Return sComment
End Get
Set (ByVal value As String)
sComment = value
End Set
End Property
End Class
Now, we can "move" data from datatable object into
List(Of BinData)
this way:
Dim bins As List(Of BinData) = (From d In dt.AsEnumerable() _
Select New BinData With _
{ _
.Item = d.Field(Of String)("Item"), _
.Description = d.Field(Of String)("Description"), _
.Size = d.Field(Of String)("Size"), _
.Bin = d.Field(Of String)("Bin"), _
.MoveQty = d.Field(Of String)("MoveQty"), _
.ToBin = d.Field(Of String)("ToBin"), _
.Comment = d.Field(Of String)("Comment") _
}) _
.ToList()
Now, we can "filter" data:
Dim result = bins.GroupBy(Function(i) i.Bin) _
.Select(Function(g) New With _
{ _
.BinName = g.Key(), _
.Comments = String.Join("|", g.Select(Function(c) c.Comment)) _
}) _
.Where(Function(e) e.Comments.Contains("Cleared")) _
.ToList()
On the first look, there's no difference, but using
List(Of strongly_typed_object)
is much, much powerful.
Please, read this:
Named and Anonymous Types Compared (Visual Basic)[
^]
Final note:
MoveQty
should be Integer data type.