0

I'm using a excel spreadsheet at my reception desk to log in visitors in the following fashion:

[NAME] [HOUR]  
JOHN    09:00   
MARC    09:00   
DAVID   10:00   
JOHN    11:00   
MARC    12:00   
DAVID   13:00   

Obviously there are better tools for the job but no choice here. I'm asked to make a pivot table from this data which should then look like this:

[JOHN] [MARC]  [DAVID]   
09:00   09:00   10:00   
11:00   12:00
13:00

But I can only get the pivot table to make operaitons out of the values (for example sum of 09:00 + 10:00 etc) whereas I'm trying to simply enumerate them. Maybe using a pivot table isn't the right solution here?

Thanks for reading

  • I don't think you can just use a pivot table but you can use an array formula to return multiple values that match your lookup value in the list. – Antony May 11 '17 at 15:08
  • Thank you Antony. How would such formula look like? I'm stuck here: – user727123 May 11 '17 at 15:37
0

If NAMES are in Column A and TIMES are in Column B then add John Marc and David as row 1 column headings for columns E, F and G.

in E2 add the formula below as an array formula (Make sure you press CONTROL-SHIFT-ENTER in the cell to convert it to an array formula! this will then give you curly brackets {} to show its array formula)

=IF(ISERROR(INDEX($A:$B,SMALL(IF($A:$A=E$1,ROW($A:$A)),ROW(1:1)),2)),"",INDEX($A:$B,SMALL(IF($A:$A=E$1,ROW($A:$A)),ROW(1:1)),2))

drag this formula across to column G then with E2 to G2 selected drag down however many rows you need.

Make sure you change the cell format for the time or to match you example to custom format hh:mm

SMALL() identifies The nth smallest value in the array matching row. then uses this row number to return the value in that row only. As you copy the formula down, it looks to the next matching row number and returns its value, until it becomes ISERROR true where it enters a "" blank entry

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.