Selected Answer
Hi
You can do this, without VBA, by just using a Pivot Table (a powerful tool for summarising data in Excel).
The attached version of your sheet has that done (with a small sample of data)- suggest you try that before applying to thousands of records.
Revision 1: After typing my answer, I realised it might be daunting for a "novice" but there's an easy way to use the Pivot Table I created and formatted to suit your needs...
Open up the file attached and (in Sheet 1) you'll see (from cell F10 down and right) what's called a Pivot Table (created by the steps written beyond this revision in italics). It summarises the data in your sample records in A1 to C9 in the form you need.
Now you can change the data it uses easily. Click in F10 (or anywhere in that table) and in the Excel ribbon you'll see two new tabs under PivotTable Tools. Click Analyze then Change Data Source. Under Table and Range, it will say Sheet1!$A$1:$C$9 (your sample data).
Click in that box then click Sheet 2 and select all the (test) data in Sheet 2 (that data is a bit of a copy of A1:C9 so don't expect it to look realistic!). It should then read Sheet2!$A$1:$C$97. Press enter and the Pivot table on Sheet1 will now show records for 48 employees.
To do that with your real data, indo the source change and copy Sheet1 to the workbook where your thousands of records are. Go to it and change the pivot data source as above (having made sure the column headings for the sheet with thousands are the same as in temp.xlsx). That's it!
Back to the original answer (which I suggest you follow to see how it's done)...
There are a few steps to creating that pivot table but just follow them carefully please on your original temp.xlsx file (it's worth if for other stuff!):
- Select the data (A1:A9 in your temp.xlsx file)
- On the Insert ribbon, click Pivot Table then under "Choose where..." click Existing Worksheet (so you can see the table alongside your data) and pick in Location the upper left cell for the table (I chose F10 of Sheet 1) then OK. Note: With much more data, you'll need to pick another sheet
- You'll see a box appear the Location and, to the right of your screen a new Section called "PivotTable fields" (this will reappear when you click in the table later).
- At the top of that area, you'll see your headings, Employee ID, Status and Date. To create your pivot table:
- Drag Employee ID into the area in the bottom half of the screen called "Rows"
- Drag Status into "Columns"
- Drag Date into "Values"
- You'll see a table but just with blanks or 1s in it (the number of dates it found per employee of status). To change that (assuming there's only 1 per combination), right click (or down arrow) on Date in "Values", pick Value Field Settings and
- Under Summarize... pick Max
- Click Show Values As tab as then under Base Field, click Number Format then Custom and Under Type, type dd-mmm-yyyy (so dates will appear like 10-Nov-2020)
- Click okay
- Now you'll see dates in the pivot table.
It's a matter of tidying up the pivot table now, so please do this
- To remove the row and column totals (meaningless in your case), right click somewhere in the table and pick Pivot Table Options... then in the Tables and Filters tab, untick Show grand totals for rows (and same for columns, below that) then click OK
- To reorder the columns, click the down arrow on "Column Labels" then clich Sort Z to A (so they go Unassigned, Block, Assigned as you want).
- Rename the pivot table heading (currently) Max of Date, Column Labels, Row Labels) by clicking on them then typig something in the formula bar (where you'd add values to a cell) but note that they can't be the same as Field Names (so you could change Max of Date to "Status Date" but not to Date, which exists).
- When doing this for real, you might want to change your original field names (e.g. to Date1) before creating the pivot table so in the Pivot they cange be meaningful (so Date would be allowed)
It seems a lot but Pivot Tables are great for loads of data.
Hope this helps