Results 1 to 2 of 2

Thread: Listing missing info in a grid.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Listing missing info in a grid.

    Some Helpful advice please.

    Imagine a collection of 10 items (First to Tenth) each item comes in one of 10 possible colours. So to collect them all I need all 100.

    Now I can create a table with the numbers as column titles and the colours as the row title which would allow me to tick off as I collect. This works great but as it fills up it gets harder and harder to spot the missing items, I don't want to use anything like conditional formatting or similar to colour them.


    Iwant it in some way to create a wish list of the missing ones on its own sheet. So it would need to pull the Column Title and the Row Title and compile it into a list of all items not ticked off in the grid (e.g. Sixth Green). This list thencould easily put into other programs or on to a mobile device as a shoppinglist.

    Soof course the list will have 100 combinations to start and none by the end.This would ideally be self-updating as the table gets filled in.

    Also while I mention the use of a 10 by 10 grid it would be good for it to work with any number or rows and columns where data could be Staff Vs Training, Patients Vs Required Testing.

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Abergavenny, Wales, UK
    Posts
    259
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listing missing info in a grid.

    Hi
    Assuming your grid starts in A1 on Sheet 1, and the top row has the headers and the first column the row names e.g.

    Red Green Yellow
    Roger 10 20
    Jack 15 30
    Charles 14 21

    Assuming your workbook also has a Sheet 2, then the following code will produce a list of the Blank cells in column A of Sheet 2 as
    Red Jack
    Green Roger
    Yellow Charles

    The code will adjust for the number of rows and columns on your Sheet 1
    Code:
    Sub CheckData()    Dim ary, outary
        Dim r As Long, c As Long, i As Long, j As Long, k As Long
    
    
        Dim wsO As Worksheet
        Set wsO = Sheet2
        wsO.Cells.Delete
        k = 1
        Sheet1.Activate
        ary = ActiveSheet.UsedRange
        r = UBound(ary)
        c = UBound(ary, 2)
        For j = 2 To r
            For i = 2 To c - 1
                If ary(i, j) = "" Then
                    wsO.Cells(k, 1) = ary(1, j) & " " & ary(i, 1)
                    k = k + 1
                End If
            Next i
        Next j
        Sheet2.Activate
    
    
    End Sub
    --
    Roger Govier
    Technology 4 U

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •