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 >>

 

[steve the fish]

If you dont have TEXTJOIN then this will be difficult. TEXTJOIN makes it easy:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$6=A13,IF($B$2:$B$6=B13,IF($C$2:$C$6>=$D$8,IF($C$2:$C$6<=$D$10,$E$2:$E$6,""),""),""),""))

 

[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 >>

or any single line to each cell that does the same work with if and concatenate functions ?

 

[bobbyexcel]

Thanks .. let me try and let you know

If you dont have TEXTJOIN then this will be difficult. TEXTJOIN makes it easy:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$6=A13,IF($B$2:$B$6=B13,IF($C$2:$C$6>=$D$8,IF($C$2:$C$6<=$D$10,$E$2:$E$6,""),""),""),""))

 

[bobbyexcel]

I'm getting the error as "N/A" after modifying the formula.. Any suggestions ??

=TEXTJOIN(", ",TRUE,IF($A$5:$A$38=Summary!A:A,IF($B$5:$B$38=Summary!C:C,IF(Summary!B:B>=$D$2,IF(Summary!B:B<=$D$3,Summary!N:N,""),""),""),""))

 

[steve the fish]

This is wrong:

Summary!A:A

Change it to single cell. Same as the rest of the occurrences in the formula. If you need it in several cells drag it down.

 

[bobbyexcel]

This is wrong:

Summary!A:A

Change it to single cell. Same as the rest of the occurrences in the formula. If you need it in several cells drag it down.

Changed as below but didn't work.. Data is in multiple cells so cannot compare with one cell.. Any suggestions ??

And my data to concatenate is in N Column.. Is my command correct ?


=TEXTJOIN(", ",TRUE,IF($A$5:$A$38=Summary!$A:$A,IF($B$5:$B$38=Summary!$C:$C,IF(Summary!$B:$B>=$D$2,IF(Summary!$B:$B<=$D$3,Summary!$N:$N,""),""),""),""))

 

[steve the fish]

You are comparing a list of names with a single cell with a name in it. That has to be a single cell. From what i gave that would be this bit:

$A$2:$A$6=A13

A2:A6 contains the list of names and A13 the name you want to test. In your example 'abc'

That make more sense?

 

[bobbyexcel]

You are comparing a list of names with a single cell with a name in it. That has to be a single cell. From what i gave that would be this bit:

$A$2:$A$6=A13

A2:A6 contains the list of names and A13 the name you want to test. In your example 'abc'

That make more sense?

My Bad.. Let me rewrite my question again..

I have 2 sheets as mentioned below.. Now compare Column A & B from Sheet 1 with Column A & B from Sheet 2 and concatenate the Cell if matches but between the date range.. (concatenate all the text between the date range with a delimiter )

Sheet 1:

Column A	Column B	Column C	Column D	Column N
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

Sheet 2:

Column A	Column B	Column C
	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 >>

 

[bobbyexcel]

I've changed the formula like this and I achieved 80%.. My output is coming as follows

=TEXTJOIN(", ",TRUE,IF($A$8=Summary!$A:$A,IF($B$8=Summary!$C:$C,IF(Summary!$B:$B>=E$2,IF(Summary!$B:$B<=E$3,Summary!$N:$N,""),""),""),""))

Output:

test, 0, 0, 0, 0, 0, 0

why those zero's ??? observed that if there is no data then it is giving 7 zeros ?? (may be for all 7 days but why )?