0

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.

Community
  • 1
  • 1
aglasier
  • 43
  • 5
  • Questions with links to files are not ideal as the links may be malicious, files may be infected, the information needed is not immediately visible in the question so not an [mcve] as per guidelines. It all makes the question less attractive to attempt to answer. Can you 1) Include a screenshot of datalayout and 2) Use a tool like a [table generator](https://www.tablesgenerator.com/markdown_tables) to insert sample data that others can copy and use for testing. – QHarr Jun 06 '18 at 19:29
  • Then show your attempts so far as this shows you have tried yourself and gives us an idea of your approach and helps us target where you are stuck. At the moment, without seeing the sheet, it all feels (to me at least - you asked for feedback; I didn't downvote btw) a bit vague and not clearly defined to me (e.g. Format in what way?) ; and is quite broad, where having a specific programming problem might yield better responses – QHarr Jun 06 '18 at 19:31

1 Answers1

0

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

Kit
  • 341
  • 1
  • 6