I've been struggling with this for a while, using groups, pivot tables etc. My data looks like this:
Date Time Reading
01/01/2015 1:18:20 13.4
01/01/2015 6:04:59 8.7
01/01/2015 21:16:57 21.1
01/02/2015 0:12:53 20.2
01/02/2015 6:52:06 16.1
01/02/2015 11:03:25 20.8
01/02/2015 15:47:29 15.3
01/02/2015 16:36:51 12.0
01/02/2015 20:11:22 9.1
01/03/2015 0:06:23 13.4
01/03/2015 6:31:24 19.3
and I would like it to look like this:
Date Time Reading Time Reading Time etc.
01/01/2015 1:18:20 13.4 6:04:59 8.7 21:16:57
01/02/2015 0:12:53 20.2 6:52:06 16.1 11:03:25
01/03/2015 0:06:23 13.4 6:31:24 19.3
etc.
Please can you help me.....this is for my doctor?
Just to get you started, perhaps try the following:
1) Assuming the data is in columns A, B & C to start with, insert a column in front of A, and add the following formula in A2: =B2&" "&COUNTIF($B$1:$B2,B2)
(This basically creates an index that we can use for a VLookUp)
2) In Range F2 down, put in the dates of which measurements were taken
3) In G2 put in the following formula: =IFERROR(VLOOKUP($F2&" "&G$1,$A:$D,3,0),"")
(This is looking up the first time for the given date)
4) In H2 put in the following formula: =IFERROR(VLOOKUP($F2&" "&H$1,$A:$D,4,0),"")
(This is looking up the first reading for the given date)
5) Copy these formulas down for as many days as there are readings listed.
6) Copy columns G & H to the right for as many columns as you like, but iterating the titles by 1 each time.
Please see below for example:
Index Date Time Reading Date 1 1 2 2 3 3 4 4
42005 1 1/1/2015 1:18:20 13.4 1/1/2015 1:18:20 13.4 6:04:59 8.7 21:16:57 21.1
42005 2 1/1/2015 6:04:59 8.7 1/2/2015 0:12:53 20.2 6:52:06 16.1 11:03:25 20.8 15:47:29 15.3
42005 3 1/1/2015 21:16:57 21.1 1/3/2015 0:06:23 13.4 6:31:24 19.3
42006 1 1/2/2015 0:12:53 20.2
42006 2 1/2/2015 6:52:06 16.1
42006 3 1/2/2015 11:03:25 20.8
42006 4 1/2/2015 15:47:29 15.3
42006 5 1/2/2015 16:36:51 12
42006 6 1/2/2015 20:11:22 9.1
42007 1 1/3/2015 0:06:23 13.4
42007 2 1/3/2015 6:31:24 19.3