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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

bobbyexcel

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Column AColumn BColumn CColumn DColumn N
2NamecityDateCodecomments
3abcLondon1-Jan-21Lthe capital of England
4xyzUS1-Jan-21USUS is a country of 50 States
5jklUK1-Jan-21UKThe United Kingdom, made up of England, Scotland, Wales and Northern Ireland
6xyzUS2-Jan-21USHollywood is famed for filmmaking
7xyzIndia2-Jan-21IndIs a country of South Asia
Summary


bobbyexcel.xlsm
ABC
1Column AColumn BColumn C
2WeekStart27-Dec-20
3WeekEnd2-Jan-21
4Week - 1
5Namecity
6abcLondonthe capital of England
7xyzUSUS is a country of 50 States, Hollywood is famed for filmmaking
Sheet2
Cell Formulas
RangeFormula
C6:C7C6=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

New Member
Joined
Nov 21, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Column AColumn BColumn CColumn DColumn N
2NamecityDateCodecomments
3abcLondon1-Jan-21Lthe capital of England
4xyzUS1-Jan-21USUS is a country of 50 States
5jklUK1-Jan-21UKThe United Kingdom, made up of England, Scotland, Wales and Northern Ireland
6xyzUS2-Jan-21USHollywood is famed for filmmaking
7xyzIndia2-Jan-21IndIs a country of South Asia
Summary


bobbyexcel.xlsm
ABC
1Column AColumn BColumn C
2WeekStart27-Dec-20
3WeekEnd2-Jan-21
4Week - 1
5Namecity
6abcLondonthe capital of England
7xyzUSUS is a country of 50 States, Hollywood is famed for filmmaking
Sheet2
Cell Formulas
RangeFormula
C6:C7C6=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,914
Messages
5,627,608
Members
416,256
Latest member
Tammy Beaudoin

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