Double Lookup or something Better?

D

dthhal

New Member
Joined
Aug 18, 2009
Messages
18
Hello Gurus.
I have a set of data that contains serial numbers, models, and compartments. In addition I have a master list that contains what compartments should be associated with what models. This issue Im having is that I need to know which compartments are missing from each serial number. This put me into a double lookup scenario where I need to find the model in the master list, then tell me what compartments are missing in the actual data.

Actual DataMaster List
SerialModelCOMPARTMENTMODELCOMPARTMENT
18742305CRENG305CRENG
18742305CRHS305CRFD_RR_LT
2201056815FENG305CRFD_RR_RT
247MT765DIFF_RR305CRFS
247MT765ENG305CRHS
247MT765FD_RR_LT305CRRA
247MT765FD_RR_RT815FDIFF_FR
247MT765FS815FDIFF_RR
247MT765HS815FENG
247MT765RA815FFD_FR_LT
247MT765ST_SYS815FFD_FR_RT
247MT765TR_PW_SH815FFD_RR_LT
815FFD_RR_RT
815FFS
815FHS
815FRA
815FTR_PW_SH
MT765DIFF_RR
MT765ENG
MT765FD_RR_LT
MT765FD_RR_RT
MT765FS
MT765HS
MT765RA
MT765ST_SYS
MT765TR_PW_SH

<COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><TBODY>
</TBODY>



Here is what Im hoping to accomplish:

Actual Data
SerialModelCOMPARTMENT
18742305CRENGENG
18742305CRHSHS
FD_RR_LT
FD_RR_RT
FS
RA
2201056815FENGENG
DIFF_RR
FD_FR_LT
FD_FR_RT
FD_RR_LT
FD_RR_RT
FS
HS
RA
TR_PW_SH
DIFF_FR
247MT765DIFF_RRDIFF_RR
247MT765ENGENG
247MT765FD_RR_LTFD_RR_LT
247MT765FD_RR_RTFD_RR_RT
247MT765FSFS
247MT765HSHS
247MT765RARA
247MT765ST_SYSST_SYS
247MT765TR_PW_SHTR_PW_SH

<COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY>
</TBODY>


All my code to this point has failed. Any help is most appreciated. Thank you.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
dthhal,

I have used the worksheet names Actual, and, Master.

Sample data in worksheet Master:


Excel 2007
AB
1MODELCOMPARTMENT
2305CRENG
3305CRFD_RR_LT
4305CRFD_RR_RT
5305CRFS
6305CRHS
7305CRRA
8815FDIFF_FR
9815FDIFF_RR
10815FENG
11815FFD_FR_LT
12815FFD_FR_RT
13815FFD_RR_LT
14815FFD_RR_RT
15815FFS
16815FHS
17815FRA
18815FTR_PW_SH
19MT765DIFF_RR
20MT765ENG
21MT765FD_RR_LT
22MT765FD_RR_RT
23MT765FS
24MT765HS
25MT765RA
26MT765ST_SYS
27MT765TR_PW_SH
28
Master


Sample raw data before the macro in worksheet Actual:


Excel 2007
ABCD
1SerialModelCOMPARTMENT
218742305CRENG
318742305CRHS
42201056815FENG
5247MT765DIFF_RR
6247MT765ENG
7247MT765FD_RR_LT
8247MT765FD_RR_RT
9247MT765FS
10247MT765HS
11247MT765RA
12247MT765ST_SYS
13247MT765TR_PW_SH
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Actual


After the macro:


