Search code examples
excelexcel-formulaexcel-tables

Extract related data from one table to another

Here is my worksheet, it doesn't show up right in google docs so you may have to download:

https://drive.google.com/open?id=1Im8RY_cokV2NBtkU9YwMhzkVWwUT_Gn_

I need to grab the data from the first table, format it a bit and enter it into the second table. I've put an example in the second tab. I'm looking to make a template where I paste the raw data in the first tab and the second tab will highlight any issues that need correction.

The notes in the worksheet I think explains what I'm looking to do.

Solution

  • You can think of your issue in terms of two problems.

    First, you want to create a dynamic list of unique values "File Name" or column B on the "Summary" tab. You can do that by following the answers in this question

    A second (simpler but static) way of doing this, is merely pasting Column B from the first tab into Column A of the second tab, then clicking on the "Remove Duplicates" button under the "Data" tab in the "Data Tools" section of Excel's "Data" tab. Remove Duplicates

    Once you have your list of unique values, all you have to do to gather the rest is use a combination TEXTJOIN and IF array functions

    So on the Sheet4, use

    {=MID(TEXTJOIN(,TRUE,IF($A3=Summary!$B$2:$B$12047,IF(LEFT(Summary!$C$2:$C$12047,11)="MARK DETAIL",Summary!$C$2:$C$12047,""),"")),14,100)}

    the second IF statement finds cells that start with MARK DETAIL, the first IF statement filters matching file names, the TEXTJOIN strings all the results together and the MID function cuts off the MARK DETAIL

    You can adapt this function to

    {=TEXTJOIN(,TRUE,IF($A3=Summary!$B$2:$B$12047,IF(LEFT(Summary!$A$2:$A$12047,4)="HEAD",Summary!$D$2:$D$12047,""),""))}

    for column C and 

    {=TEXTJOIN(,TRUE,IF($A3=Summary!$B$2:$B$12047,IF(LEFT(Summary!$A$2:$A$12047,4)="OEM_",Summary!$E$2:$E$12047,""),""))}

    for column D

    It should be noted that these formulas are all array functions, meaning you will have to enter them in the cell and then activate them by pressing Ctrl+Shift+Enter