+ Reply to Thread
Results 1 to 4 of 4

Need dynamic formula to create a single list of unique values from two separate tables.

  1. #1
    Registered User
    Join Date
    05-17-2016
    Location
    Norfolk, VA
    MS-Off Ver
    Excel 2016
    Posts
    2

    Need dynamic formula to create a single list of unique values from two separate tables.

    Hi all, I have two separate tables, each on their own worksheet within one workbook. I have one column in each table that I want to combine into one list of unique values. I have a query that refreshes both tables which will have a different number of rows in each table with each refresh, sometimes expanding, sometimes contracting. I cannot use VBA due to limitations with the software I use for the SQL queries so it has to be formula based. I don't want users to have to copy and paste data and remove duplicates either. Any ideas or is this not possible?

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Need dynamic formula to create a single list of unique values from two separate tables

    Are you using tables in the Excel structured referencing sense? If so, TableName[KeyFieldName] would autosize.

    I'll assume not. Instead, the SQL queries leave their results in plain, unstructured ranges. If one key field is in A!X2:X10001 and the other in B!Y2:Y10001, and there should be nothing in the rows below the SQL query results, then best in the worksheet in which you're constructing the list of distinct key field values to calculate the bottommost rows in the current SQL query results.

    The following would be in a 3rd worksheet.

    CORRECTIONS

    A1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Select A2:C2, type
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    hold down [Ctrl] and [Alt] keys, and press [Enter]. This should produce an array formula in A2:C2.

    A5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill A5 down into A6:A20004.

    B5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill B6 down into B7:B20004.

    As long as the SQL queries produce 10,000 or fewer rows, column B in this 3rd worksheet would be the distinct values from the key field columns in the other 2 worksheets. The point here is that it's a lot easier to consolidate the key field columns from both worksheets into a single column, then determine the combined distinct values from the combined column than to do this piecemeal.
    Last edited by hrlngrv; 11-20-2020 at 03:06 PM. Reason: corrections

  3. #3
    Registered User
    Join Date
    05-17-2016
    Location
    Norfolk, VA
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Need dynamic formula to create a single list of unique values from two separate tables

    Yes, I am using tables in the Excel structured referencing sense. Trying your suggestion, I'm getting #N/A in cells A1 and A2 which in turn results in all formulas returning #N/A. I tried using you code changing A!X2:X10001 to Sales_Line[SO Unique Identifier] which is the actual table column and in B2 I changed B!Y2:Y10001 to Sales_Shipment_Line[SH Unique Identifier]. I beleive if I can get A1 and A2 working, the rest of your formulas will work.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Need dynamic formula to create a single list of unique values from two separate tables

    I must have fixed the formulas in my test worksheet after I copied them into my response. The rightmost ,0 shouldn't have been in either the A1 or B1 formulas. I've made corrections in my previous response. However, if you're using structured references, you could instead use ROWS(...) to find the number of rows in each table.

+ 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] Create a list of unique values from several columns in a dynamic table.
    By schurchill39 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-28-2018, 06:51 PM
  2. Create a unique list of items from two tables
    By XLalbania in forum Excel General
    Replies: 22
    Last Post: 01-15-2017, 06:16 PM
  3. Replies: 2
    Last Post: 06-16-2016, 08:09 PM
  4. Formula to create a list of unique values / remove duplicates
    By TicklyTigger in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-12-2014, 08:39 AM
  5. Create unique list from dynamic list with dupes
    By kfryar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-10-2014, 09:20 PM
  6. How do create a Dynamic List on a Separate Worksheet
    By graphictees in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-18-2011, 01:56 PM
  7. Replies: 8
    Last Post: 12-30-2008, 12:06 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