• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get the most frequently Occurring Words between two dates

R

Ramesh Deo

Member
Hi, I want to get the most frequently Occurring Word between two dates in a cell as below

example:In column A1

Luke
SM
Smallman
Chandoo
Chandoo
Chandoo
Smallman
Marcus
Luke
Chandoo
NK
Smallman
Hui
Boh
Smallman
Marcus

And assume Dates are in column B1

Result is in C1=Smallman & Chandoo
 
Hi SM Sir,

Thanks for responding with sample file. But in your sample file I want answer A and D in same cell.
like result with A & D. and suppose if count of one more word is same with other max ,it will also include with "&" like A & D & F.and if possible date range using only from column B not using D2 and E2.
 
@Ramesh Deo

Concatenation of result i.e. name in a single cell through formula will not be a good point here as the formula is huge. Second you don't know how many names will be there, suppose in a real life there are 10 names, than what? In those situation VBA will have to overtake formula solutions.

Second Dates in D2 & E2 are ref. dates for start & end, actual dates which are looked are in column B only. To explain this point better upload a sample file, as your title says "between two dates" , so you have to give the ref. of thos two dates, or there can be a situation say in B2 you have start date and say in B20 last cell of your data holds end date?

So explain.

Regards,
 
Ok, SM Sir,I have to find out the same in week-wise data set, suppose in week 41 start date is 10/6/2014 and end date 10/12/2014 and in between entries are max to max 50 with repeated Names. so i hope 2 or 3 names will be same max count.so plz now do the needful.
 
Just put names and dates in my sample file in column A & B and put week start date in D2 and week end date in E2.

Check the results than.

regards,
 
@Ramesh Deo

Before starting working on your file, I would like to make a comment on your column Week on Dashboard sheet, look wise it looks good, but the format will make all the formulas complex and slow your worksheet. And when you will add more data with unique name array formula your sheet will become slow.

So, I would suggest you to break this column into 3 - Week No. - Week Start Date - Week End date.

With this your all formulas will become easier to read and will not put load on calculation.

Regards,
 
Somendra Misra said:
@Ramesh Deo

Before starting working on your file, I would like to make a comment on your column Week on Dashboard sheet, look wise it looks good, but the format will make all the formulas complex and slow your worksheet. And when you will add more data with unique name array formula your sheet will become slow.

So, I would suggest you to break this column into 3 - Week No. - Week Start Date - Week End date.

With this your all formulas will become easier to read and will not put load on calculation.

Regards,
Click to expand...


Thanks for the suggestion,Sm Sir!
please work on this file from your side to make it better.

Regards

Deo
 
Hello Ramesh,

If you modify your sheet as SM suggested, Col B for StartDate and COl C for EndDate, here is one way.

So now B3 has StartDate & C3 has EndDate, in Q2, with CTRL+SHIFT+ENTER

=INDEX(Data!B$2:B$271,MODE(IF(Data!A$2:A$271>=B3,IF(Data!A$2:A$271<=C3,IF(Data!B$2:B$271<>"",MATCH(Data!B$2:B$271,Data!B$2:B$271,0))))))

then copy down.

Note: If none of the names are repeating in a week, will give an error

You can convert to data to Table structure or use dynamic range in the formula.
 
@Ramesh Deo

See the file. Modifications done are listed below:
1. Converted your data in to Excel table so that it can adjust the calculation on Dashboard when data expands.
2. Include Week Number column B in your data.
3. Separated Week #, Start & End Date on Dashboard.
4. Changed all formulas on Dashboard as per new alignment of data.
5. Column O & P on dashboard sheet don't have any formulas so I have left them at it is.
6. Client Name are listed on dashboard but they are now only 1, if there are multiple, I will suggest you to extent them towards right.

Just check all the calculations.

Regards,
 

Attachments

Have a very good morning, SM Sir !

Thank you so much for this help with great improvement and yours precious suggestion. really I appreciate to being a part of this forum and to have nice n great excel ninjas.

and SM sir ,one thing i like to know that is it possible to concatenate the word if max count of word is same. Please let me know?
 
Somendra Misra said:
@Ramesh Deo

What can be max no. of names that can be repeated?

Regards,[/quote

like In your previous uploaded sample file max count of A and D was 4 in a date range . ok then my question was that if max count two words are same then result should be like A & D in same cell.is this possible. i want to apply the same thing in column Q in my dashboard sheet.
Click to expand...
 
You must log in or register to reply here.
Back
Top