Need to list unique values from (3) separate table columns

I am trying to list unique values from three separate table columns into a new (output) list. I've attached a sample sheet showing what I'm hoping to be able to accomplish.

Also, I would like to use an 'index/match' formula on the output list to grab cost values from the three tables and match them with their respective part numbers.

My 'desired output' list shows the formulas that I'm using currently which pulls data from a single table, but I cannot figure out how I'd be able to use a similar formula that would grab data from all three of the tables.

I hope that I'm explaining this correctly!

Any help would be greatly appreciated.

Thank you,

Is it an option to add all 3 tables into 1 table and after that analyse the data?

with data In one table, Dv could be based on "Xpart","Ypart" if these were added as a column. Having multiple tables is not be recommended as they add nothing but complexity.

It is easier to "dis-assemble" than to "re-assemble".

Hi John,

The worksheet I made is used to create estimates for my job. I'm trying to keep the tables separately becuase different users will input values (parts) specific to the area they work in. the output sheet gives a master list (in no particular order) that will be used to place orders and keep track of inventory. If I lumped everything into one table, then there would be extra coding required by the input teams in order to keep the data organized.

I was hoping there was an easy way to reference three or more tables, for example, index((XPARTS[Xparts])*(YPARTS[Yparts])*(ZPARTS[ZParts]),Match...'-type of formula.

I'm also trying to stay away from VBA if at all humanly possible. I can tinker around with formulas to get them to work, but the VB coding is just way over my head!!

Thanks,

... how many tables (more than the 3 in your sample)? and are they organised as per your sample?

As of now, there are only three tables, but I may need to add more in the future. They're organized in a similar manner as shown in my sample sheet, although in my actual workbook, the the output sheet is on a different tab/worksheet than the input tables.

I've attached a more detailed test sheet. it's actually pulled from the workbook i made, but i removed all of the irrelevant tabs and columns. If you go to the 'materials' tab, you'll see (3) tables set up, with some sample information entered. if you go to the 'takeoff' tab, you'll see where someone would use the data validation to enter some of the info listed in the 'materials' tab to create the job estimate (just for reference, on my takeoff sheet; SB stands for 'substrate material', SBQ is 'substrate material qty, HST stands for 'Hardware Spec', HSQ is Hardware Spec Qty', DSL stands for 'Desired Security Lock', and DSQ is Desired Security Lock Qty').


If you then go to the 'job data' tab, you'll see a table showing one of the part numbers (PN1). I'm just trying to list AA and BB underneath PN1. Please disregard all of the '#REF!' that shows up, there were 6 tabs that I deleted for labor operations, customer data, etc. that fed into the 'job data' sheet. That info was not relevant to what I'm trying to accomplish here so i just removed it to keep it as simple as possible.

An alternative to VBA is Power Query where the tables are appended to each other in a connection only. See the attached file to view the steps. Results are shown in Column N under your expected results and match your expectation.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

What if you used SUMIF across all the tables. PT1 .... PTn can be input by typing PT1 in the first cell and then dragging the fill handle down. PT2.... will autofill PT2, PT3 etc.
Then in O6 this formula.

Formula:

Please Login or Register  to view this content.

deleted ...... power query good way