Excel 2007
ABCD
1SerialModelCOMPARTMENT
218742305CRENGENG
318742305CRHSHS
4FD_RR_LT
5FD_RR_RT
6FS
7RA
82201056815FENGENG
9DIFF_FR
10DIFF_RR
11FD_FR_LT
12FD_FR_RT
13FD_RR_LT
14FD_RR_RT
15FS
16HS
17RA
18TR_PW_SH
19247MT765DIFF_RRDIFF_RR
20247MT765ENGENG
21247MT765FD_RR_LTFD_RR_LT
22247MT765FD_RR_RTFD_RR_RT
23247MT765FSFS
24247MT765HSHS
25247MT765RARA
26247MT765ST_SYSST_SYS
27247MT765TR_PW_SHTR_PW_SH
28
Actual


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub GetMissingCompartment()
' hiker95, 03/29/2013
' http://www.mrexcel.com/forum/excel-questions/694290-double-lookup-something-better.html
Dim r As Long, sr As Long, lr As Long, i As Long, n As Long, nm As Long, sm As Long, em As Long, mc As Long
Dim rng As Range, fr As Long, nr As Long
Application.ScreenUpdating = False
With Sheets("Actual")
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  For r = 2 To lr
ReStart:
    sr = r
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    nm = Application.CountIf(Sheets("Master").Columns(1), .Cells(r, 2).Value)
    If n = nm Then
      .Cells(r, 4).Resize(n).Value = .Cells(r, 3).Resize(n).Value
    Else
      .Rows(r + n).Resize(nm - n).Insert
      .Cells(r, 4).Resize(n).Value = .Cells(r, 3).Resize(n).Value
      Set rng = .Range("C" & r & ":C" & r + nm - n)
      nr = r + n - 1
      sm = Application.Match(.Cells(r, 2), Sheets("Master").Columns(1), 0)
      em = sm + nm - 1
      For i = sm To em Step 1
        fr = 0
        On Error Resume Next
        fr = Application.Match(Sheets("Master").Range("B" & i), rng, 0)
        On Error GoTo 0
        If fr = 0 Then
          nr = nr + 1
          .Cells(nr, 4) = Sheets("Master").Range("B" & i).Value
        End If
      Next i
    End If
    r = sr + nm - 1
    lr = .Cells(Rows.Count, 2).End(xlUp).Row
  Next r
  If r <= lr Then GoTo ReStart
  .Columns(4).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMissingCompartment macro.
 
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
dthhal,

I have used the worksheet names Actual, and, Master.

The only data in worksheet Actual is in columns A, B, and C.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub GetMissingCompartmentV2()
' hiker95, 03/30/2013
' http://www.mrexcel.com/forum/excel-questions/694290-double-lookup-something-better.html
Dim r As Long, sr As Long, lr As Long, i As Long, n As Long, nm As Long, sm As Long, em As Long, mc As Long
Dim rng As Range, fr As Long, nr As Long, nrg As Long
Application.ScreenUpdating = False
With Sheets("Actual")
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  .Cells(1, 4).Resize(, 3).Value = .Cells(1, 1).Resize(, 3).Value
  For r = 2 To lr
    sr = r
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    nm = Application.CountIf(Sheets("Master").Columns(1), .Cells(r, 2).Value)
    If n = nm Then
      nr = .Range("D" & Rows.Count).End(xlUp).Offset(1).Row
      nrg = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
      If nrg > nr Then nr = nrg
      .Cells(nr, 4).Resize(n, 3).Value = .Cells(r, 1).Resize(n, 3).Value
      .Cells(nr, 7).Resize(n, 1).Value = .Cells(r, 3).Resize(n, 3).Value
    Else
      nr = .Range("D" & Rows.Count).End(xlUp).Offset(1).Row
      nrg = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
      If nrg > nr Then nr = nrg
      .Cells(nr, 4).Resize(n, 3).Value = .Cells(r, 1).Resize(n, 3).Value
      .Cells(nr, 7).Resize(n, 1).Value = .Cells(r, 3).Resize(n, 3).Value
      Set rng = .Range("F" & nr & ":F" & nr + nm - n)
      nr = nr + 1
      sm = Application.Match(.Cells(r, 2), Sheets("Master").Columns(1), 0)
      em = sm + nm - 1
      For i = sm To em Step 1
        fr = 0
        On Error Resume Next
        fr = Application.Match(Sheets("Master").Range("B" & i), rng, 0)
        On Error GoTo 0
        If fr = 0 Then
          nr = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
          .Cells(nr, 7) = Sheets("Master").Range("B" & i).Value
        End If
      Next i
    End If
    r = sr + n - 1
  Next r
  .Columns("A:C").Delete
  .Columns("A:D").AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMissingCompartmentV2 macro.
 
