Find Text (multiple times) in column C - insert array formula - drag down - paste values

[jwb1012]

Hello, I am looking for a macro to search column C and insert an array formula one row below every occurrence of "Resource Description." Then, in column A of each row that a formula will be inserted, there is a number - which calculates how many rows to drag this array formula down. After the array formula is inserted, I would like to drag the formula down that many rows, and paste as values.

Key issue: the cell reference bolded/underlined in the code below needs to refer to 1 row above the cell it is being inserted into AND must be locked with $ as it is shown below. This formula / drag technique is extracting a unique list of Resource IDs from another worksheet, based on certain criteria). I am also having issues with the paste as values.

Appreciate any support you may be able to provide!


Example:

CELL C23= "Resource Description"

ENTERED INTO CELL C24:

{=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($T20='Staffing Plan'!$Y$14:$Y$1008, COUNTIF([I][B][U]$C$23:$C23[/U][/B][/I], 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")}

CELL C28= "Resource Description"

ENTERED INTO CELL C29:

{=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($T20='Staffing Plan'!$Y$14:$Y$1008, COUNTIF([I][B][U]$C$28:$C28[/U][/B][/I], 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")}

"Resource Description" will be found many times in column C and will not always be in the same rows.

 

[Peter_SSs]

I'm not answering your question asked, but if we were to know more about getting the list of unique Resource IDs, It could be that the code could get that list and put it directly into this sheet quicker and more simply than worrying about the array formulas. Would it be worth investigating that?

If so, perhaps you could show us small amount of dummy data from that other worksheet and explain how each list of unique IDs would be obtained manually.

See Attachments for good ways to provide sample data that we can copy/paste to test with.

 

[jwb1012]

[TABLE="width: 1775"]
<tbody>[TR]
[TD]1[/TD]
[TD][/TD]
[TD]REPORT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 16, align: center"]LABOR HOURS BY RESOURCE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Resource Description[/TD]
[TD="colspan: 4, align: center"]Labor Category[/TD]
[TD="colspan: 4, align: center"]Job Title[/TD]
[TD="colspan: 3, align: center"]EID[/TD]
[TD="colspan: 2, align: center"]Hours[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Resource A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 3, align: center"]Z[/TD]
[TD="colspan: 2, align: center"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2, align: center"]0[/TD]
[TD][/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]January 1, 2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 16, align: center"]MONTHLY LABOR HOURS SUMMARY BY TASK[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Task Title:[/TD]
[TD]Task 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Task Description:[/TD]
[TD]This is Task 1…..[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 16, align: center"]Base[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Resource Description[/TD]
[TD]Jan-2018[/TD]
[TD]Feb-2018[/TD]
[TD]Mar-2018[/TD]
[TD]Apr-2018[/TD]
[TD]May-2018[/TD]
[TD]Jun-2018[/TD]
[TD]Jul-2018[/TD]
[TD]Aug-2018[/TD]
[TD]Sep-2018[/TD]
[TD]Oct-2018[/TD]
[TD]Nov-2018[/TD]
[TD]Dec-2018[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Resource A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]5664[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]5,664[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 16, align: center"]Option 1[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Resource Description[/TD]
[TD]Jan-2019[/TD]
[TD]Feb-2019[/TD]
[TD]Mar-2019[/TD]
[TD]Apr-2019[/TD]
[TD]May-2019[/TD]
[TD]Jun-2019[/TD]
[TD]Jul-2019[/TD]
[TD]Aug-2019[/TD]
[TD]Sep-2019[/TD]
[TD]Oct-2019[/TD]
[TD]Nov-2019[/TD]
[TD]Dec-2019[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Resource A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]5664[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]472[/TD]
[TD]5,664[/TD]
[TD][/TD]
[TD]1-1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col span="13"><col><col></colgroup>[/TABLE]

 

[jwb1012]

I am open to any/all ideas...

In the table above, I need to extract 2 different unique lists (the second unique list is repeated multiple times). The first table is a summary of hours by resource for Project 1. On another worksheet called "Staffing Plan" in column K - we have the "Resource Description" column. In column W - we have the Project Number. So I need to extract the unique list of resources assigned to Project 1. Cell $A1 has the Project Number

Inserted into cell C24, C29, etc...

{=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($A$1='Staffing Plan'!$W$14:$W$1008, COUNTIF($C$14:$C14, 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")

Then below the Project 1 table is a Project 1 - Task 1 table. So this unique list extracts the unique resources for Project 1 - Task 1. The "Project Number-Task Number" field is in column Y (instead of column W) of the staffing plan. Cell $T24 has the Project Number-Task Number

=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($T24='Staffing Plan'!$Y$14:$Y$1008, COUNTIF($C$23:$C23, 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")

 

[jwb1012]

When formulas are dragged down, Resource B, Resource C, Resource D, etc appear.

 

[Peter_SSs]

The requirements and expected results are not clear to me from what has been posted.

 

[jwb1012]

Okay, so at a more general level here is the scenario I am trying to sort through:

Worksheets:

1. Staffing Plan - "resource" field in column K and "Project Number" field in column W
2. Labor BOE 1 of X (ex: Labor BOE 1 of 2, Labor BOE 2 of 2) - a different code creates multiple copies of a template based on a user selection and numbers them sequentially - this is in cell A1 and is the "Project Number"

My spreadsheets creates copies of the template for each project, renames them "Labor BOE 1 of N) and numbers them sequentially in cell A1. I need to extract a unique list of resources from the "resource description" filed on the staffing plan (column K) IF the project number in column W matches the project number in cell A1.

I need to insert this unique list in column C every time there is a non blank cell in column A.

Does that make sense?