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

S

Squizza

New Member
Joined
May 31, 2022
Messages
8
Office Version
  1. 365
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:
ITEMLOCATIONUNITS
AA 120G-08-1100
AA 120G-08-2100
AA 120G-12-1150
AA 60C-04-1350
BB 200D-11-236
BB 200G-19-130
BB 200G-17-230
SH 1001B-02-255
SH 1001B-03-155

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 120100150
AA 60350
BB 2003036
SH 100155

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.

1654078637432.png


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
 
Sort by date Sort by votes
@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:
www.mediafire.com

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
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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
 
Upvote 0
Squizza said:
Can this work with the depending value being a static value in a row?
Click to expand...
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?
 
Upvote 0
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.

1654250814682.png


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
Click to expand...
 
Upvote 0
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.
Click to expand...
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
Click to expand...

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
ABCDEFGHIJKL
1COUNTRYCITYPERSON
2ChinaBEIJINGGuangzhouZachariahZaynPaxtonAri
3GermanyBERLINBonnAdam
4IndonesiaBandungJAKARTASurabayaJonathanIkerRodrigoHunter
5United StatesChicago (IL)Los Angeles (CA)New York (NY)GriffinMakai
Sheet2


Data validation in col B:C:
Squizza 1a.xlsm
ABC
1COUNTRYCITYPerson
2ChinaGuangzhouPaxton
3GermanyAdam
4IndonesiaJAKARTA
5United States
Sheet1
Cells with Data Validation
CellAllowCriteria
B2:B5List=toXDAV
C2:C5List=toYDAV


Sample workbook:
www.mediafire.com

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
 
Upvote 0
Solution
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
ABCDEFGHIJKL
1COUNTRYCITYPERSON
2ChinaBEIJINGGuangzhouZachariahZaynPaxtonAri
3GermanyBERLINBonnAdam
4IndonesiaBandungJAKARTASurabayaJonathanIkerRodrigoHunter
5United StatesChicago (IL)Los Angeles (CA)New York (NY)GriffinMakai
Sheet2


Data validation in col B:C:
Squizza 1a.xlsm
ABC
1COUNTRYCITYPerson
2ChinaGuangzhouPaxton
3GermanyAdam
4IndonesiaJAKARTA
5United States
Sheet1
Cells with Data Validation
CellAllowCriteria
B2:B5List=toXDAV
C2:C5List=toYDAV


Sample workbook:
www.mediafire.com

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
Click to expand...
Brilliant! Thanks so much Akuini! :)
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Dropdown list not working
Replies
0
Views
167
pyclen
P
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
337
Greenbehindthecells
G
F
  • Solved
Dynamic Array Formula Problem
Replies
5
Views
105
KRice
K
N
  • Solved
Filter Function returns #N/A from Dynamic Array but works with cell reference
Replies
6
Views
572
Peter_SSs
Peter_SSs
W
  • Question
Defined coloring of dynamic diagrams
Replies
0
Views
219
WalmitAal
W

Forum statistics

Threads
1,214,621
Messages
6,120,568
Members
448,972
Latest member
Shantanu2024

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
Back
Top