Match, fill and create records

[Pramod91]

Hello everyone, I am new to alteryx.
I have two excel sheets. Here is an example,

Sheet 1

Medicine	Phase	Unique ID	Subsequent ID	Disease	Patient Group	Line of Therapy
a	1	172	1	Lung Cancer in adults, children or elderly	Adults	1st
a	1	172	1	Lung Cancer in adults, children or elderly	Adults	1st
a	1	172	2	Lung Cancer in adults, children or elderly	Children	1st
a	1	172	2	Lung Cancer in adults, children or elderly	Children	1st
a	1	172	3	Lung Cancer in adults, children or elderly	Elderly	1st
a	1	172	3	Lung Cancer in adults, children or elderly	Elderly	1st
c	1	45	1	Tuberculosis in adults	Adults	1st
c	1	45	1	Tuberculosis in adults	Adults	1st

Sheet 2

Medicine	Phase	Unique ID	Subsequent ID	Disease	Age Group	Line of Therapy
b	2			Lung Cancer in adults, children or elderly	-	-
b	2			Lung Cancer in adults, children or elderly	-	-
d	2			Tuberculosis in adults	-	-

What I need to do is in the sheet 2 if the disease matches with that in sheet 1, then the columns of Unique Id, Subsequent Id, patient group and line of therapy should be filled as per the data in sheet 1.

Also, if the disease matches and there is a Subsequent Id greater than 1 in the sheet 1 then for that disease in sheet 2, records should be created as per the subsequent Id of sheet 1 with all the columns.

Can anyone help me to tackle this?

[bpatel]

hi @Pramod91 ,

could you please share what your final output should look like?

[Pramod91]

Hi bpatel,

Thanks for coming back to me. The output I need is - The sheet 2 should be updated like this

Medicine	Phase	Unique ID	Subsequent ID	Disease	Age Group	Line of Therapy	Status
b	2	172	1	Lung Cancer in adults, children or elderly	Adults	1st	Filled taking reference from sheet 1
b	2	172	1	Lung Cancer in adults, children or elderly	Adults	1st	Filled taking reference from sheet 1
d	2	45	1	Tuberculosis in adults	Adults	1st	Filled taking reference from sheet 1
b	2	172	2	Lung Cancer in adults, children or elderly	Children	1st	Record Created and filled taking reference from sheet 1
b	2	172	2	Lung Cancer in adults, children or elderly	Children	1st	Record Created and filled taking reference from sheet 1
b	2	172	3	Lung Cancer in adults, children or elderly	Elderly	1st	Record Created and filled taking reference from sheet 1
b	2	172	3	Lung Cancer in adults, children or elderly	Elderly	1st	Record Created and filled taking reference from sheet 1

[grazitti_sapna · Accepted solution]

Hi @Pramod91,

Below is my attempt to solve your problem:

 PFA Workflow.

I hope it solves your issue.

[AlanSchaeffer]

Hi,
I don't really understand why there's only 1 row with tuberculosis in the output, since there's two of them in sheet1. Is it because the subsequentID is set to 1 and in sheet2 there's only 1 row with tuberculosis ?

Thanks for the clarification,
Alan

[Pramod91]

@Alan,

Yes, as there is only one row for tuberculosis in sheet 2 and in sheet 1 it has subsequent Id 1 so it will just fill the values in other columns as that in sheet 1 and not create any new record. Had there been multiple subsequent Ids for Tuberculosis in sheet 1 it would have created new record for those subsequent Ids and fill the other columns matching against those subsequent Ids.

[Pramod91]

@grazitti_sapna,

Thank you so much. This solves my issue.