0

I have an Excel file with several rows (about 1k). Each row describe some action of one user and (my bad) for differents actions there are differents rows. Something like this

      action              user
      ===============     =====
r1    lore ipsum 1        user1
r2    lore ipsum 2        user1
r3    lore ipsum 3        user2
r4    lore ipsum 1        user2
r5    lore ipsum 2        user3
r6    lore ipsum 4        user1
r7    lore ipsum 5        user1

As you can see, I have multiple actions for user1. All I need is to put on a single row all the actions of user1 and on the other rows the other actions for the other users (in the same way as for user1) witohut duplicates on the "user" column. Something like this:

      action              user
      ===============     =====
r1    lore ipsum 1        user1
      lore ipsum 2        
      lore ipsum 4        
      lore ipsum 5        
r2    lore ipsum 3        user2
      lore ipsum 1        
r3    lore ipsum 2        user3

How can i do that? Thanks a lot

7
  • Can't you just sort by user then by action and then remove duplicates from user? Jul 19, 2013 at 12:38
  • Yes I could but this do not remove the multiple rows for each user... I would have al the actions of the user in one row!
    – Lc0rE
    Jul 19, 2013 at 12:45
  • Do you know how to use Pivot Tables?
    – Math
    Jul 19, 2013 at 13:25
  • It would be great to know the Excel version you have.
    – Math
    Jul 19, 2013 at 13:32
  • @Math I don't know Pivot Tables.. shoud I? I'm on the 2013 version on Excel
    – Lc0rE
    Jul 19, 2013 at 13:35

2 Answers 2

0

Here's a sample of how your data might be stored:

data

Select one of the cells of the table, just like the picture above. Then go to menu Insert > Pivot Tables. It will select all your data and prompt a Dialog like this:

new pivot table

I'm sorry! My Excel is in Portuguese, but I believe you can follow the pictures.

Click Ok. It will create a new Sheet, like this:

new sheet is now created

And a menu will be shown in the right:

pivot table fields

Drag and drop user, the drag and drop action, to the Row Label, in the left-bottom box.

Row Label

The result will look like:

Final result

See if this is satisfactory for you, or if you want something else. We can work on something at this point, but maybe this is what will need, I don't know.

Sorry the delay to answer, I'm in job time ;)

1
  • It's it! The solution I was loocking for! Thanks so much mate!
    – Lc0rE
    Jul 20, 2013 at 6:25
0

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .