Hi, I would really appreciate some help with something I am asked to do at work. I have been asked to create another table which will show the next 20 visits from todays date. I am not too savvy with excel and have looked all over the internet. I have tried ifs and ranks with countifs but I keep getting duplicated results. I require them to be unique because the different managers will have visits on the same day and these need to be seen separately. I was wondering if there is a formula that will produce such a table. Any help will be appreciated.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Visit Tracker
- Thread starter Krust710
- Start date
Rijnsent
Well-known Member
- Joined
- Oct 17, 2005
- Messages
- 1,386
- Office Version
- 365
- Platform
- Windows
Hi Krust,
how would you see what the next 20 visits from today are? Is that the "Visit booked" column? Please tell a bit more about your data and the desired output, is that mainly the table you posted, but than sorted by date and filtered for the first 20 visits from today on?
Thanks,
Koen
how would you see what the next 20 visits from today are? Is that the "Visit booked" column? Please tell a bit more about your data and the desired output, is that mainly the table you posted, but than sorted by date and filtered for the first 20 visits from today on?
Thanks,
Koen
Upvote
0
Hi Koen,
Thank you for getting back to me.
The Team leader wants an overview page which will show various information from this sheet. As there are alot of sponsors an overview page is beneficial for the team leader. I have managed to do that no problem. This sheet, is the second sheet is what the account managers fill in. The frequency of visits, last visit etc and it is from the "visit booked" column that I need to extract the information from. They would like a table that shows the name of account manager, name of sponsor and then the date the book is visited. This is so they can see the amount of people that are going to be away from the office at a snap shot. Now I can use lookup etc to get the account manager and sponsor. However, this messes up when more than one sponsor has the same visit date. I dont know how do get unique results when there are duplicated dates especially seen as there are around 200 sponsors. I have attached an example of what they would like on the overview sheet. It looks so simple but has been so complicated. Any help at all would be appreciated.
Thank you
Krust
Thank you for getting back to me.
The Team leader wants an overview page which will show various information from this sheet. As there are alot of sponsors an overview page is beneficial for the team leader. I have managed to do that no problem. This sheet, is the second sheet is what the account managers fill in. The frequency of visits, last visit etc and it is from the "visit booked" column that I need to extract the information from. They would like a table that shows the name of account manager, name of sponsor and then the date the book is visited. This is so they can see the amount of people that are going to be away from the office at a snap shot. Now I can use lookup etc to get the account manager and sponsor. However, this messes up when more than one sponsor has the same visit date. I dont know how do get unique results when there are duplicated dates especially seen as there are around 200 sponsors. I have attached an example of what they would like on the overview sheet. It looks so simple but has been so complicated. Any help at all would be appreciated.
Thank you
Krust
Attachments
Upvote
0
Rijnsent
Well-known Member
- Joined
- Oct 17, 2005
- Messages
- 1,386
- Office Version
- 365
- Platform
- Windows
HiKrust,
I think I understand what you mean. I found a tutorial that will come in handy: https://www.get-digital-help.com/sorting-text-cells-using-array-formula/ -> they have an example with 2 columns, but with the same logic you could add a third. Note: your date column is probably the one you want to sort by.
Cheers,
Koen
I think I understand what you mean. I found a tutorial that will come in handy: https://www.get-digital-help.com/sorting-text-cells-using-array-formula/ -> they have an example with 2 columns, but with the same logic you could add a third. Note: your date column is probably the one you want to sort by.
Cheers,
Koen
Upvote
0
