LEN Function to Create Dynamic Array to Use for Named Ranges/Drop Down Lists

Squizza · Jun 1, 2022

Hi, I am working with some source data is updated weekly, which has a list of Items in one column and Units in another column. There can be multiple instances of each item and the corresponding Units. I am trying to create a dynamic array of unique items in one column, and then in the adjacent columns, all the unique values associated with that item.

Source data example:

ITEM	LOCATION	UNITS
AA 120	G-08-1	100
AA 120	G-08-2	100
AA 120	G-12-1	150
AA 60	C-04-1	350
BB 200	D-11-2	36
BB 200	G-19-1	30
BB 200	G-17-2	30
SH 1001	B-02-2	55
SH 1001	B-03-1	55

What my goal is to have the data displayed as below, but with spilled data, not with a formula in the top cell and then dragged down to copy in all below cells.

AA 120	100	150
AA 60	350
BB 200	30	36
SH 1001	55

I have refined a LET formula (below), that I found online to give me the all the unique values, but the Units don't spill, they are 'Text Joined' in one call and separated by a comma e.g AA 120 | 100,150 so on. That was the intent of the original LET formula I have been working from, but I can seem to work out how to make the unique unit values, spill instead of joining in the one cell.

Excel Formula:

=LET( data, $B$3#,
       item, INDEX(data,,1), units, INDEX(data,,3),
       uItem, TRANSPOSE(SORT(UNIQUE(item))),
       uUnits, SORT(UNIQUE(TRANSPOSE(IF(item=uItem,units,"")),TRUE),,,TRUE),
       uStr, LET( m, uUnits,
                   rSeq, SEQUENCE( ROWS(m) ),
                   L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - 1,
                   i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
                  IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) ),
       CHOOSE( {1,2}, TRANSPOSE(uItem), uStr ) )

Any tips or assistance is greatly appreciated

Oh, may as well detail my end goal, maybe there is a better way. The source data (that updates weekly) is in an external workbook and is copied to $B$3#. On another sheet there is a complete list of all unique items, some that have no Units value. The end goal is to have a drop down list showing all unique Unit values for each item that has a Unit value on the source data. Like the below image in this example.

After I have the dynamic array, I will use VBA to update all named ranges for each Item, so that it refers to all the updated, corresponding unique Unit values.

VBA Code:

Sub RangeRename()

Dim n As Name
    For Each n In Names
        If Mid(n.RefersTo, 7) = "C" Then n.RefersTo = n.RefersTo & "#"
    Next n
End Sub

The VBA above is all I have so far. It only adds the # to the RefersTo cell, but I will add more code to first delete all named ranges, and then create named ranges from the dynamic range maybe something like this?

VBA Code:

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")
Set StartCell = Range("D9")

  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

  Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False

Not sure if it will all work, but love trying!

TIA

Akuini · Jun 2, 2022

