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]

1		REPORT																	2
		LABOR HOURS BY RESOURCE
		Resource Description	Labor Category	Job Title	EID	Hours
7		Resource A			X				Y				Z	0
																0		END
					January 1, 2017
		MONTHLY LABOR HOURS SUMMARY BY TASK
		Task Title:	Task 1																1-1
		Task Description:	This is Task 1…..																1-1
		Base		1-1
		Resource Description	Jan-2018	Feb-2018	Mar-2018	Apr-2018	May-2018	Jun-2018	Jul-2018	Aug-2018	Sep-2018	Oct-2018	Nov-2018	Dec-2018	Grand Total		1-1
3		Resource A			472	472	472	472	472	472	472	472	472	472	472	472	5664		1-1
																			1-1
					472	472	472	472	472	472	472	472	472	472	472	472	5,664		1-1
		Option 1		1-1
		Resource Description	Jan-2019	Feb-2019	Mar-2019	Apr-2019	May-2019	Jun-2019	Jul-2019	Aug-2019	Sep-2019	Oct-2019	Nov-2019	Dec-2019	Grand Total		1-1
3		Resource A			472	472	472	472	472	472	472	472	472	472	472	472	5664		1-1
																			1-1
					472	472	472	472	472	472	472	472	472	472	472	472	5,664		1-1

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col span="13"><col><col></colgroup>

 

[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?