+ Reply to Thread
Results 1 to 9 of 9

Determine which columns are tied to changes in first column?

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Determine which columns are tied to changes in first column?

    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:
    1. Find some way of determining which columns are dependent on the duplicates/values in column A
    2. 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
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Determine which columns are tied to changes in first column?

    Hi,

    This sounds like a job for "Show Formulas" which is located on the Formulas Tab.

    http://www.howtogeek.com/175759/how-...in-excel-2013/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Determine which columns are tied to changes in first column?

    You might look at Trace Dependant and Trace President on the Formula Tab.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Determine which columns are tied to changes in first column?

    Thank you both for your suggestions but there are no formulas involved.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Determine which columns are tied to changes in first column?

    in the end I wrote the entire function without any help.

    I have to say though, I am very surprised by some of the results that were returned after running on real world data?!

    Please Login or Register  to view this content.

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Determine which columns are tied to changes in first column?

    Hi mc84excel,
    try so
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Determine which columns are tied to changes in first column?

    Thanks nilem, this looks interesting. I will give it a shot over the next few days and let you know how it worked

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Determine which columns are tied to changes in first column?

    Sorry nilem, I have been tied up and haven't had a chance to test your code yet. I will aim to get back to you mid next week.

  9. #9
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Determine which columns are tied to changes in first column?

    Hi Nilem, I finally got the chance to try your code out. It came up with the same results as my latest version however yours was much quicker! Thanks +1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Random Pick 4 column of datab but keep 2 cells tied together.
    By Thupp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2013, 06:11 AM
  2. Fomula to Calculate Multiple Columns with TIED Values
    By lumo1985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2008, 01:23 AM
  3. Replies: 2
    Last Post: 05-17-2008, 07:33 AM
  4. Determine if column is within range of columns
    By Michael Beckinsale in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2006, 07:30 AM
  5. [SOLVED] Determine last row in columns 1-4 to copy a formula in column 5
    By GEB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2005, 05:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1