Comparing 2 Lists

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

Hello,

I haven't touched code in 10+ years and I need to do this
quick thing and... errr... could someone just flesh it out
a bit for me and I'll do the rest of the legwork? Maybe
just some help with the syntax and looping statements? It
would be much appreciated.

I have two LONG single-column lists, each one on it's own
spreadsheet tab. There are product categories with
indented product listings under each category. The
categories all start with the same 6 characters (say
ABCDEF) and then a bunch more characters. Starting at the
top of one worksheet, I want to go down 1 item at a time
and see if it's in the 2nd list. If it ISN'T, I want to
color that entry or flag it in some way.

ABCDEF-53426334
bat
cat
dog
mouse
ABCDEF-827726363
armadillo
cat
ferret

Everything's in alphanumeric order -- categories and
products -- and the categories are unique and won't
repeat, but the products might. The test has to be
correct for the given category, so 'cat' has to be tested
twice in the case above, and match-tested within the same
category on the other sheet.

Anyway, any and all help would be appreciated. Even
guidance to an article or link to a similar exercise.

Thanks in advance!
Glenn
 
Glenn,

I notice that your indent character is not a space but Chr(160). Depending
on how newsgroups mangle this message, you may need to change cTabChar back
to Chr(160)

Wasn't sure what you were talking about "match-tested within the same
category". I'm assuming you meant Product, not Category.

Also assuming Product and Category never match (mainly because of the
Indent)


Sub test()
Const cTabChar = " ", cColourIndex = 35

Dim lngLastRow As Long, i As Long, j As Long, rngDest As Range, rng As
Range
Dim wksS As Worksheet, wksD As Worksheet
Dim strTemp As String

Set wksS = Sheet1
Set wksD = Sheet2

lngLastRow = wksS.Cells(Rows.Count, 1).End(xlUp).Row

i = 1
Do Until i > lngLastRow
strTemp = wksS.Cells(i, 1).Value
If Not Left(strTemp, 1) = cTabChar Then
Set rngDest = wksD.Columns(1).Find(strTemp, LookIn:=xlValues,
LookAt:=xlWhole, SearchDirection:=xlNext)
If Not rngDest Is Nothing Then
j = 0: Do Until Left(rngDest.Offset(j + 1, 0).Value, 1) <>
cTabChar: j = j + 1: Loop
If j > 0 Then Set rngDest = Range(rngDest.Offset(1, 0),
rngDest.Offset(j, 0))
Else
wksS.Cells(i, 1).Interior.ColorIndex = cColourIndex
End If
Else
If Not rngDest Is Nothing Then
Set rng = rngDest.Find(strTemp)
If rng Is Nothing Then wksS.Cells(i, 1).Interior.ColorIndex
= cColourIndex
End If
End If
i = i + 1
Loop
End Sub


Rob
 
Rob,

This is great! Thanks! I'm going to have to take a long
look at it and give it a try, but I wanted to take a
second and thank you.

There isn't any "real" indent in the data -- it's
formatted that way and I probably shouldn't have said
that. My bad.

Regarding the categories: in my example below,
product "cat" needs to be tested in Category ABCDEF-
53426334 from List #1 (sheet1) against the existence
of "cat" in the same category in List #2 (sheet2).

The test for product existence is always and strictly
within the same category. IOW, first you test the
existence of a category and then test for existence of
products within that category. Then move on to the next
category, etc. etc.

Thanks again for taking the time!

Glenn
 
Glenn,

The code supplied does the logic you want with respect to categories and
products.

But, it expects products to start with an indent character.
If the indent is actually a formatting feature and not a character then the
code will fail.

If it's a formatting indent then you could make the following changes to the
code:

If Not Left(strTemp, 1) = cTabChar Then
becomes
If wksS.Cells(i, 1).IndentLevel = 0 Then

Do Until Left(rngDest.Offset(j + 1, 0).Value, 1) <> cTabChar
becomes
Do Until rngDest.Offset(j + 1, 0).IndentLevel = 0


Rob
 
Rob,

Thanks!! It works great! I really appreciate your time,
and effort in sharing your knowledge. I'm going to take
the opportunity to learn VB... it's a long way from my
Cobol days back in the 70s! :)

Glenn
 
Back
Top