2

I have an Excel Sheet with keeps track of Persons, who have gone to certain Activities. Now i want to add an Activity with hundreds of Persons, of which some have gone to others already. Then I want to sort by the times they went to an activity.

To clarify:

screenshot

What is the easiest way of achieving this?

Thanks :)

3
  • The quick and dirty way would be to use VBA - as long as the Names in New Activity 3 exactly match those in the existing sheet, you can use a for loop to add an x into Column D where there is a match. There might be a better / more efficient way tho...
    – ChrisW
    Oct 29, 2012 at 15:07
  • @ChrisW Good suggestion, but why not add an answer below showing Tom how to do what you suggest?
    – CharlieRB
    Oct 29, 2012 at 15:17
  • I'm not really into VBA.. I just worked something out with VLOOKUP which grabs the information from a second table. Then I sort manually. The only problem is that I have to add the new Persons to the main list first. It's not optimal but it works ;)
    – Tom
    Oct 29, 2012 at 16:23

2 Answers 2

0

Here is a solution without VBA. Would recommend it, when this is something more rare than a monthly task.

This is how you setup your data - note, cols A-D are the "new cols".

This is the setup

Here are the formulas - set them up on row 2, then copy down:

A2=IF(ISBLANK(F2),INDEX(J:J,ROW()-COUNTIF(F:F,"*")+1),F2)

B2=IFERROR(T(VLOOKUP($A2,$F:$H,2,FALSE)),"")

C2=IFERROR(T(VLOOKUP($A2,$F:$H,3,FALSE)),"")

D2=IFERROR(T(VLOOKUP($A2,$J:$K,2,FALSE)),"")

A2 fills in all Names from your current activity-table, until it gets blank cells. Then you start filling in Names from your new activity-table, using COUNTIF to get the Number of non-empty cells and ROW and +1 to get the correct INDEX / Name.

Now you just have to make seperate lookups for your activities.

As you see, you will get duplicates, but they can be easily handled. My suggestion would be - copy the area A-D as values to another destination, then use the build-in duplicate eraser from the data tab.

Of course you could do this with VBA, however often this is a task, which is done by inexperienced users - and to create a user-safe dynamic version of this, is quite some code and needs a lot more specifics to be done. However, I want to remind you, that this is a basic database-task, and can be done a lot easier with database functions.

0

Since you mention “I want to sort by the times they went to an activity” I am assuming your Xs represent times (for which @Jook’s answer would not work). The approach below would however work for times (or Xs with minor adaptation) and uses no formula (other than what underlies icons):

Assuming New Activity 3 has values in a column immediately to the right of Name:-

  1. Copy Existing Sheet data and headers into a new sheet.
  2. Immediately below append a copy of the data from New Activity 3 into that sheet.
  3. Move New Activity 3 data other than names across two columns to the right.
  4. Drag Activity 2 label across to the right by one cell.
  5. Select entire sheet, Data > Sort & Filter – AZ.
  6. Data > Outline > Subtotal with At each change in : Name, Use function: Max, Add subtotal to: check each of Activity 1, Activity 2 and Activity 3, check Replace current subtotals and Summary below data, OK.
  7. Copy Paste Special > Values, OK.
  8. Data > Sort & Filter – Filter and for Name column Text Filters, Does Not Contain enter MAX, OK.
  9. Delete all visible rows except the one with labels.
  10. Data > Sort & Filter – Filter.
  11. Select Name column, Home > Editing > Find & Select, Replace, enter " MAX" without inverted commas for Find what:, Replace All, OK, Close.
  12. Select entire sheet, Data > Outline > Subtotal, Remove All.
  13. Delete row with Name ‘Grand’.
  14. Select activity columns, Home > Cells - Format, Format Cells, Custom, dd/mm/yyyy hh:mm;;"" or to suit, OK.
  15. Save.

This gives results sorted in name order regardless of times which could then be sorted activity by activity but not, given the layout, conveniently be sorted by activity regardless of whether 1, 2 or 3.

You must log in to answer this question.

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