Forum Discussion
Compare data across different sheets, return value where match
Sheet 1 has duplicative data noted here:
Sheet 2 is trying to indicate if a check occurred in each month. I of course placed the 'X' in manually but have 2771 rows to view. I've googled and don't know how to describe the level of complexity to get a good result.
This is what I would like to do in plain text:
* compare sheet 1, row A and sheet 2 row A (recognize the match)
* Where matches exist, indicate if check occurred within each month (sheet 1 column B and sheet 2 row 1) (OR if easier)
* indicate month of most recent check date (for ID 9876 would indicate June 2021
I don't know if this is possible or if there are other simple recommendations I'm not thinking of?
Thank you!
b-coronado I'd suggest you use a pivot table for this purpose. Demonstrated in the attached file. It use some of Excel's built-in time intelligence and counts the number of ID per date, grouped by month. No problem to do this on 2771 rows of data. It might take two seconds to generate the pivot table.
2 Replies
Hello, attached is an excel file with an example. I did this using power query...
You just need to input your information in the blue table and then go to Data > Refresh all. (like a pivot table)
Hope it works!
b-coronado I'd suggest you use a pivot table for this purpose. Demonstrated in the attached file. It use some of Excel's built-in time intelligence and counts the number of ID per date, grouped by month. No problem to do this on 2771 rows of data. It might take two seconds to generate the pivot table.
