Forum Discussion
Dates of visits from table based on the crtieria
Hello, I have 100 villages on a table, and there is a target of visiting various villages in a month. I have used COUNTIF formula to put on the number of times a village is visited in one month. How to find out dates of each visit for one village. Like Village A is visited twice in May, 5th and 10th of the month. Countif brings 2 for Village A from May, but what formula to use to pull out dates of 5 and 10 may.
Multiple dates of visit can be returned in a single cell using the TEXTJOIN function. This formula is available in recent versions of Excel. In the screenshot is TEXTVERKETTEN which is the name of TEXTJOIN in german Excel. The formula is translated when you open the attached sample file.
See my attached suggestion for 4 villages. The size of the database and the formula can be changed according to the requirement. The formula that returns the dates of the visits is in cell G2 and dragged across range G2:J5. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
If you work with a recent version of Excel you can use the FILTER function.
OliverScheurich
Hi, thanks for the reply.
I cannot get what u did in the attachment.
I have prepared some sample data.
Kindly review it and see if you can help.
sorry, it did not let me attach xlsx fileMultiple dates of visit can be returned in a single cell using the TEXTJOIN function. This formula is available in recent versions of Excel. In the screenshot is TEXTVERKETTEN which is the name of TEXTJOIN in german Excel. The formula is translated when you open the attached sample file.
