This is not going to be easy to explain.
I have an excel report I am generating. It has a table of records that contain duplicate values in one field (column A).
What I want to do is to:
- Find some way of determining which columns are dependent on the duplicates/values in column A
- Automate the above (either pure SQL and/or VBA function) so it gives me a list of columns that are NOT dependent on column A + a list of columns that MAY be dependent on column A
I need to explain goal #1 in more detail.
This will be much easier by visual example.
Attached is a mockup of the report (with most columns removed and with most values obfuscated for privacy).
Note that the values in Column A contains duplicate values.
For each group of 'duplicates', we check that column values are equal. (e.g. B2 = B3, C2 = C3, D2 = D3)
If any column does NOT contain the same values for that group of duplicates then this column is NOT dependent on column A (i.e. any other duplicate set which may have the same values in that column is merely coincidence. One mismatch for any group negates that whole column)
To give a few examples:
- B2 = B3 (B may be dependent), B4=B5 (B may be dependent), B18 <> B19 = column B is NOT dependent
- C6=C7 (col C may be dependent), C10=C11=C12 (col C may be dependent), C20 <> C20:C23 = Column C is NOT dependent
- Column G doesn't contain any values = Column G may be dependent
Bookmarks