Compare 2 cells and concatenate the text which exists between date range

[bobbyexcel]

I need help on my vba script

I want to compare 2 columns in 2 sheets (compare A,B column with C,D column in other sheet) and merge the data from E,F columns if it matches with in the date range in above cell of each column.

Name	city	Date	Code	comments
abc	London	01-Jan-2021	L	the capital of England​
xyz	US	01-Jan-2021	US	US is a country of 50 States​
jkl	UK	01-Jan-2021	UK	The United Kingdom, made up of England, Scotland, Wales and Northern Ireland​
xyz	US	02-Jan-2021	US	Hollywood is famed for filmmaking
xyz	India	02-Jan-2021	Ind	Is a country of South Asia

	WeekStart	​	Sun, 27-Dec-2020
	WeekEnd		Sat, 02-Jan-2021
			Week - 1
Name	city
abc	London		<< concatenate data if cells matches between the date range >>
xyz	US		<< concatenate data if cells matches between the date range >>

 

[Peter_SSs]

What version of Excel are you using? I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[bobbyexcel]

What version of Excel are you using? I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

Office 365is the version and yes I have updated.. Any update on my query or do I need to post it again ?

 

[Peter_SSs]

Thanks for updating your profile. I would try to avoid using whole column references - surely you are not using 1,000,000+ rows?

See if something like this could be adapted for you.

bobbyexcel.xlsm
	A	B	C	D	E
1	Column A	Column B	Column C	Column D	Column N
2	Name	city	Date	Code	comments
3	abc	London	1-Jan-21	L	the capital of England
4	xyz	US	1-Jan-21	US	US is a country of 50 States
5	jkl	UK	1-Jan-21	UK	The United Kingdom, made up of England, Scotland, Wales and Northern Ireland
6	xyz	US	2-Jan-21	US	Hollywood is famed for filmmaking
7	xyz	India	2-Jan-21	Ind	Is a country of South Asia
Summary

bobbyexcel.xlsm
	A	B	C
1	Column A	Column B	Column C
2		WeekStart	27-Dec-20
3		WeekEnd	2-Jan-21
4			Week - 1
5	Name	city
6	abc	London	the capital of England
7	xyz	US	US is a country of 50 States, Hollywood is famed for filmmaking
Sheet2

Cell Formulas
Range	Formula
C6:C7	C6	=TEXTJOIN(", ",1,FILTER(Summary!E$3:E$100,(Summary!A$3:A$100=A6)*(Summary!B$3:B$100=B6)*(Summary!C$3:C$100>=C$2)*(Summary!C$3:C$100<=C$3),""))

 

[bobbyexcel]

Thanks for updating your profile. I would try to avoid using whole column references - surely you are not using 1,000,000+ rows?

See if something like this could be adapted for you.

bobbyexcel.xlsm
	A	B	C	D	E
1	Column A	Column B	Column C	Column D	Column N
2	Name	city	Date	Code	comments
3	abc	London	1-Jan-21	L	the capital of England
4	xyz	US	1-Jan-21	US	US is a country of 50 States
5	jkl	UK	1-Jan-21	UK	The United Kingdom, made up of England, Scotland, Wales and Northern Ireland
6	xyz	US	2-Jan-21	US	Hollywood is famed for filmmaking
7	xyz	India	2-Jan-21	Ind	Is a country of South Asia
Summary

bobbyexcel.xlsm
	A	B	C
1	Column A	Column B	Column C
2		WeekStart	27-Dec-20
3		WeekEnd	2-Jan-21
4			Week - 1
5	Name	city
6	abc	London	the capital of England
7	xyz	US	US is a country of 50 States, Hollywood is famed for filmmaking
Sheet2

Cell Formulas
Range	Formula
C6:C7	C6	=TEXTJOIN(", ",1,FILTER(Summary!E$3:E$100,(Summary!A$3:A$100=A6)*(Summary!B$3:B$100=B6)*(Summary!C$3:C$100>=C$2)*(Summary!C$3:C$100<=C$3),""))

Thanks for your reply and this worked perfectly.. Thanks again for your help.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.