How to format a table to show single dates with multiple values?

[Moo1987]

Hi,

Wondering if someone could help me?

I have a table with dates in column A and values in column B

There are 48 dates for every 1 day over 365 days (E.G 01/04/2016 48 times, then 02/04/2016 48 times and so on over 365 days).

Each date (row) has it's own seperate value next to it.

I need change the table, so there is only one date for every day in Column A, but I need all 48 values for that day in the same row.

E.G

Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48
01/04/2016	8	9	7	5	5	5	3	3	1	12	2	3	2	2	25	5	8	8	8	7	8	6	2	3	1	5	8	6	2	1	3	5	84	2	3	5	5	5	8	8	87	4	1	1	2	35	5	4
02/04/2016	3	6	1	2	9	8	5	4	5	2	3	6	5	4	9	5	1	5	5	6	98	8	5	2	6	5	2	2	5	6	1	5	6	8	5	2	1	5	6	2	3	5	6	8	4	6	2	3
03/04/2016	1	2	5	6	9	5	2	3	6	4	5	3	5	6	3	2	5	4	2	8	6	3	4	3	2	5	6	9	8	5	2	1	5	6	5	2	9	3	1	4	5	6	9	3	2	1	5	6

<tbody>
</tbody>

I am sure there is a simple solution, but it has been a very long day.

Thanks for your help in advance.

 

[Special-K99]

"I need change the table, so there is only one date for every day in Column A, but I need all 48 values for that day in the same row."

So what would the result look like for 01/04/2016 ?
All 48 dates strung together in one long continuous number?
Or did you have something else in mind?

What's the highest number a value can have in one of those 48 ?

 

[Moo1987]

Hi Special-K99

The result for 01/04/2016,02/042016 and 03/04/2016 would look like the table below.
Comlun A B C AW

Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48
01/04/2016	8	9	7	5	5	5	3	3	1	12	2	3	2	2	25	5	8	8	8	7	8	6	2	3	1	5	8	6	2	1	3	5	84	2	3	5	5	5	8	8	87	4	1	1	2	35	5	4
02/04/2016	3	6	1	2	9	8	5	4	5	2	3	6	5	4	9	5	1	5	5	6	98	8	5	2	6	5	2	2	5	6	1	5	6	8	5	2	1	5	6	2	3	5	6	8	4	6	2	3
03/04/2016	1	2	5	6	9	5	2	3	6	4	5	3	5	6	3	2	5	4	2	8	6	3	4	3	2	5	6	9	8	5	2	1	5	6	5	2	9	3	1	4	5	6	9	3	2	1	5	6

<tbody>
</tbody>

There are 48 columns with the vaules in, but only one date.

I hope this makes sense?

 

[NoSparks]

Is this of any help ?

Sub Testing()

    Dim ray, lr As Long, i As Long
    
Application.ScreenUpdating = False
With Sheet1
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lr Step 48
        ray = .Range("B" & i).Resize(48).Value
        .Cells(i, 2).Resize(, 48).Value = Application.Transpose(ray)
    Next i
    
    Range("C1:C" & lr).SpecialCells(xlBlanks).EntireRow.Delete
    
End With
Application.ScreenUpdating = True
End Sub

 

[Moo1987]

Thanks No Sparks,

I used this solution instead =INDEX($B$2:$B$17659,ROWS(C$2:C2)*48-48+COLUMNS($C2:C2)) then copied across and down, but thank you for your time.