Listing missing info in a grid.

[Eedlaw]

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.

 

[Roger Govier]

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

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