Create a new table to the right of your existing table, put the users on the left, then create a column for each possible action. This is the only reliable way to record who did which actions.
Next create a column to the left of your existing table containing this formula:
=UserColumnCell&ActionColumnCell
i.e. =$C2&$B2
This will give you a value you can use to create a VLOOKUP in your new table, such as User1Lore ipsum 1
.
Go back to your new table and in the first cell of the left most Action column add this formula:
=IF(ISERROR(VLOOKUP(UserColumnCell&ActionColumnTitle),FirstTableRange,2,FALSE)),0,1)
i.e. =IF(ISERROR(VLOOKUP(E2&F1,$A$2:$C$1000,2,FALSE),0,1)
Because we didn't fix the cell references in the first half of the formula you can now drag the formula accross for each Action and down for each user and it will adjust accordingly.
Finally, copy all the data we just created, and paste it back over itself, srtting the paste type as Values. You can know destroy your original table.
Obviously if you want a different flag for the action being done then change the 1 and the 0. For instance, if you want a little tick you could leave it as is and use the conditional formatting for 'cells based on their values' set to the Tick icon set, or you could simply set the font of each of the Action columns to Marelett and replace 0,1
with "","a"
as a
is a tick in Marlett.
If you're using tables you can simplify this by using table references. Where your existing table is Table1 and the new one is Table2:
The formula in Column1 of Table1 would be:
=[@User]&[@Action]
And then the formula in each of the action columns in Table2 would be:
=IF(ISERROR(VLOOKUP([@User]&Table2[[#Headers],[lore ipsum 1]],Table1,2,FALSE)),0,1)
Although if you do this you will have to reset the [@User] reference once you drag the formula across as it will have drifted.
So what is this doing?
just in case you're not experiecned with VLOOKUP: The IF statement drives the ability to dictate what we output, rather than just returning the value the VLOOKUP finds.
The ISERROR will return a true or false depending on whether or not the VLOOKUP finds anything
The VLOOKUP looks for that concatenated formula we built, to see if it can find a result within that column that matches the combination of user and action relevent to that column and row combo in Table2, if it finds it it returns it, which makes the ISERROR return as false, and if it finds nothing then it errors, making the ISERROR return as true.
The output of the ISERROR is either TRUE or FALSE, if it's TRUE then there is no match so we leave a 0 or a blank if you prefer to show that that user did not complete that action. If it returns a FALSE then the user did complete that action so it returns a 1 or an a
for a tick or whatever you'd prefer.
EDIT
This answer is based on you wanting to redesign your table. If you want to keep your table as is than I would point you to Math's comment about Pivot Tables as they would be the best way to do this if you needed to keep your original table structure but present the data a different way.
user
then byaction
and then remove duplicates fromuser
?Pivot Tables
?Pivot Tables
.. shoud I? I'm on the 2013 version on Excel