Index/Match to return second and third instance of a matching column header

novazona

New Member
Joined
Dec 3, 2018
Messages
2
Preface: MicrosoftProject is not an option, and I am trying to avoid having to manually changecolumn names in the data output file. Thecolumns are not always in the same position in the data dump. Vlookup and Offset using the task numberdoesn’t work because the total number of activities on a given task varies, andtherefore, the next activity/occurrence will be in a different column with eachdownload.
I may be overlooking and/or overthinking something here,but I need some help. I have a dataoutput file from an older web-based tool that repeats column headers similar tothe table below, but with up to one hundred rows and a couple hundred columns. I need a way to return the second, third,fourth, etc. instances of a column header. The row headers are Task 1, Task 2, Task 3, Task 4, etc., and the datafile returns a repeating series of columns for each activity within the task:

A
B
C
D
E
F
G
H
I
J
K
1
Task
Activity No (index: 1)
Activity Name

Estimated Complete Date
Actual Complete Date
Status
Activity No (index: 2)
Activity Name

Estimated Complete
Date
Actual Complete
Date
Status
2
Task 1
1
Backup old computer
09/30/17
10/01/18
Delayed due to lack of resources


2
Install new software
10/15/15
10/15/15
Completed on time
3
Task 2
1
Submit purchase order for approval
01/01/17
01/01/17
Submitted on time
2
Order desks
01/02/17
01/09/17
Delayed due to being out of office
4
Task 3
1
Test work station
04/04/17
03/01/17
Able to complete early due to other projects being on hold
2
Write test station report
04/11/17
03/15/17
Able to write report early due to other projects being on hold
<tbody> </tbody>

What I need is a way to quickly status a given task andreview all activities and their estimated completion date, actual completiondate, and status. My first thought waspivot table where the user could filter on the task. I need something like this:
Task (Filter):
Task 1


Activity No
Activity Name
Estimated Complete Date
Actual Complete Date
Status
1
Backup old computer
9/30/2017
10/1/2018
Delayed due to lack of resources
2
Install new software
10/15/2015
10/15/2015
Completed on time
<tbody> </tbody>

Because each “Activity no” column has its own indexnumber designation in the title, but the subsequent fields do not, I’m not ableto compile the data in clean pivot table with the activity on the left and thedates and status in columns. My solutionwas to pivot based on row headers in compact format, tweak to formatting, andthen do index/match to pull the estimated completion date.

Because he columns are not always in the same positionbased on the data dump, the index/match function works well. In cell C11, I have used:
=INDEX($A$2:$K$4,MATCH($C$8,$A$2:$A$4,0),MATCH(C10,$A$1:$K$1,0))
What I need help with is the yellow cells below. How can I get Excel to look at the data dumpand either pull the next instance of Estimated Completion Date to cell C12, ormatch both the task number and the row label and return the estimatedcompletion date? The total number ofactivities on a given task varies, and therefore, the next occurrence will bein a different column for each task. However, the activity number, activity name,estimated completion date, actual completion date and status always repeatorder. Is there a way to somehow embedand offset based on activity number within the index match function?


B
C
D
E
Task
Task 1
10
Row Labels
Estimated Completion Date
Actual Complete Date
Comment
11
Backup old computer
9/30/2017
10/1/2018
Delayed due to lack of resources
12
Install new software



<tbody> </tbody>


 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
If ive read correctly:

=INDEX(INDEX($A$2:$AA$4,MATCH($B$7,$A$2:$A$4,0),0),SMALL(IF($A$1:$AA$1=B$9,COLUMN($A$1:$AA$1)),$A10))

entered CTRL-SHIFT-ENTER
 
Upvote 0



Thank you for the reply, I’m very grateful for your time! I pasted via Microsoft word, and it appears that the tables shifted because the formula you provided references blank cells. I tried manually creating tables here and labeling the rows and columns a little better. I’m trying to create the table manually to see if that works:



Data Source:
A

B
C
D
E
F
G
H
I
J
K
2
Task

Activity No (Index 1)
Activity Name

Estimated Completion Date
Actual Complete Date
Comment
Activity No (Index 2)
Activity Name
Estimated Completion Date
Actual Complete
Comment
3
Task 1


1
Backup old computer
9/30/2017
10/1/2018
Delayed due to lack of resources

2
Install new software
10/15/2015
10/15/2015
Completed on time
4
Task 2


1
Submit purchase order for approval
1/1/2017
1/1/2017
Submitted on time

2
Order desks
1/2/2017
1/9/2017
Delayed due to being out of office
5
Task 3

1
Test work station
4/4/2017
3/1/2017
Able to complete early due to other projects being on hold

2
Write test station report
4/11/2017
3/15/2017
Able to write report early due to other projects being on hold

<tbody>
</tbody>

<tbody>
</tbody>

Need to populate C13:E13
B
C
D
E
9
Task:
Task 1
10
11
Row Labels
Estimated Completion Date
Actual Complete Date
Comment
12
Backup old computer
9/30/2017
10/1/2018
Delayed due to lack of resources
13
Install new software

<tbody>
</tbody>

<tbody>
</tbody>
I adjusted your formula and put in cell C13: =INDEX(INDEX($A$2:$AA$4,MATCH($C$9,$A$2:$A$4,0),0),SMALL(IF($A$1:$AA$1=B$13,COLUMN($A$1:$AA$1)),$B12))entered with control + shift + enter, but no luck.




If ive read correctly:

=INDEX(INDEX($A$2:$AA$4,MATCH($B$7,$A$2:$A$4,0),0),SMALL(IF($A$1:$AA$1=B$9,COLUMN($A$1:$AA$1)),$A10))

entered CTRL-SHIFT-ENTER



 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top