Pull lines from multiple tables based on matching criteria

mharper90

Board Regular
Joined
May 28, 2013
Messages
112
Office Version
  1. 365
Platform
  1. MacOS
I have a workbook with several tables. Each table is for a specific equipment model. Then within the table are the inventoried serial numbers, the location of that specific item, and its calibration expiration date. These are the tables used for tracking overall inventory of each piece of equipment. This is an example of a portion of one of the tables. There are 37 tables like this within the workbook for many different models.

Model: EQ-265
Serial NumberLocationCal Due DateInitialNotes
E00444VAN24-Apr-21
E00445CAL25-Feb-21
E0044656426-Jun-21
E004631727-Oct-21
E0046956426-Jun-21
E00473CAL24-Aug-21
E0048556419-Nov-21
E004945643-Apr-21
E004951722-Jul-21
E0049856424-Apr-21

I would like to create a set of tables on another sheet in the workbook that reorganizes the information by location so that it's easier to see which items in a specific location are due for calibration. In the above example set, there are only 4 locations, but overall there are actually 11 possible locations. My ideal location summary table would look something like this. (Note: This table below has 2 lines extracted from the above table and the remaining lines would be extracted from other tables for different model numbers.)

Location: CAL
ModelSerial NumberCal Due DateInitialNotes
EQ-265E0044525-Feb-21
EQ-265E0047324-Aug-21
EQ-549E0133219-Nov-21
EQ-549B000643-Apr-21
EQ-549E0138622-Jul-21
LP-36J0047124-Apr-21

I'd prefer to do this without VBA, but am open to VBA alternatives if needed to accomplish the task. I've tried a few different varieties of Index and Match, but I just can't quite get anything to work.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
723
If you accept VBA, please try this.
Paste on sheet module and type like just "CAL" on B2.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim w, buf, LR As Long, NLR As Long

    If Intersect(Target, Range("B1")) Is Nothing Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Range(Range("A3"), Range("A3").SpecialCells(xlLastCell).Offset(1, 0)).ClearContents
        For Each w In Worksheets
            If w.Name <> ActiveSheet.Name Then
                LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
                w.Range("A2").AutoFilter field:=2, Criteria1:=Target
                If WorksheetFunction.Subtotal(3, w.Range("B:B")) > 1 Then
                    buf = Replace(w.Range("A1").Value, "Model:", "")
                    w.Range(w.Range("A3"), w.Cells(Rows.Count, 1).End(xlUp)).Copy Cells(LR, 2)
                    w.Range(w.Range("C3"), w.Cells(Rows.Count, 3).End(xlUp)).Copy Cells(LR, 3)
                    NLR = Cells(Rows.Count, 2).End(xlUp).Row
                    Range(Cells(LR, 1), Cells(NLR, 1)).Value = buf
                    w.Range("A2").AutoFilter
                End If
            End If
        Next
    End If
    Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,518
Messages
5,625,269
Members
416,086
Latest member
CaptainGD

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