HELP!!! Could someone HELP TRANSLATE this simple psuedo code into MACRO please?!

[bxc2739]

for EACH row in WORK
{
   for each row in DATA
   {
    if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name))
    Then
    Highlight current WORK.row to YELLOW, break out of current for loop
    ELSE
    next DATA row++
   }
    next WORK row++
}

[/SIZE]

[SIZE="3"](see below for explaination)

I have a Excel workbook called FIRSTAM8.xls

Inside I have two seperate worksheets: 1)'DATA' & 2)'WORK'
There are 31886 rows in sheet 'WORK' & 5741 rows in sheet 'DATA'

Column 'C' in 'WORK' contains the customer name, Column 'B' contains the zip code.

In the 'DATA' sheet Column 'H' contains the customer name, & Column 'S' the zip code.


I want to compare (individually, one-by-one) the zip && name Columns ('B' & 'C')
of each row in sheet 'WORK' to/with/against the respective
zip && name Columns ('S' & 'H') of EACH AND EVERY row in sheet 'DATA'.

For example: if BOTH the zip &&and&& name columns of row1 of sheet 'WORK' matches
with ANY (any of the 5741) rows of sheet 'DATA' then row1 IS a match/hit,
and the entire row1 of sheet 'WORK' is highlighted yellow to indicate the match status.
If row1 of 'WORK' matches to multiple rows of 'DATA' that is okay, it is still highlighted.
However if row1 of 'WORK' does not match (by the criteria of zip && name) to ANY of the
5741 rows in "DATA" then it is not highlighted and the next row (row2) is processed.

Note: When comparing names, I really mean comparing the first
character of the customer name fields. I do this to avoid
false negatives and so not to miss a potential match.
This will entail using the substring manipulation functions.(?LEFT)

--------------------

for EACH row in WORK
{
   for each row in DATA
   {
    if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name))
    Then
    Highlight current WORK.row to YELLOW, break out of current for loop
    ELSE
    next DATA row++
   }
    next WORK row++
}

*OR*

for each row in WORK
{
   for each row in DATA
   {
    if ((WORK.row.column(B) == DATA.row.column(S)) && (WORK.row.firstcharof(column(C) == DATA.row.firstcharof(column(H)))
    Then
    Highlight.interior.current.WORK.row = YELLOW, break out of current for-loop
    ELSE
    next DATA row++
   }
    next WORK row++
}

CAN SOMEONE TRANSLATE THE ABOVE INTO REAL EXCEL CODE FOR ME??



Here is a link to the ENTIRE post I made (for clarity and reference):
http://www.ozgrid.com/forum/showthread.php?t=49941

Here is an abridged IMAGE of the my Excel Workbook:
Attachment 4703

Here is the original file I am working on (LARGE):
FIRSTAM8.xls
http://www4.sendthisfile.com/d.jsp?t...Oeb2puKBql1LmB

Thanks,
Bo

[Static]

yes.. but u only want the first letter of the customer name checked?? u will get tons of false positives?? (Or did I missunderstand) u need to match them one to one..
CASE can be ignored... so Company1 will = COMpAnY1
is that ok??
BRB with code...

[Static]

also.. must the Name and Zip match on the same row?
or can u find a name match in row 7 and a zip match in ro 12 etc...

[bxc2739]

Static, yes I do want it like that. You understood me corrects.

The thing is, my Excel spreedsheet is 31000+ rows LONG!! And I know
from previous spreedsheets of the similiar nature that ONLY 2-3%
of the ENTIRE thing actually match, and the vast majority are NO match.
So its kinda like looking for pins in a large haystack. So I will actually in my case
not get the ton of false postives like you would invision. Therefore (since I have SO FEW actuall matchs) I want to be on the safe side and only search for
first character.

Thanks for the reply!!

Bo

[bxc2739]

Static, what I am actually trying to do, is match customers from one sheet againts another. But since there are 31000+ to match, I try something clever, like using a macro in Excel to scan for all zip + name that do not match any records, thus I can safely eliminate them, and the rest (like 2-3%) that is left over, I will go back and match/verify them by hand.

Here take a look at this thread, it will explain in detial everything.

http://www.ozgrid.com/forum/showthread.php?t=49941

Thanks!

[DKenny]

Why not use a vlookup in excel? That way you wouldn't need any code.

[bxc2739]

Dkenny,

I would, but except my case is more complicated.

I have two sheets in a workbook. BOTH VERY LARGE.
The zip code column and customer name column of EACH row in sheet2
has to be compared with the repsective zip && name column EVERY row in
sheet1. (am I making sense? thats 31000*5000*2=310000000 total comparisons.)

This thread explains in FULL detial (sorry, its very long thread I wrote)
http://www.ozgrid.com/forum/showthread.php?t=49941

ANd additional, when I need to search by name, actually I am
wanting to search by the first character of the customer name fields,
thus this entials substring manipulation.

So because of these many factors, I don't think something this complexed can be implemented as a formula, but really needs a macro code.


Thanks,
Bo

[Static]

Private Sub HighLightMatches()
    Dim WORKsht As Worksheet
    Dim DATAsht As Worksheet
    Dim wRow As Long
    Dim dRow As Long
    
    Set WORKsht = ActiveWorkbook.Sheets("WORK")
    Set DATAsht = ActiveWorkbook.Sheets("DATA")
    
    wRow = 1
    'this asumes there are no blank rows
    Do Until WORKsht.Cells(wRow, 2) = ""  'go till the first empty space is found
        dRow = 1
        Do Until DATAsht.Cells(dRow, 8) = "" 'go till the first empty space is found
            If UCase(Left(WORKsht.Range("C" & wRow), 1)) = UCase(Left(DATAsht.Range("H" & dRow), 1)) And WORKsht.Range("B" & wRow) = DATAsht.Range("S" & dRow) Then
                Rows(wRow & ":" & wRow).Select
                With Selection.Interior
                    .ColorIndex = 36
                    .Pattern = xlSolid
                End With
                Exit Do
            End If
            dRow = dRow + 1
        Loop
        wRow = wRow + 1
    Loop
        
 
End Sub

[DKenny]

It can be managed with formulas. Add a column to each table that concatenates the values you need to compare into a single string and then do lookups between those 2 columns.

[bxc2739]

Static,

thanks for the QUICK response with the code! Wow that was fast!

I'm still running it on my computer (still computing) (this might take a while...)
and I'll let you know how it did when it is done!

THanks again!

[bxc2739]

Thanks Static, that is wonderful code, it works great!

I tested it on smaller files and estimated the time.
However it will take 6.5 hours, so I will see the full results tomorrow.

Thanks!

Bo

[bxc2739]

Static, it actually took 3 hours to execute the entire sheet, but it worked

Thanks so much, that is wonderful little code!!