Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to check data by client n add new symbols to client's list
Thanks, Jacob. That was marvellous!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - want to use symbols, but list is in orig font | Excel Discussion (Misc queries) | |||
vlookup for client list | Excel Discussion (Misc queries) | |||
How are symbols inserted in a data validation look-up list? | Excel Worksheet Functions | |||
Symbols in Data Validation List | Excel Worksheet Functions | |||
How could I get the client's browser to refresh an HTML spreadshee | Excel Programming |