unique values list from a range proven harder than first imagined...

[moebios]

Well, upfront, I have a very basic knowledge of excel software and I hope my question isn’t too lame…

Having said that, I have multiple storage boxes which I need to organize. Each box holds 100 units in defined fixed positions (1-100). Units have a very dynamic range of names (#ID) and also a variable number of units will be loaded to each box at a given time, meaning I don’t have a fixed list of #IDs for the units in each box or all boxes together. The unit #ID always comes first in its description (highlighted in yellow in figure 1) in the space I have allocated to each item, followed by other parameters which are not important now.

In figure 1 units at positions 21-23 are all ID: HEK293, the ones in positions 24 and 34 are ID: NSC-34, unit 31 is “CHO” and positions 32 and 33 are empty. The image above is from an excel file I have made to organize these boxes and it was working pretty well when I had only a couple of boxes to organise. Unfortunately, now I not only have a growing number of boxes but also a greater diversity of #IDs to account for.

In order to automate the counting of items/box and the number of empty spaces I have a list under the table I have made (figure 2) which gives me the number of units for each unique #ID. So far I have been typing the list manually, which has not been very practical with too many boxes...

Finally, my question is: How can I automatically list the newly added items to a list of unique values in the same sheet, in order to longer need to type them individually? I mean, if I add now a single unit ID'ed “XXX” at position 91 this would be a new unit never listed before in this sheet, and in my system it will need to be typed in manually. Instead, I want to figure how to make it appear as unique item number “7” in my list (see list underneath the table in figure 2)? I want to be able to automatically list any unique value added to rows “2”, “6”, “10”, “14”, “22”, “26”, “30”, “34” and “38” (as can be seem from figure 2) to the list underneath (starting from P39, or any other given position)

On note, I have searched for “list unique values from a given range of cells” and the solutions I have found didn’t work for me (or I may be using them wrong!)… I get either “N/A” or “0” in the listing cells. Advanced filtering also haven't work for me. I have tried to follow the example on the website below without success.

Excel formula: Extract unique items from a list | Exceljet

BTW, I am using office 2016.

Any help will be greatly appreciated!

Best,

mcastro

 

[Legacy 456155]

And I have a very basic knowledge of formulas, but this is pretty easy using VBA.
Here is a pseudo example you can try if you have any problems. --> "ID Count.xlsm"

Add the following to a standard module:

Sub CreateBoxRecord()
    Dim bu As BoxUnit, c As Integer, r As Integer, d As Object
 
    Set d = CreateObject("Scripting.Dictionary")
 
    For r = 2 To 38 Step 4
        For c = 2 To 20 Step 2
            With ActiveSheet
                With .Cells(r, c)
                    If Not d.Exists(.Text) Then
                        Set bu = New BoxUnit
                        bu.ID = .Text
                        bu.ColorCode = .Offset(, 1).Interior.Color
                        bu.Count = 1
                        d.Add bu.ID, bu
                    Else
                        With d(.Text)
                            .Count = .Count + 1
                        End With
                    End If
                End With
            End With
        Next
    Next
 
    With [n44:q1000]
        .ClearFormats
        .ClearContents
    End With
 
    Dim v, t As Range
    Set t = [n44]
 
    v = d.Items
    For r = 0 To d.Count - 1
        t = r + 1
        t.Offset(, 1).Interior.Color = v(r).ColorCode
        t.Offset(, 2) = v(r).ID
        With t.Offset(, 3)
            .Value = v(r).Count
            .Font.Bold = True
        End With
        Set t = t.Offset(1)
    Next
End Sub

Create a class module named BoxUnit. Add the following:

Public ID As String
Public ColorCode As Long
Public Count As Integer

Now run CreateBoxRecord on the activesheet.
Post back if you need help getting it runnin'.

 

[moebios]

Hey Dataluver!

Thank you very much for you reply and sorry for the late response. Your suggestion will sure shortcut a lot of work for me and I really appreciate the file you have put together.

I am still not sure why the system was made to replicate the values down the columns when I type in the first cell though. Maybe I am completely missing the point of strategy behind it and I am sorry if this is the case... While it sure helps to add large batches of the same items, it also crops a bit my freedom (or adds a bit more work) when I have 100 individual non-repeating items to add to that table. No problems here and I can sure work around that. The way I first had in mind should mostly allow single or multiple individual entries each time. I should be able to add or remove entries without affecting other positions.

Anyway, the duty of polishing of what you have provided so far is on me and it actually motivated me to get deeper understand of the VBA world. Thanks!

BTW, nice touch by making the colour of the neighbouring cell to follow the entry on the generated list. Thanks for that!

I wonder if it is possible to automatically come up with a different colour in the neighbouring cells every time I type in a unique item entry? I mean, as in fugure 1, if position 1 was labelled "A" and position 2 labelled "B", they would both have distinct colour in their neighbouring cells, but if I label position 3 "A" again the same colour shown in position one would be associated to this position neighbouring cell... Most likely the answer is YES and I just need to search further.

Thanks a lot and all the best!

 

[Legacy 456155]

I am still not sure why the system was made to replicate the values down the columns when I type in the first cell though. Maybe I am completely missing the point of strategy behind it and I am sorry if this is the case...

No. The code should work just fine with your original scenario. My example file was set up as it is to easily generate some test data instead of requiring me to type it all in. Ignore my formulas and punch in your own data or simply apply the code to a test copy of your current data. The code I provided is a basic example that is quite rigid because it's dependent upon the exact structure of your "boxes" as stated. If that will not change, then we're off to a good start. If it might be subject to change, the code could be edited to accommodate variant structures.

I wonder if it is possible to automatically come up with a different colour in the neighbouring cells every time I type in a unique item entry? I mean, as in fugure 1, if position 1 was labelled "A" and position 2 labelled "B", they would both have distinct colour in their neighbouring cells, but if I label position 3 "A" again the same colour shown in position one would be associated to this position neighbouring cell... Most likely the answer is YES and I just need to search further.

Sure. One of the formula wizards on this board might be able to help you along with some conditional formatting. We could then edit the code above to look at the conditional formatting back-color. If not, I can surely help you with this issue as well. If you do post on another thread, please let me know so that I can monitor the post. Just shoot me a message or post a link in this thread. Have a good one!