World's worst VLOOKUP - give it a look please :)

[abarbee314]

Howdy folks!

You've been my rock many times so I'm hoping someone here has a suggestion.

Below is a sample of a spreadsheet with two small bits of data. Start Here is where we need to see if the RANGE of numbers covered by the Bates label columns are found in *ANY* of the values in SHEET 1. World's worst vlookup.

Description:

• "Start here" shows various bates number ranges
• We would like to know if the ranges in that set cover ANY of the values shown (horribly) in SHEET 1
• Yes, SHEET 1 is also ranges, so we're trying to find the cross-over between those values. But obviously they were recorded differently.
• There are over 10,000 rows of data involved, otherwise I'd just tell them to crank thru manually

Yes, I thought about text to columns and concatenate, but it doesn't work for all entries in SHEET 1. As you can see, many of them have a range listed themselves, minus leading values, while others have EXTRA text.

Anyway, I told you it was "not-too-friendly". I welcome any suggestions or thoughts!

THANKS!

Aaron

START HERE
BeginBates	EndBates
DSW033942 <tbody> </tbody>	DSW033974 <tbody> </tbody>
DSW033975 <tbody> </tbody>	DSW034025 <tbody> </tbody>
DSW034025 <tbody> </tbody>	DSW034028 <tbody> </tbody>

<tbody>
</tbody>

SHEET 1

DSW 002155-390 <tbody> </tbody>

DSW 002391-497 (DSW 002391, DSW 002490-002497 selected by D. Stewart 03/01-02/01) <tbody> </tbody>

[BLANK]

DSW 002680-785 (DSW 002718-002732 selected by D. Stewart 03/21/01) <tbody> </tbody>

[BLANK]

DSW 002786-908 <tbody> </tbody>

DSW 006849-870 (selected by D. Stewart 03/01-02/01) <tbody> </tbody>

DSW 006871-962 <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Apparently, none of the ranges match the data on the sheet1.
Or did I miss something?
You could put examples on sheet1 that do match the range of data and explain why they match and also explain where you want the result.

 

[abarbee314]

Sorry, DanteAmor. I was merely trying to showcase the variants between the data sets in hopes that you guys could help me dig through. I edited the table (BOLD) to show a sample match...

Howdy folks!

You've been my rock many times so I'm hoping someone here has a suggestion.

Below is a sample of a spreadsheet with two small bits of data. Start Here is where we need to see if the RANGE of numbers covered by the Bates label columns are found in *ANY* of the values in SHEET 1. World's worst vlookup.

Description:

• "Start here" shows various bates number ranges
• We would like to know if the ranges in that set cover ANY of the values shown (horribly) in SHEET 1
• Yes, SHEET 1 is also ranges, so we're trying to find the cross-over between those values. But obviously they were recorded differently.
• There are over 10,000 rows of data involved, otherwise I'd just tell them to crank thru manually

Yes, I thought about text to columns and concatenate, but it doesn't work for all entries in SHEET 1. As you can see, many of them have a range listed themselves, minus leading values, while others have EXTRA text.

Anyway, I told you it was "not-too-friendly". I welcome any suggestions or thoughts!

THANKS!

Aaron

START HERE
BeginBates	EndBates
DSW033942 <tbody> </tbody>	DSW033974 <tbody> </tbody>
DSW033975 <tbody> </tbody>	DSW034025 <tbody> </tbody>
DSW034025 <tbody> </tbody>	DSW034028 <tbody> </tbody>

<tbody>
</tbody>

SHEET 1

DSW 002155-390 <tbody> </tbody>

DSW 002391-497 (DSW 002391, DSW 002490-002497 selected by D. Stewart 03/01-02/01) <tbody> </tbody>

[BLANK]

DSW 034026-027 (DSW 034026 selected by D. Stewart 03/21/01) <tbody> </tbody>

[BLANK]

DSW 002786-908 <tbody> </tbody>

DSW 006849-870 (selected by D. Stewart 03/01-02/01) <tbody> </tbody>

DSW 006871-962 <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Okay, the number 034026-027 is in the DSW034025 - DSW034028 range,

Now what do you need?
That is, what data you want to take, where do you want to take it and where do you want to put it.

 

[abarbee314]

Is there anything you can think of that might help us extract that matching value from SHEET 1 and mark it or label it on the START HERE sheet? Again, similar to a vlookup, but obviously there is a lot of 'junk' in that second set of data. We just need to know if there are matches/cross-overs between the data sets.

Both sheets/files contain 1,000s of rows of data, so I'm trying to save them a few weeks of work. But, doing this manually may be their only option.

Thanks.

 

[DanteAmor]

Is there anything you can think of that might help us extract that matching value from SHEET 1 and mark it or label it on the START HERE sheet? Again, similar to a vlookup, but obviously there is a lot of 'junk' in that second set of data. We just need to know if there are matches/cross-overs between the data sets.

Both sheets/files contain 1,000s of rows of data, so I'm trying to save them a few weeks of work. But, doing this manually may be their only option.

Thanks.

But you do not tell me what you want to do.
Do you want to put a mark?
what kind of mark?
Where do you want to put the mark, on which sheet, in which cell?

 

[abarbee314]

Again, my apologies for not clarifying.

How about this: put a 12pt, bold, capitalized X (times new roman) in the first available cell to the right of the EndBates cell in START HERE?

I am not overly concerned about how it looks or is marked. Anything that draws our attention to the match. Your flexibility or personal choice might be better.

Thank you, sir.

 

[DanteAmor]

Again, my apologies for not clarifying.

How about this: put a 12pt, bold, capitalized X (times new roman) in the first available cell to the right of the EndBates cell in START HERE?

I am not overly concerned about how it looks or is marked. Anything that draws our attention to the match. Your flexibility or personal choice might be better.

Thank you, sir.

Assuming the data is like this in your sheets

<b>START HERE</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >BeginBates</td><td >EndBates</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >DSW033942</td><td >DSW033974</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >DSW033975</td><td >DSW034025</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >DSW034025</td><td >DSW034028</td><td >x</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td></tr></table>


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:423.92px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >DSW 002155-390</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >DSW 002391-497</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >(DSW 002391, DSW 002490-002497 selected by D. Stewart 03/01-02/01)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >[BLANK]</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:; ">DSW 034026-027</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >(DSW 034026 selected by D. Stewart 03/21/01)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >[BLANK]</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >DSW 002786-908</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >DSW 006849-870</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >(selected by D. Stewart 03/01-02/01)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >DSW 006871-962</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td></tr></table>


Use this macro

Sub Test11()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, b As Range, i As Long
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("START HERE")
    sh2.Range("C2:C" & Rows.Count).ClearContents
    For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
        ini = Val(Mid(c, 4))
        fin = Val(Mid(c.Offset(, 1), 4))
        For i = ini To fin
            Set b = sh1.Range("A:A").Find(i, LookIn:=xlValues, lookat:=xlPart)
            If Not b Is Nothing Then
                c.Offset(0, 2) = "x"
            End If
        Next
    Next
End Sub