Sub to check data by client n add new symbols to client's list
 

In sheet: All,
In A6:B6 down are ClientID/Symbol data, eg:
AA MT
AA JPM
BB HIG
AA PCS
BB GE
BB GE
BB AIG
etc
(there may be duplicate clientID/symbols listed, eg BB - GE above)

In another sheet: T,
C1 contains the clientID, eg: BB

In B19 down are client BB's existing symbols eg:
HIG
TXT

What I need is for a sub to go to All, gather all the unique symbols
associated with the clientID in T's C1 (ie: BB), then check these with the
symbols already listed in T (in B19 down) and add any new symbols into T's
col B (append below) in red/bold font

For the sample data above, I would expect to see GE & AIG written into T's
B21:B22 (the order for the 2 symbols is immaterial). Thanks

Sub to check data by client n add new symbols to client's list
 

Hi Max

Try the below and feedback

Sub Macro7()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rngTemp1 As Range, rngTemp2 As Range

Set ws1 = Worksheets("All")
Set ws2 = Worksheets("T")

For lngRow = 6 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
If ws1.Range("A" & lngRow) = ws2.Range("C1") Then
Set rngTemp = ws2.Range("B19:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If WorksheetFunction.CountIf(rngTemp, ws1.Range("B" & lngRow)) = 0 Then
Set rngTemp2 = ws2.Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
rngTemp2 = ws1.Range("B" & lngRow).Text
rngTemp2.Font.ColorIndex = 3: rngTemp2.Font.Bold = True
End If
End If
Next

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Max" wrote:

In sheet: All,
In A6:B6 down are ClientID/Symbol data, eg:
AA MT
AA JPM
BB HIG
AA PCS
BB GE
BB GE
BB AIG
etc
(there may be duplicate clientID/symbols listed, eg BB - GE above)

In another sheet: T,
C1 contains the clientID, eg: BB

In B19 down are client BB's existing symbols eg:
HIG
TXT

What I need is for a sub to go to All, gather all the unique symbols
associated with the clientID in T's C1 (ie: BB), then check these with the
symbols already listed in T (in B19 down) and add any new symbols into T's
col B (append below) in red/bold font

For the sample data above, I would expect to see GE & AIG written into T's
B21:B22 (the order for the 2 symbols is immaterial). Thanks

Sub to check data by client n add new symbols to client's list
 

Jacob, muchas gracias`. It works good.

To handle data quality concerns I would like the matching for the clientID
(T's C1 vs All's col A) to be more "robust" via addition of TRIM, and the
match is also not to be case-sensitive (I think currently it is
case-sensitive), ref the line below:
If ws1.Range("A" & lngRow) = ws2.Range("C1") Then

Thanks

Sub to check data by client n add new symbols to client's list
 

Replace
<<If ws1.Range("A" & lngRow) = ws2.Range("C1") Then

with

If StrComp(Trim(ws1.Range("A" & lngRow)), _
Trim(ws2.Range("C1")), vbTextCompare) = 0 Then

If this post helps click Yes
---------------
Jacob Skaria


"Max" wrote:

Jacob, muchas gracias`. It works good.

To handle data quality concerns I would like the matching for the clientID
(T's C1 vs All's col A) to be more "robust" via addition of TRIM, and the
match is also not to be case-sensitive (I think currently it is
case-sensitive), ref the line below:
If ws1.Range("A" & lngRow) = ws2.Range("C1") Then

Thanks

Sub to check data by client n add new symbols to client's list
 

Thanks, Jacob. That was marvellous!