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

Domain	Issues	Comment
Form1	Something is wrong	No its not

<tbody>
</tbody>

Next when its run on Friday

Domain	Issues	Comment
Form1	Something is wrong
Form1	Form is missing item 2
Form2	Form is not complete

<tbody>
</tbody>

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

DOMAIN	ISSUE	COMMENT
AE	AEACN value not found in 'Action Taken with Study Treatment' non-extensible codelist	0
AE	NULL value in AEDECOD variable marked as Required
AE	AE start date is after the latest Disposition date
AE	Model permissible variable added into standard domain
AE	Permissible variable with missing value for all records
AE	No Treatment Emergent info for Adverse Event
CM	Model permissible variable added into standard domain

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sheet 2

DOMAIN	ISSUE	COMMENT
AE	AEACN value not found in 'Action Taken with Study Treatment' non-extensible codelist	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
AE	NULL value in AEDECOD variable marked as Required	EPOCH added to subject-level domains
AE	AE start date is after the latest Disposition date	This can be clarified with the analysis team, to ensure treatment-emergent flags are derived on an ADaM level
AE	Model permissible variable added into standard domain	Data Issue: Coding values missing for 2 AE's for subject HBI-8000-302/020103. To be followed-up with DTL
AE	Permissible variable with missing value for all records	Not all qualifiers for serious AE populated yet. Due to ongoing nature of the study
AE	No Treatment Emergent info for Adverse Event	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
CM	Model permissible variable added into standard domain	EPOCH added to subject-level domains

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

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

Domain	Issues	Comment
Form1	Something is wrong	No its not
Form3	Form is not complete	something 1
Form4	Form is not complete	something 2
Form5	Form is not complete	something 3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

SHEET2

Domain	Issues	Comment
Form1	Something is wrong	No its not
Form1	Form is missing item 2
Form2	Form is not complete
Form3	Form is not complete	something 1
Form4	Form is not complete
Form5	Form is not complete
Form6	Form is not complete
Form7	Form is not complete
Form8	Form is not complete

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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.