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

abarbee314

abarbee314

New Member
Joined
Apr 5, 2013
Messages
24
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 :cool:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]START HERE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BeginBates[/TD]
[TD]EndBates[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW033942[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW033974[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW033975[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW034025[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW034025[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW034028[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SHEET 1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002155-390[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002391-497
(DSW 002391, DSW 002490-002497 selected by D. Stewart 03/01-02/01)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][BLANK][/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002680-785
(DSW 002718-002732 selected by D. Stewart 03/21/01)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][BLANK][/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002786-908[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 006849-870
(selected by D. Stewart 03/01-02/01)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 006871-962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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.
 
Upvote 0
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...

abarbee314 said:
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 :cool:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]START HERE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BeginBates[/TD]
[TD]EndBates[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW033942[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW033974[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW033975[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW034025[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW034025[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl66, width: 79"]DSW034028[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SHEET 1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002155-390[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002391-497
(DSW 002391, DSW 002490-002497 selected by D. Stewart 03/01-02/01)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][BLANK][/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 034026-027
(DSW 034026 selected by D. Stewart 03/21/01)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][BLANK][/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 002786-908[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 006849-870
(selected by D. Stewart 03/01-02/01)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]DSW 006871-962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
abarbee314 said:
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.
Click to expand...

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?
 
Upvote 0
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. :)
 
Upvote 0
abarbee314 said:
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. :)
Click to expand...


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
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top