Search code examples
excelvlookup

Automatically combine two tables which are individually updated

I have 2 datasets which are continuously updated. I want to combine them in one table. The structure of these two tables are not the same. Is this possible in Excel? And if so, how?

  • UPDATE: I simplified the example. Hopefully to make it more clear
    what I'm looking for:

I have 2 datasets which I want to combine:

Data1

Data2

I want to combine in one table underneath each other, so it will look like this. Note that the colors of the headers represent the same data but in different columns in Data1 and Data2. The pictures shows how it would look like:

Overview

So horizontally the data is from the same dataset but in a different order. But vertically, it can be data from Data1 or Data2. I hope my question is clear, otherwise let me know and I'll try to make it more clear

  • Old example:

For example, I have these two data sets:

[Data1][https://i.sstatic.net/UBEe8.png]

[Data2][https://i.sstatic.net/BNlTs.png]

And I want to combine them in one overview tab like this. I have included which columns needs to be combined:

[Overview][https://i.sstatic.net/wpdCi.png]

So horizontally the data is from the same dataset but in a different order. But vertically, it can be data from Data1 or Data2. I hope my question is clear, otherwise let me know and I'll try to make it more clear

I tried using VLOOKUP, but I'm not sure how to do this with combining 2 lookups in 1 column

Larger Data2

Solution

  • I found the solution for my problem here: https://trumpexcel.com/combine-multiple-worksheets/

    However, additional question. I can match tables with each other by using the same table headers. For example:

    let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}, {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}),

    But, can I also combine two columns with different names? If I try to do something like this, it will not work:

    let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}, {"AlternativeName_NAME.1", "AlternativeName_NAME.2", "AlternativeName_NUMBER", "AlternativeName_RANDOM"}),

    Because, now I'm adding extra columns to make it work. Where the RAW data initially uses "AlternativeName_NAME.1". I now added a new column with header "NAME.1" and with the formula =[@[AlternativeName_NAME.1]]

    @Derby9421 do you know maybe?