Populating a table from a database based on birthdate

iain9090

New Member
Joined
Sep 7, 2014
Messages
4
Hi, I'm new to this and a relative novice when it comes to excel. I would greatly appreciate any help.

In my workbook, on sheet 1, I have my main database. A list of names with corresponding birth dates and other information.
On sheet 2 I wish to populate a table of a number of these names, based on their birthdate.

For example, anyone born between 01-Jan-1994 and 31-Dec-1995, will appear in this table on sheet 2.

Is this possible to do?

Thanks in advance
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
.
.

Simplest way would by to use an AutoFilter and copy the filtered data to Sheet2...

Or, if you want a macro, which column contains your birth dates?
 
Upvote 0
.
.

Place the following code in a standard code module in your workbook.

Code:
Sub Test()

    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Lb As Long
    Dim Ub As Long
    Dim Cnt As Long
    Dim i As Long
    
    'Set worksheet variables
    With ThisWorkbook
        Set Sht1 = .Worksheets("Sheet1")    'change as necessary
        Set Sht2 = .Worksheets.Add
    End With
    
    'Set range containing dates
    With Sht1
        Set Rng = Range(.Range("C2"), _
            .Range("C2").End(xlDown))
    End With
        
    'Copy field headings
    Sht2.Range("A1:C1").Value = _
        Sht1.Range("A1:C1").Value
    
    'Set criteria for dates
    Lb = DateSerial(1994, 1, 1)
    Ub = DateSerial(1995, 12, 31)
    
    'Loop through dates and copy
    'records that meet criteria...
    
    Cnt = 1
    For Each Cell In Rng
        If CLng(Cell.Value) >= Lb And _
            CLng(Cell.Value) <= Ub Then
            Cnt = Cnt + 1
            For i = 1 To 3
                Sht2.Cells(Cnt, i).Value = _
                    Cell.Offset(0, i - 3).Value
            Next i
        End If
    Next Cell
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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