I want to add 2nd sheet data for each row of 1st sheet data, cross join

[Nidhi_Gupta1 (Nidhi G)]

Hi
I want to add 2nd sheet data for each row of 1st sheet data, cross join.
I tried using join option but it is not full join

no of rows and col in both sheet will be dynamic
sample ip n op attached, color used for understanding
pls help, trying using for each but getting it hardcoded i want dynamic approach
Data.xlsx (12.3 KB)

[Nidhi_Gupta1 (Nidhi G)]

Sheet1

IN1_Name	IN1_Age	IN1_Mob	IN1_City	IN1_ID	IN1_DES
qwe rty	23	9876543	Hyd	2	sr
asd fgh	45	876543	Che	3	jr
zxc vbn	43	765432	Del	44	mgr

Sheet2

IN2_Name	IN2_Age	IN2_Mob	IN2_City	IN2_dept	IN2_sal	IN2_po	IN2_remark
ZXCV BNM	11	1234567	Ahm	acc	45	5	good
asdf hjk	12	2345678	Bang	sales	67	6	average
qwer poi	13	3456789	kol	hr	87	9	better

Output

IN1_Name	IN1_Age	IN1_Mob	IN1_City	IN1_ID	IN1_DES	IN2_Name	IN2_Age	IN2_Mob	IN2_City	IN2_dept	IN2_sal	IN2_po	IN2_remark
qwe rty	23	9876543	Hyd	2	sr	ZXCV BNM	11	1234567	Ahm	acc	45	5	good
qwe rty	23	9876543	Hyd	2	sr	asdf hjk	12	2345678	Bang	sales	67	6	average
qwe rty	23	9876543	Hyd	2	sr	qwer poi	13	3456789	kol	hr	87	9	better
asd fgh	45	876543	Che	3	jr	ZXCV BNM	11	1234567	Ahm	acc	45	5	good
asd fgh	45	876543	Che	3	jr	asdf hjk	12	2345678	Bang	sales	67	6	average
asd fgh	45	876543	Che	3	jr	qwer poi	13	3456789	kol	hr	87	9	better
zxc vbn	43	765432	Del	44	mgr	ZXCV BNM	11	1234567	Ahm	acc	45	5	good
zxc vbn	43	765432	Del	44	mgr	asdf hjk	12	2345678	Bang	sales	67	6	average
zxc vbn	43	765432	Del	44	mgr	qwer poi	13	3456789	kol	hr	87	9	better

[maheshwari.sonawane (Maheshwari S)]

Hi @Nidhi_Gupta1

Step1 ~ read range, both sheets
You will get 2 data tables dtSheet1 and dtSheet2

Step2~build data table (this is where u will get output , add all columns from sheet1 and then sheet2

Step3~use assign activity

dtResult = (From row1 In dtSheet1.AsEnumerable
From row2 In dtSheet2.AsEnumerable
Select dtResult.LoadDataRow(New Object() {
row1(“IN1_Name”), row1(“IN1_Age”), row1(“”),
row2(“IN2_Name”), row2(“IN2-Age”), row2(“”)
}, False)).CopyToDataTable

Please make sure to add all columns above I wrote 2 for each sheet for reference

Step4~ write range dtresult

Let me know if it works .

Thank you !

[Nidhi_Gupta1 (Nidhi G)]

hey, it worked as expected. thanks !