Inputting new Headers and pulling data from other tables (same WB) into main table

This is part of a bigger project where I take 2-5 CRV files and automatically create tables (each named after the sheet name (with “_” instead of spaces) in each and merge them into one workbook. I have accomplished that (w/ help from this board). Now I want to take that workbook (Trackwise_7-22-2014) and add selected columns from the other tables to the correct cells of “query_export_results.” I will run the macro from “Importing Columns.xlsm. So,
1. Add headers to the primary Table (query_export_results) as I list them in column B of Sheet1 of ImportingColumns1.xlsm. New columns just added to the right of the existing columns. In the example, these would be Product Code, Product Name and Cause Type in columns I, J and K (extending the table)
2. Go down each new column in the Table and if the other table has a value for that row (based on PR ID), then enter it.
a. This is made more complex because some other tables might have a PR ID listed more than once and in those cases, I want Excel to concatenate with commas or line breaks.
I see this as some sort of CASE situation.
So VBA goes through I2:I16 of table “query_export_results” (under heading “Product Code”). It has to know that product code is from table “Product_Information” so it looks there for matches and counts the number of times 900 appears (once). Then
Case 0
Enter “N/A”
Case 1
Enter value from “Product_Information
Case >1
Do a loop = Value & CHAR(010) & next Value

Am I on the right track? I am in over my head. PR numbers will always be in order so multiples will always be in order and the query_ex… table does not ever contain duplicates.
Questions?

Hi ChemistB,

This got it off the ground - but I've got to go out now (I renamed your books for this code):

Please Login or Register  to view this content.

Thanks XLAdept,
From there I was able to replace the question marks at the end like so

Please Login or Register  to view this content.

That completes part 1. I read the selected fields off sheet1 and transfer them to the main table headers.

Now part 2. Go to the appropriate tables for each of these headers and fill in the blanks on the main table. I will continue to work on it here. I appreciate the help!

Okay, so thinking through this is pseudo code
For each cell in new columns going down
For each PR number in table query_export_results
In the table representing the current header (ITabR(I) )
CountIF(Col 1 of Appropriate Table, =current PR)
With CASE =0 then "NA",
With CASE = 1 then enter value under same header
With CASE >1 then loop and concatenate, then enter

I just don't know enough VBA to do this with TABLEs, I might revert to used ranges and INDEX/MATCH

Hi ChemistB,

Try this:

Please Login or Register  to view this content.

Close, when run on my example, it doesn't concatenate duplicates (or triplicates etc). For example, PR 908 has 2 entries in the Product_Information table but the code is only copying the first one. I need multiples concatenated, preferribly with line breaks between each. Hmmmm

Hi ChemistB,

I had forgotten about the concatenations - here:

Please Login or Register  to view this content.

Thanks for the rep!

Excellent. This phase of my project is complete. Thanks for all your help XL

You're welcome, although I still feel indebted to you for past kindnesses

LOL, if you ever need a convoluted Formula approach, let me know.

Thanks - will do