@Squizza
There's another way to accomplish the job, which is by using UDF. By using this method you only need 1 named range, instead of recreating many named ranges as your code does.
Note:
I assumed the helper table has already been created, so this part is only to deal with setting up the data validation.
How to set it up (I'm using my own example):
1. Insert this UDF in a code module:

VBA Code:

Function xDAV(c As Range) As Range
'Akuini, June 2022
Dim r As Range
With Sheets("Sheet2").Columns("B:B") 'data source
    
    Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not r Is Nothing Then Set xDAV = r.Offset(, 1).Resize(WorksheetFunction.CountIf(.Cells, c.Value), 1)
    
End With

End Function

2. In sheet1: set up dependent data validation
Dependent data validation is in cells A2:B7
Select B2 > create a named range "toXDAV", scope: Workbook, refer to: =xDAV(Sheet1!$A2)
Note: it's important that you're selecting B2 while creating the named range, otherwise it won't work properly.
Select B2 > create data validation > List > Source: =toXDAV
Copy B2 down to row 7.

The workbook:

Squizza 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Akuini · Jun 2, 2022

Sorry, the code in post #11 is wrong, it should be the one in the sample workbook, i.e:

VBA Code:

Function xDAV(c As Range) As Range
Dim r As Range
 
With Sheets("Sheet2").Columns("B:B") 'data source
    
    Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not r Is Nothing Then
            If r.Offset(, 1) = "" Then
                'if col L is empty then data validation is empty
                Set xDAV = r.Offset(, 1) 'blank cell
            Else
                rc = r.End(xlToRight).Column 'get last column with data
                Set xDAV = r.Offset(, 1).Resize(, rc - r.Column)
            End If
    End If


End With


End Function

Squizza · Jun 3, 2022

Thanks a lot for your input Akuini, really appreciated! Does there need to be 2 column dependent data validation? Can this work with the depending value being a static value in a row? I have so many items, it is not feasible to use a drop down list for the dependent value.

Also, just wondering how this will work with 3 different sheets of data to be used in the data validation. I have 3 dynamic tables created on different sheets. They are used for the named ranges for the data validation on another sheet.

Sheet 1 - static list of all items (hundreds of them) with 3 drop down lists across 3 adjacent columns, 1 for each item location (A, B & C)
Sheet 2 - dynamic data source for location A
Sheet 3 - dynamic data source for location B
Sheet 4 - dynamic data source for location C

The data for each sheet has the same startcell reference if that helps?

Thanks heaps again, I'm loving learning this.. (only started with VBA a couple of weeks ago, and only been using Excel for more than basic spreadsheets, for 8 months or so. I'm in sales but love this problem solving analytical stuff... even though I only get a few hours a week to spend on it, I am helping my team out so much with what I can do now :D

Akuini · Jun 3, 2022

Squizza said:
Can this work with the depending value being a static value in a row?

I do not understand what you want. Can you explain in more detail using an example?
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?

Squizza · Jun 3, 2022

In your sample workbook, you had column A and B with drop down lists. The value selected in column A drop down list, created the values available in the drop down list in B. What I need is just the drop down box in B that is dependent on the value in A which is not selected with a drop down list. So in column A, there is a column of 200+ rows of unique values, in column B the drop down list will be populated based on the value in column A in the same row.

If your example fit what I need, the data would be as below... I'm pretty sure this is possible, was just confirming.

The bigger question is the 2nd part to my previous post if you can assist with this

Also, just wondering how this will work with 3 different sheets of data to be used in the data validation. I have 3 dynamic tables created on different sheets. They are used for the named ranges for the data validation on another sheet.

Sheet 1 - static list of all items (hundreds of them) with 3 drop down lists across 3 adjacent columns, 1 for each item location (A, B & C)
Sheet 2 - dynamic data source for location A
Sheet 3 - dynamic data source for location B
Sheet 4 - dynamic data source for location C

Akuini · Jun 3, 2022

Squizza said:
What I need is just the drop down box in B that is dependent on the value in A which is not selected with a drop down list.

Just remove data validation in col A.

Squizza said:
The bigger question is the 2nd part to my previous post if you can assist with this

I made an example with only 2 sub-categories, i.e City & Person. You can add the 3rd sub-category by yourself.
You need to add another named range & another UDF, amend the column of City & Person in the UDF in this part:
'CITY in col C
'col C is 1 columns to the right from col B
n = 1 'change to suit

'person in col I
'col I is 7 columns to the right from col B
n = 7 'change to suit

UDF:

VBA Code:

Function xDAV(c As Range) As Range
Dim r As Range, f As Range, n As Long, rc As Long
 
With Sheets("Sheet2").Columns("B:B") 'COUNTRY
    
    Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    'CITY in col C
    'col C is 1 columns to the right from col B
    n = 1 'change to suit
    
    If Not r Is Nothing Then
        Set f = r.Offset(, n)
            If f.Offset(, 1) = "" Then
                Set xDAV = f 'single cell or blank cell
            Else
                rc = f.End(xlToRight).Column 'get last column with data
                Set xDAV = f.Resize(, rc + 1 - f.Column)
            End If
    End If

End With

End Function

Function yDAV(c As Range) As Range
Dim r As Range, f As Range, n As Long, rc As Long
 
With Sheets("Sheet2").Columns("B:B") 'COUNTRY
    
    Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    'person in col I
    'col I is 7 columns to the right from col B
    n = 7 'change to suit
    
    If Not r Is Nothing Then
        Set f = r.Offset(, n)
            If f.Offset(, 1) = "" Then
                Set yDAV = f 'single cell or blank cell
            Else
                rc = f.End(xlToRight).Column 'get last column with data
                Set yDAV = f.Resize(, rc + 1 - f.Column)
            End If
    End If

End With

End Function

Data source:

Squizza 1a.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

1

COUNTRY

CITY

PERSON

2

China

BEIJING

Guangzhou

Zachariah

Zayn

Paxton

Ari

3

Germany

BERLIN

Bonn

Adam

4

Indonesia

Bandung

JAKARTA

Surabaya

Jonathan

Iker

Rodrigo

Hunter

5

United States

Chicago (IL)

Los Angeles (CA)

New York (NY)

Griffin

Makai

Sheet2

Data validation in col B:C:

Squizza 1a.xlsm

A

B

C

1

COUNTRY

CITY

Person

2

China

Guangzhou

Paxton

3

Germany

Adam

4

Indonesia

JAKARTA

5

United States

Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2:B5	List	=toXDAV
C2:C5	List	=toYDAV

Sample workbook:

Squizza 1a

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Squizza · Jun 7, 2022

Akuini said:
Just remove data validation in col A.

I made an example with only 2 sub-categories, i.e City & Person. You can add the 3rd sub-category by yourself.
You need to add another named range & another UDF, amend the column of City & Person in the UDF in this part:
'CITY in col C
'col C is 1 columns to the right from col B
n = 1 'change to suit

'person in col I
'col I is 7 columns to the right from col B
n = 7 'change to suit

UDF:

VBA Code:

Function xDAV(c As Range) As Range Dim r As Range, f As Range, n As Long, rc As Long With Sheets("Sheet2").Columns("B:B") 'COUNTRY Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 'CITY in col C 'col C is 1 columns to the right from col B n = 1 'change to suit If Not r Is Nothing Then Set f = r.Offset(, n) If f.Offset(, 1) = "" Then Set xDAV = f 'single cell or blank cell Else rc = f.End(xlToRight).Column 'get last column with data Set xDAV = f.Resize(, rc + 1 - f.Column) End If End If End With End Function Function yDAV(c As Range) As Range Dim r As Range, f As Range, n As Long, rc As Long With Sheets("Sheet2").Columns("B:B") 'COUNTRY Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 'person in col I 'col I is 7 columns to the right from col B n = 7 'change to suit If Not r Is Nothing Then Set f = r.Offset(, n) If f.Offset(, 1) = "" Then Set yDAV = f 'single cell or blank cell Else rc = f.End(xlToRight).Column 'get last column with data Set yDAV = f.Resize(, rc + 1 - f.Column) End If End If End With End Function

Data source:

Squizza 1a.xlsm
A B C D E F G H I J K L
1 COUNTRY CITY PERSON
2 China BEIJING Guangzhou Zachariah Zayn Paxton Ari
3 Germany BERLIN Bonn Adam
4 Indonesia Bandung JAKARTA Surabaya Jonathan Iker Rodrigo Hunter
5 United States Chicago (IL) Los Angeles (CA) New York (NY) Griffin Makai
Sheet2

Data validation in col B:C:

Squizza 1a.xlsm
A B C
1 COUNTRY CITY Person
2 China Guangzhou Paxton
3 Germany Adam
4 Indonesia JAKARTA
5 United States
Sheet1
Cells with Data Validation
Cell Allow Criteria
B2:B5 List =toXDAV
C2:C5 List =toYDAV

Sample workbook:

Squizza 1a

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Brilliant! Thanks so much Akuini!

Akuini · Jun 7, 2022

You're welcome, glad to help & thanks for the feedback.

LEN Function to Create Dynamic Array to Use for Named Ranges/Drop Down Lists

Squizza

New Member

Akuini

Well-known Member

Squizza 1

Excel Facts

Akuini

Well-known Member

Squizza

New Member

Akuini

Well-known Member

Squizza

New Member

Akuini

Well-known Member

Squizza 1a

Squizza

New Member

Squizza 1a

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

LEN Function to Create Dynamic Array to Use for Named Ranges/Drop Down Lists

New Member

Well-known Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock