Listing missing info in a grid.

Eedlaw

New Member
Joined
Jul 17, 2019
Messages
1
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top