+ Reply to Thread
Results 1 to 4 of 4

Looking to combine and filter tables

  1. #1
    Registered User
    Join Date
    09-08-2019
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    2

    Looking to combine and filter tables

    Hello, I need help coming up with a formula to combine two or more tables of data into a single table. In the example below, I want to combine the rows of tables A and B together into a single table, while filtering out the rows that are blank entirely and those rows with a zero value of "Amount". Knowing how to sort by a particular column would be good too. I know how to do all this in google sheets with a query command, but I don't know how in excel and I don't have any SQL skills. Can I accomplish this in a single formula??? Thanks for the help. Max

    A Dept Project Account Amount B Dept Project Account Amount
    100 20 0 1 56 110 20 0 2 12
    101 40 0 2 0 110 40 0 3 34
    102 60 0 3 78 111 60 0 4 0


    103 80 0 4 34 112 80 0 5 67
    104 100 0 5 0 113 100 0 6 78
    105 120 0 6 6 114 120 0 7 0
    106 140 0 7 12 115 140 0 8 23

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,432

    Re: Looking to combine and filter tables

    Cannot decipher your columns.

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    09-08-2019
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    2

    Re: Looking to combine and filter tables

    Hi Alan - thanks for the tips! See attached. I need help coming up with a formula
    to combine two or more tables of data into a single table. In the example attached,
    I want to combine the rows of tables A and B together into a single table, while
    filtering out the rows that are blank entirely and those rows with a zero value of
    "Amount". Knowing how to sort by a particular column would be good too. I know how
    to do all this in google sheets with a query command, but I don't know how in excel
    and I don't have any SQL skills. Can I accomplish this in a single formula??? Thanks
    for the help. Max
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,432

    Re: Looking to combine and filter tables

    Using Power Query which is an Excel Add in available to you. Bring each table into PQ. Close and Load each to a connection only. Append table two to table one. Filter out the nulls and zero in the amount column. See the Mcode below and the attached file.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] I need formula or macro to filter a number of items from different tables at single filter
    By Karthikeyannb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2014, 11:04 AM
  2. Replies: 0
    Last Post: 06-13-2013, 07:00 PM
  3. One filter/ Master filter multiple pivot tables based on worksheet...
    By jlworden in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-10-2013, 11:16 AM
  4. [SOLVED] One filter/ Master filter for multiple pivot tables...
    By jlworden in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-09-2013, 04:35 PM
  5. Duplicate Report Filter and Row Labels Filter Across Multiple Pivot Tables
    By tash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2013, 05:32 PM
  6. Filter several pivot tables by one programmed date range filter in Excel 2003
    By olewka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2011, 11:49 AM
  7. Replies: 3
    Last Post: 06-23-2009, 01:36 PM

Tags for this Thread

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