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

bobbyexcel

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.

NamecityDateCodecomments
abcLondon01-Jan-2021L
the capital of England​
xyzUS01-Jan-2021US
US is a country of 50 States​
jklUK01-Jan-2021UK
The United Kingdom, made up of England, Scotland, Wales and Northern Ireland​
xyzUS02-Jan-2021USHollywood is famed for filmmaking
xyzIndia02-Jan-2021IndIs a country of South Asia

WeekStart
Sun, 27-Dec-2020
WeekEndSat, 02-Jan-2021
Week - 1
Namecity
abcLondon<< concatenate data if cells matches between the date range >>
xyzUS<< concatenate data if cells matches between the date range >>
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.

NamecityDateCodecomments
abcLondon01-Jan-2021L
the capital of England​
xyzUS01-Jan-2021US
US is a country of 50 States​
jklUK01-Jan-2021UK
The United Kingdom, made up of England, Scotland, Wales and Northern Ireland​
xyzUS02-Jan-2021USHollywood is famed for filmmaking
xyzIndia02-Jan-2021IndIs a country of South Asia

WeekStart
Sun, 27-Dec-2020
WeekEndSat, 02-Jan-2021
Week - 1
Namecity
abcLondon<< concatenate data if cells matches between the date range >>
xyzUS<< 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

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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 AColumn BColumn CColumn DColumn N
NamecityDateCodecomments
abcLondon01-Jan-2021Lthe capital of England
xyzUS01-Jan-2021USUS is a country of 50 States
jklUK01-Jan-2021UKThe United Kingdom, made up of England, Scotland, Wales and Northern Ireland
xyzUS02-Jan-2021USHollywood is famed for filmmaking
xyzIndia02-Jan-2021IndIs a country of South Asia

Sheet 2:
Column AColumn BColumn C
WeekStartSun, 27-Dec-2020
WeekEndSat, 02-Jan-2021
Week - 1
Namecity
abcLondon<< concatenate data if cells matches between the date range >>
xyzUS<< concatenate data if cells matches between the date range >>
 

bobbyexcel

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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 )?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,621
Messages
5,625,927
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top