Index Match help needed

[colmsmyth]

Hi all

I have a report that is run at regular intervals, when it is run I need to comment on the findings, then at the next run I want to pull across the identical comments for the same previous finding.

The column structure is as follows for when it is first run on Monday

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Domain[/TD]
[TD]Issues[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Form1[/TD]
[TD]Something is wrong[/TD]
[TD]No its not[/TD]
[/TR]
</tbody>[/TABLE]





Next when its run on Friday

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Domain[/TD]
[TD]Issues[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Form1[/TD]
[TD]Something is wrong[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]Form1[/TD]
[TD]Form is missing item 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form2[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









So you can see that I have two new records between Monday and Friday runs but the original record from Monday is missing the comment.

I have had some success with Index/match on this but it seems to go wrong if the records don't link up or something. Key point here are that 'Domain' and 'Issues' columns are to be considered key variable as the same comment might exist for multiple domains.

Hope you can help.

 

[Scott T]

with your first report on sheet1 and the next report on sheet2 comments in column C. This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly excel will but {} around the formula.

=IFERROR(INDEX(Sheet1!$C$2:$C$3,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$3&Sheet1!$B$2:$B$3,0)),"")

 

[colmsmyth]

Thanks for the help, I have tried this but it doesn't work....See below my actual tables

The value of 0 is returned rather than the comment, any ideas ?

Sheet 1

[TABLE="width: 459"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]DOMAIN[/TD]
[TD]ISSUE[/TD]
[TD]COMMENT[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]AEACN value not found in 'Action Taken with Study Treatment' non-extensible codelist[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]NULL value in AEDECOD variable marked as Required[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]AE start date is after the latest Disposition date[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]Model permissible variable added into standard domain[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]Permissible variable with missing value for all records[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]No Treatment Emergent info for Adverse Event[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CM[/TD]
[TD]Model permissible variable added into standard domain[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 974"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]DOMAIN[/TD]
[TD]ISSUE[/TD]
[TD]COMMENT[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]AEACN value not found in 'Action Taken with Study Treatment' non-extensible codelist[/TD]
[TD]This is due to the ongoing nature of the trial. Definitive disposition events will be attained once subjects complete the trial or early terminate from the trial[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]NULL value in AEDECOD variable marked as Required[/TD]
[TD]EPOCH added to subject-level domains[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]AE start date is after the latest Disposition date[/TD]
[TD]This can be clarified with the analysis team, to ensure treatment-emergent flags are derived on an ADaM level[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]Model permissible variable added into standard domain[/TD]
[TD]Data Issue: Coding values missing for 2 AE's for subject HBI-8000-302/020103. To be followed-up with DTL[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]Permissible variable with missing value for all records[/TD]
[TD]Not all qualifiers for serious AE populated yet. Due to ongoing nature of the study[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]No Treatment Emergent info for Adverse Event[/TD]
[TD]Issues:

1. Why was AEACN removed? This is an expected variable and cannot be removed from the domain model. AEACN should be mapped relative to the IMP (which is the HBI-8000 drug) and then the other item on the eCRF can be mapped to a supplemental qualif[/TD]
[/TR]
[TR]
[TD]CM[/TD]
[TD]Model permissible variable added into standard domain[/TD]
[TD]EPOCH added to subject-level domains[/TD]
[/TR]
</tbody>[/TABLE]

with your first report on sheet1 and the next report on sheet2 comments in column C. This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly excel will but {} around the formula.

=IFERROR(INDEX(Sheet1!$C$2:$C$3,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$3&Sheet1!$B$2:$B$3,0)),"")

 

[Scott T]

If it is returning 0 then the match was found but the comment cell was blank. Are all comments on the same row as the Domain and issue?

 

[63falcondude]

Scott's formula is working for me. Make sure that you adjust the ranges in the formula to fit your data.

 

[colmsmyth]

Hi both

Sorry for the delay coming back...I managed to get Scott's example working on the data tables I provided originally however no matter what I do....if I expand the tables the code will not work....

Can I make the code work for say 1000 rows static.... ?even if these returns a load of 0's on empty rows...See below, only one new comment came over
SHEET1

[TABLE="width: 417"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Domain[/TD]
[TD]Issues[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Form1[/TD]
[TD]Something is wrong[/TD]
[TD]No its not[/TD]
[/TR]
[TR]
[TD]Form3[/TD]
[TD]Form is not complete[/TD]
[TD]something 1[/TD]
[/TR]
[TR]
[TD]Form4[/TD]
[TD]Form is not complete[/TD]
[TD]something 2[/TD]
[/TR]
[TR]
[TD]Form5[/TD]
[TD]Form is not complete[/TD]
[TD]something 3[/TD]
[/TR]
</tbody>[/TABLE]

SHEET2

[TABLE="width: 463"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Domain[/TD]
[TD]Issues[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Form1[/TD]
[TD]Something is wrong[/TD]
[TD]No its not[/TD]
[/TR]
[TR]
[TD]Form1[/TD]
[TD]Form is missing item 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form2[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form3[/TD]
[TD]Form is not complete[/TD]
[TD]something 1[/TD]
[/TR]
[TR]
[TD]Form4[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form5[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form6[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form7[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Form8[/TD]
[TD]Form is not complete[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

Colm

Scott's formula is working for me. Make sure that you adjust the ranges in the formula to fit your data.

 

[63falcondude]

If you want to make the formula work for 1000 rows of data, simply change each "3" in the formula to "1001".

I said 1001 instead of 1000 because the data starts on row 2.

The formula would then become:
=IFERROR(INDEX(Sheet1!$C$2:$C$1001,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$1001&Sheet1!$B$2:$B$1001,0)),"") Ctrl Shift Enter

Since the formula is on Sheet2, you can get rid of "Sheet2!" in the formula leaving you with:
=IFERROR(INDEX(Sheet1!$C$2:$C$1001,MATCH(A2&B2,Sheet1!$A$2:$A$1001&Sheet1!$B$2:$B$1001,0)),"") Ctrl Shift Enter

 

[Scott T]

You should only have to change the ranges referring to sheet1 so may be $C$2:$C$1001

=IFERROR(INDEX(Sheet1![COLOR=#ff0000]$C$2:$C$3[/COLOR],MATCH(Sheet2!A2&Sheet2!B2,Sheet1![COLOR=#ff0000]$A$2:$A$3[/COLOR]&Sheet1![COLOR=#ff0000]$B$2:$B$3[/COLOR],0)),"")

 

[colmsmyth]

Thanks a million....it just didn't want to work for me...very odd but it is now working ....

Thanks to you also Scott....your reply also the same...both working

If you want to make the formula work for 1000 rows of data, simply change each "3" in the formula to "1001".

I said 1001 instead of 1000 because the data starts on row 2.

The formula would then become:
=IFERROR(INDEX(Sheet1!$C$2:$C$1001,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$1001&Sheet1!$B$2:$B$1001,0)),"") Ctrl Shift Enter

Since the formula is on Sheet2, you can get rid of "Sheet2!" in the formula leaving you with:
=IFERROR(INDEX(Sheet1!$C$2:$C$1001,MATCH(A2&B2,Sheet1!$A$2:$A$1001&Sheet1!$B$2:$B$1001,0)),"") Ctrl Shift Enter

 

[63falcondude]

Glad to help.