{"id":2153,"date":"2023-06-24T15:52:57","date_gmt":"2023-06-24T14:52:57","guid":{"rendered":"https:\/\/mammoth.enspire.in\/?p=2153"},"modified":"2026-03-02T17:59:23","modified_gmt":"2026-03-02T17:59:23","slug":"combine-tables-in-excel","status":"publish","type":"post","link":"https:\/\/mammoth.io\/mammoth_v2\/combine-tables-in-excel\/","title":{"rendered":"Combine Tables In Excel: In Minutes (No-Code Solution)"},"content":{"rendered":"<p>Here\u2019s a scenario (that happens to be typical in the manufacturing world) &#8211; Dataset A (ERP data) contains a date range, and Dataset B (Sensor Data) contains rows that fall within the date range of dataset A.<\/p>\n<div class=\"single_article_content w-richtext\">\n<p>Example:<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone wp-image-3733 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2152-1.png\" alt=\"\" width=\"934\" height=\"366\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2152-1.png 934w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2152-1-300x118.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2152-1-768x301.png 768w\" sizes=\"(max-width: 934px) 100vw, 934px\" \/><\/figure>\n<h2>The Objective<\/h2>\n<p>Our goal is to combine the two datasets with \u201cTime\u201d as the common column. We need to place the Order ID next to the Sensor values so it looks like this:<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img decoding=\"async\" class=\"alignnone wp-image-3734 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-2.png\" alt=\"\" width=\"889\" height=\"443\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-2.png 889w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-2-300x149.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-2-768x383.png 768w\" sizes=\"(max-width: 889px) 100vw, 889px\" \/><\/figure>\n<h2>Why this is not a trivial task<\/h2>\n<p>At the surface, this may look like a simple JOIN (in SQL) or VLOOKUP (in Excel) exercise, but it\u2019s not. Because the \u201cTime\u201d columns in both tables describe a range of time values but not exact times. This operation involves a complex, multi-step SQL operation.If you\u2019re trying to do this with code, you need to\u00a0&#8211;<\/p>\n<ul role=\"list\">\n<li>Perform an Outer JOIN with the two datasets<\/li>\n<li>Then deal with missing or null values. (which you can solve through another\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/47073076\/postgresql-fill-null-values-in-timeserie-query-with-previous-value?rq=1\">non-trivial piece of code<\/a>.)<\/li>\n<\/ul>\n<h2>A simple, code-free and automated solution using Mammoth<\/h2>\n<p>Firstly, for those who don\u2019t know about\u00a0<a href=\"https:\/\/www.mammoth.io\/\">Mammoth Analytics<\/a>, it is a lightweight, code-free data management platform.<\/p>\n<p>It provides powerful tools for the entire data journey, including data retrieval, consolidation, storage, cleanup, reshaping, analysis, insights, alerts and more.<\/p>\n<h3><strong>Okay, let\u2019s get started<\/strong><\/h3>\n<p>The following steps describe how we can perform this task, easily, and in a couple of minutes.<\/p>\n<p>First, you need to bring your data into Mammoth.\u00a0<a href=\"https:\/\/mammoth.io\/mammoth_v2\/features\/#bring-your-data-together\">We offer a lot of easy ways to do that<\/a>.<\/p>\n<p>For our example, I just uploaded the two CSV files from my computer into Mammoth.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img decoding=\"async\" class=\"alignnone wp-image-3735 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-3.png\" alt=\"\" width=\"980\" height=\"344\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-3.png 980w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-3-300x105.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-3-768x270.png 768w\" sizes=\"(max-width: 980px) 100vw, 980px\" \/><\/figure>\n<p>Now, our goal is to combine two separate datasets into one and then perform some transformations on the Combined Data.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\">\n<div><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3736 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-4.png\" alt=\"\" width=\"594\" height=\"349\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-4.png 594w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-4-300x176.png 300w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/div><figcaption><\/figcaption><\/figure>\n<h4><strong>Step 1: Send the ERP data into another Dataset called \u201cCombined Data\u201d<\/strong><\/h4>\n<p>To do this, open up any of the datasets, go to \u201cData Preparation\u201d and use the \u201cMerge \u00a0&amp; Branch out &gt; Branch out to dataset\u201d option.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3737 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-5.png\" alt=\"\" width=\"1024\" height=\"396\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-5.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-5-300x116.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-5-768x297.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>Now send this data to a new Dataset called \u201cCombined Data\u201d<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3738 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-6.png\" alt=\"\" width=\"1024\" height=\"584\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-6.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-6-300x171.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-6-768x438.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>If you\u2019ve selected \u201cKeep this task in the data pipeline\u201d, you\u2019ll now see a step in the pipeline indicating your action.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3739 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-7.png\" alt=\"\" width=\"1024\" height=\"748\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-7.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-7-300x219.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-7-768x561.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<h4><strong>Step 2: Send the Sensor data into the same \u201cCombined Data\u201d Dataset<\/strong><\/h4>\n<p>Open up the Sensor Dataset and perform similar steps as Step 1, only this time it needs to be sent to the existing \u201cCombined Data\u201d dataset.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3739 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-7.png\" alt=\"\" width=\"1024\" height=\"748\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-7.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-7-300x219.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-7-768x561.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>In your Data Library, you\u2019ll see a new dataset called \u201cCombined Data\u201d.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3740 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-8.png\" alt=\"\" width=\"1024\" height=\"439\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-8.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-8-300x129.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-8-768x329.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>When you open that file, you will see the following:<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3741 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-9.png\" alt=\"\" width=\"1024\" height=\"555\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-9.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-9-300x163.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-9-768x416.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<h4><strong>Step 3: Fill the missing or null values in the \u201cCombined Data\u201d Dataset<\/strong><\/h4>\n<p>First, let\u2019s sort the data in ascending order by Time.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3742 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-10.png\" alt=\"\" width=\"1024\" height=\"663\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-10.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-10-300x194.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-10-768x497.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>Which gives you this:<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3743 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-11.png\" alt=\"\" width=\"1024\" height=\"548\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-11.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-11-300x161.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-11-768x411.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>Now, to fill the missing Order ID values, we just need to use the\u00a0<a href=\"https:\/\/mammoth.io\/mammoth_v2\/docs\/content\/feature_guide\/tasks\/fill.missing.values.html\">Fill Missing Values<\/a>\u00a0function.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3744 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-12.png\" alt=\"\" width=\"1024\" height=\"672\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-12.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-12-300x197.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-12-768x504.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>Select the Order ID column in \u201cfill empty cells in\u201c drop-down and to fill the values from the cells above the empty cell, select \u201cabove\u201c in \u201cwith values from\u201c drop-down. Its worth noticing that the grouping is not applicable here, so we will fill the missing values without adding a grouping rule.<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3745 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-13.png\" alt=\"\" width=\"1024\" height=\"699\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-13.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-13-300x205.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-13-768x524.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<p>Which gives you this final result:<\/p>\n<figure class=\"w-richtext-figure-type- w-richtext-align-center\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3746 size-full\" src=\"http:\/\/mammoth.enspire.in\/wp-content\/uploads\/2023\/06\/2153-14.png\" alt=\"\" width=\"1024\" height=\"430\" srcset=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-14.png 1024w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-14-300x126.png 300w, https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/06\/2153-14-768x323.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<h2>And we\u2019re done<\/h2>\n<p>We\u2019ve achieved a code-free solution to combining two time-series datasets in a couple of minutes.<\/p>\n<p>An extra bonus \u2014 automation is built-in. New data coming into any of the original datasets automatically sends it to the \u201cCombined Data\u201d Dataset<\/p>\n<p>Once you\u2019re done with this task, you can explore other ways of getting your data in the right shape using the Mammoth platform.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Here\u2019s a scenario (that happens to be typical in the manufacturing world) &#8211; Dataset A (ERP data) contains a date range, and Dataset B (Sensor Data) contains rows that fall within the date range of dataset A. Example: The Objective Our goal is to combine the two datasets with \u201cTime\u201d as the common column. We [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":2154,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"content-type":"","footnotes":""},"categories":[15],"tags":[73],"class_list":["post-2153","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-data-integration"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts\/2153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/comments?post=2153"}],"version-history":[{"count":8,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts\/2153\/revisions"}],"predecessor-version":[{"id":10265,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts\/2153\/revisions\/10265"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/media\/2154"}],"wp:attachment":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/media?parent=2153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/categories?post=2153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/tags?post=2153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}