Get distinct values from a dynamic column into another worksheet

Question

I have a sheet named Inventory which has a list of sample numbers. This list grows automatically, so I have to include the whole column. It looks something like this:

I have another sheet named Inventory Count in which I want to count the unique sample numbers. For this data, the inventory count should look something like this:

I tried doing this with advanced filter but it doesn't allow me to have 2 separate sheets which is crucial in my case.

Please help me with any formula or VBA code. Thanks in advance

May be [this link](https://stackoverflow.com/a/59684203/9808063) will give you unique values in 1D array and then you can use countif — Naresh, May 26 '20 at 14:36
Or you can use the first formula in [this link](https://stackoverflow.com/a/60772656/9808063) — Naresh, May 26 '20 at 14:51
I dont think that a macro will auto run everytimethere is a change in the main list — Raghav Chamadiya, May 26 '20 at 14:55
You certainly **CAN** copy the results of the Advanced Filter to another worksheet. Just activate the destination sheet before you select Advanced Filter. Then select the data and criteria ranges from the source sheet; and the destination range from the destination sheet. — Ron Rosenfeld, May 26 '20 at 15:42
@RonRosenfeld I figured out the Advanced filter macro, can you tell me a way to run the macro every time there is a new value in the Inventory sheet? — Raghav Chamadiya, May 26 '20 at 17:11

Naresh · Accepted Answer · 2020-05-26T18:27:56.570

Reference link

You can define name for the Column B in "Inventory" sheet like InventoryRecords

InventoryRecords = =OFFSET(Inventory!$B$1,,,COUNTA(Inventory!$B:$B),1)

In "inventory Count" Sheet, enter formula in A2

=IFERROR(INDEX(InventoryRecords,MATCH(0,INDEX(COUNTIF($A$1:A1,InventoryRecords),0,0),0)),"")

Copy it down until you get blanks and additional say 100 rows. I would copy for number of rows equivalent to the number of relevant inventory items in the inventory master.

In column B enter countif formula.

Ron Rosenfeld's suggestion in comments to the question to select unique items using advance filter, you can also record a macro. It is more efficient than the formula above which will keep calculating every time.

In "Inventory" Sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Dim SourceSh As Worksheet, DestSh As Worksheet
    Set SourceSh = Worksheets("Inventory"): Set DestSh = Worksheets("Inventory Count")

    Dim FilterRng As Range, UniqueRng As Range, DestRng As Range, Cl As Range
    Set FilterRng = SourceSh.Range("B1:B" & Range("B" & SourceSh.Rows.Count).End(xlUp).Row)
    FilterRng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

    Set UniqueRng = FilterRng.SpecialCells(xlCellTypeVisible)
    Set DestRng = DestSh.Range("A1")

    UniqueRng.Copy DestRng
    Application.CutCopyMode = False

    Set DestRng = DestSh.Range("A2:A" & UniqueRng.Cells.Count)
    For Each Cl In DestRng
    Cl.Offset(0, 1) = WorksheetFunction.CountIf(FilterRng, Cl)
    Next

    FilterRng.AdvancedFilter Action:=xlFilterInPlace, Unique:=False
End If
Application.DisplayAlerts = True
End Sub

Thanks a lot, I decided to go with the Advanced filter macro, I just need to figure out how to run the macro every time there is a change in the Sample number column of the Inventory sheet. — Raghav Chamadiya, May 26 '20 at 17:14
You can use Worksheet_Change event and check If the target and column B "Inventory Sheet" intersect. — Naresh, May 26 '20 at 17:31

Get distinct values from a dynamic column into another worksheet

1 Answers1