D

dthhal

New Member
Joined
Aug 18, 2009
Messages
18
Thanks for your help hiker95. I really appreciate it.

When I run the code Im getting the following error:
Runtime errror 13
Type Mismatch
Debugger is pointing to the line in red below.

Code: 
Sub GetMissingCompartmentV2()
' hiker95, 03/30/2013
' [URL]http://www.mrexcel.com/forum/excel-questions/694290-double-lookup-something-better.html[/URL]
Dim r As Long, sr As Long, lr As Long, i As Long, n As Long, nm As Long, sm As Long, em As Long, mc As Long
Dim rng As Range, fr As Long, nr As Long, nrg As Long
Application.ScreenUpdating = False
With Sheets("Actual")
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  .Cells(1, 4).Resize(, 3).Value = .Cells(1, 1).Resize(, 3).Value
  For r = 2 To lr
    sr = r
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    nm = Application.CountIf(Sheets("Master").Columns(1), .Cells(r, 2).Value)
    If n = nm Then
      nr = .Range("D" & Rows.Count).End(xlUp).Offset(1).Row
      nrg = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
      If nrg > nr Then nr = nrg
      .Cells(nr, 4).Resize(n, 3).Value = .Cells(r, 1).Resize(n, 3).Value
      .Cells(nr, 7).Resize(n, 1).Value = .Cells(r, 3).Resize(n, 3).Value
    Else
      nr = .Range("D" & Rows.Count).End(xlUp).Offset(1).Row
      nrg = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
      If nrg > nr Then nr = nrg
      .Cells(nr, 4).Resize(n, 3).Value = .Cells(r, 1).Resize(n, 3).Value
      .Cells(nr, 7).Resize(n, 1).Value = .Cells(r, 3).Resize(n, 3).Value
     [COLOR=#ff0000] Set rng = .Range("F" & nr & ":F" & nr + nm - n)
[/COLOR]      nr = nr + 1
      sm = Application.Match(.Cells(r, 2), Sheets("Master").Columns(1), 0)
      em = sm + nm - 1
      For i = sm To em Step 1
        fr = 0
        On Error Resume Next
        fr = Application.Match(Sheets("Master").Range("B" & i), rng, 0)
        On Error GoTo 0
        If fr = 0 Then
          nr = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
          .Cells(nr, 7) = Sheets("Master").Range("B" & i).Value
        End If
      Next i
    End If
    r = sr + n - 1
  Next r
  .Columns("A:C").Delete
  .Columns("A:D").AutoFit
End With
Application.ScreenUpdating = True
End Sub

Thanks again for the help.
 
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

dthhal,

You are very welcome.

In order to resolve your current issue, I will need to see your actual workbook containing the two worksheets.

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

dthhal,

Your original raw data is not depicted correctly in your original screenshots.

Thanks for the new workbook.

Worksheet Actual contained 237,696 rows of information.

I have modified the macro to work with your actual raw data, but, each time I ran the macro on your complete raw data on my new laptop, it hung and then crashed.


I may try to re-write the macro using arrays in memory, but, in the meantime:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.
 
D

dthhal

New Member
Joined
Aug 18, 2009
Messages
18
Another bump. This one has me quite stymied.

To perhaps clarify a bit more:

I have several thousand serial numbers. 25,000+ to be exact. Each serial number is unique. However, each serial number is related to a model number. Model numbers are not unique and may have several thousand serial numbers with with same model. Each model has specific compartments associated to them. The model compartment association is in a master list.

In the actual data, not all serial numbers have the proper compartments assigned to them. They may have more added to them, or they may be missing compartments based on the master list. My goal is to identify which compartments are added or missing from each serial number.

Hopefully this may help clarify a little. As hiker95 mentioned there is a lot of data.

Thanks again for the help.
 
You must log in or register to reply here.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,400
Messages
5,837,024
Members
430,467
Latest member
FrazzledbyExcel

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
Top