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

bobbyexcel

Board Regular
Joined
Nov 21, 2019
Messages
88
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 >>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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,""),""),""),""))
 
Upvote 0
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 ?
 
Upvote 0
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,""),""),""),""))
 
Upvote 0
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,""),""),""),""))
 
Upvote 0
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.
 
Upvote 0
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,""),""),""),""))
 
Upvote 0
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?
 
Upvote 0
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 >>
 
Upvote 0
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 )?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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