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:
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.
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.
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.
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?
Not sure if it will all work, but love trying!
TIA
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 SubThe 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:=FalseNot sure if it will all work, but love trying!
TIA
