Define many names for many ranges of cells

[dtmoran70]

Hi there,

This may require a macro, but i have 2 columns of data that comprise of many groups of the same name in column a, accompanied by a date of a test result in column d, ie,

a d

x dec 1998
x nov 2001
x aug 2007
x jan 2009
y jan 1996
y nov 1999
y jul 2002
y jun 2006
y sep 2012
z apr 1992
z nov 1997
z mar 2000

I want to carry out data validation in selecting the column d dates from drop boxes dependent on the names in column a.

In order to do this, i must define a name for each group of dates with the corresponding name in column a. This is long, considering there are 4000 different names and 25000 dates...

Is there a formula (or macro) where each group of dates in column d can be defined automatically by the corresponding name in column a?

Thanks in advance!!

 

[Fazza]

Some VBA might be best as simple formulas might be too slow. You could check though, just to see how slow it is.

Say there is a cell rng (that has the user's selection of x, y, z) then the data validation formula in another cell can be the list with source defined by

=OFFSET($D$1,MATCH(rng,$A$2:$A$lastrow,0),,COUNTIF($A:$A,rng))

Or some variant of this. If that is too slow, some VBA is easy enough. Please advise.

 

[Fazza]

Sub something_like_this_maybe()        'defined names of form DV_code


    Const str_COLUMN_WITH_CODES As String = "A"
    Const str_COLUMN_WITH_DATA As String = "D"


    Dim i As Long, j As Long
    Dim nm As Excel.Name
    Dim ar As Variant


    'delete any old names first
    For Each nm In ThisWorkbook.Names
        If nm.Name Like "*DV_*" Then nm.Delete
    Next nm
    Set nm = Nothing
    'store codes data into an array
    With Range(Range(str_COLUMN_WITH_CODES & 1), Range(str_COLUMN_WITH_CODES & Cells.Rows.Count).End(xlUp))
        ReDim ar(1 To .Rows.Count, 1 To 1)
        ar = .Value2
    End With


    j = 1
    For i = 2 To UBound(ar, 1) - 1        'start loop from 2 to skip header in first row
        If ar(i + 1, 1) = ar(i, 1) Then
            j = j + 1
        Else
            Range(str_COLUMN_WITH_DATA & i - j + 1).Resize(j).Name = "DV_" & ar(i, 1)
            j = 1
        End If
    Next i
    Range("D" & i - j + 1).Resize(j).Name = "DV_" & ar(i, 1)
    Erase ar


End Sub

 

[dtmoran70]

Hi, thanks for your reply. The formula is displaying a #name error in adjacent cells. The macro has an application-defined or object-defined error. The highlighted line with the error was, "Range(str_COLUMN_WITH_DATA & i - j + 1).Resize(j).Name = "DV_" & ar(i, 1)". I'm wondering if it's something to do with other cells in the workbook that I have named. I have already around 500 unrelated names in other sheets I have done manually. Thanks.

 

[dtmoran70]

Ah my mistake, The macro worked perfectly! I had unfamiliar symbols in some of the names which I changed and the macro worked fine. Thank you very much