Data Match with existing axes

[Luke777]

Hi all,

I asked a similar question a week or two ago, but I was a little vague with exactly what I was looking for so the answers given (whilst great) often came with more questions from myself.

To skip to the important part I have a data source that looks like the following. All sheets are contained within one workbook. The below is taken from Worksheets("Source").

286	06:30:00	46
214	06:30:00	46
1535	06:30:00	46
1580	06:30:00	46
1307	06:30:00	46
1029	06:30:00	53
57	06:30:00	23
286	06:45:00	12
214	06:45:00	12
1535	06:45:00	12
1580	06:45:00	12
1307	06:45:00	12
932	07:30:00	46
538	07:30:00	46
932	07:45:00	12
542	07:45:00	46
538	07:45:00	12
542	08:00:00	12
241	08:00:00	46
989	08:00:00	23

This is only the first 20 rows. The data currently goes down to 342, but is variable - though I would be quite shocked if the value was ever above 5k. The data is also sorted by date/time as per B:B - the earliest date/time will always be first.

I wish to create a table (I don't specifically mean the table feature in excel but table rather just organise the data in a more readable way) where the numbers in A:A act as a header across the top starting in C1. These headers should be unique (note values in A:A appear more than once, but the data should be consolidated if that makes sense). This 'table' will be on Worksheets("Results")

The date/time axis should begin in A4 - though this is where things are a little tricky. As per the source data, the first time would be 6:30 on the given date. Times are also dealt with in 15 minute intervals. however, notice that B12:B13 skips 45 minutes (three intervals or so). This means that using B:B as the Axis on the final table is not desireable as the axis may have missing chunks of time depending on the source data. To get around this, the date/time axis already exists on the result sheet - I have a bit of code that looks for the time in B1, places it in A4 and loops through rowno 4 To 148 (final row ~ 36 hours from start time but this doesn't really matter at this stage) adding 15 minutes each step.

With the example source data, the Results sheet should look like below - I'm looking for a VBA solution to this

ID#		286	214	1535	1580	1307	1029	57	932	538	542	241	989
Part (ignore)		///	///	///
Time
06:30:00		46	46	46	46	46	53	23
06:45:00		12	12	12	12	12
07:00:00
07:15:00
07:30:00									46	46	46
07:45:00									12	12
08:00:00											12	46	23

The gap in B and rows 2,3 is deliberate, I'll be adding other data as part of a sep process in those spaces - though if needs be, these can be ignored and I can simply add an extra column and two new rows at a later time.

I think I've covered everything, but if I've missed anything crucial, please let me know.

 

[Luke777]

Here you go. Even more dynamic. This will find the end of your data on sheet1.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID#		286	214	1535	1580	1307	1029	57	932	538	542	241	989
2	Part (ignore)
3	Time
4	6:30 AM		46	46	46	46	46	53	23
5	6:45 AM		12	12	12	12	12
6	7:00 AM
7	7:15 AM
8	7:30 AM									46	46
9	7:45 AM									12	12	46
10	8:00 AM											12	46	23
Sheet2

Cell Formulas
Range	Formula
C1:N1	C1	=TRANSPOSE(FILTER(UNIQUE(INDIRECT("Sheet1!A1:A" & MAX((Sheet1!A:A<>"")*(ROW(Sheet1!A:A))))),UNIQUE(INDIRECT("Sheet1!A1:A" & MAX((Sheet1!A:A<>"")*(ROW(Sheet1!A:A)))))<>0))
C4:N10	C4	=XLOOKUP(C1#&ROUND(A4:A10,5),Sheet1!A1:A20&ROUND(Sheet1!B1:B20,5),Sheet1!C1:C20,"")
A5:A10	A5	=A4+TIME(0,15,0)
Dynamic array formulas.

I ended up trying...

=XLOOKUP(C1#&ROUND(A4:A148,5),'All Activity'!A1:INDEX('All Activity'!A:A,COUNTA('All Activity'!A:A))&ROUND('All Activity'!B1:INDEX('All Activity'!B:B,COUNTA('All Activity'!B:B)),5),'All Activity'!C1:INDEX('All Activity'!C:C,COUNTA('All Activity'!C:C)),"")

just minutes before your post "All Activity" is the equivalent of sheet1 - it seems to do exactly the same thing as far as I can tell through testing. Is there any advantage/disadvantage to either method or just two ways of getting the same result? Though your method seems significantly shorter and simpler and probably better because of it :P

Also, thanks for your help!

 

[Fluff]

For the C1 formula I would use

=LET(Rws,COUNTA('All Activity'!A:A),rng,'All Activity'!A1:INDEX('All Activity'!A:A,Rws),TRANSPOSE(UNIQUE(rng)))

which is non-volatile.

 

[Fluff]

And a slightly shorter version of your formula for C4

=LET(Rws,COUNTA('All Activity'!A:A),Rng,'All Activity'!A1:INDEX('All Activity'!C:C,Rws),XLOOKUP(C1#&ROUND(FILTER(A4:A148,A4:A148<>""),5),INDEX(Rng,,1)&ROUND(INDEX(Rng,,2),5),INDEX(Rng,,3),""))

 

[eastrand]

For the C1 formula I would use =LET(Rws,COUNTA('All Activity'!A:A),Rng,'All Activity'!A1:INDEX('All Activity'!C:C,Rws),XLOOKUP(C1#&ROUND(FILTER(A4:A148,A4:A148<>""),5),INDEX(Rng,,1)&ROUND(INDEX(Rng,,2),5),INDEX(Rng,,3),""))

Without a doubt, this is a better formula.

And a slightly shorter version of your formula for C4 =LET(Rws,COUNTA('All Activity'!A:A),Rng,'All Activity'!A1:INDEX('All Activity'!C:C,Rws),XLOOKUP(C1#&ROUND(FILTER(A4:A148,A4:A148<>""),5),INDEX(Rng,,1)&ROUND(INDEX(Rng,,2),5),INDEX(Rng,,3),""))

This one? I have to ask. How is this shorter than =XLOOKUP(C1#&ROUND(A4:A10,5),Sheet1!A1:A20&ROUND(Sheet1!B1:B20,5),Sheet1!C1:C20,"")

 

[Fluff]

It's not, but it is shorter than the OP's formula which is using a dynamic range, rather than a hardcode one.

 

[BSALV]

old school method with a pivottable, refresh speed instantly ?
you want a different layout that that fixed layout ???
Place the pivottable in a dark corner of your sheet and the wanted layout is defined by the named range "TopLeft", here G25.
The 3 colored parts of the pivottable are copied to your layout.

Sub kopieren()
     Set pvt = Sheets("blad1").PivotTables(1)
     With pvt
          Set rr = .RowRange
          Set cr = .ColumnRange
          Set dbr = .DataBodyRange
     End With

     With Range("Topleft")
          .Offset(3).Resize(100, 100).ClearContents
          .Offset(, 1).Resize(, 99).ClearContents

          With .Offset(, 2).Resize(1, cr.Columns.Count)
               .Value = cr.Offset(cr.Rows.Count - 1).Value
               .Interior.Color = RGB(255, 0, 0)
          End With

          With .Offset(3).Resize(-dbr.Row + rr.Row + rr.Rows.Count, 1)
               .Value = rr.Offset(dbr.Row - rr.Row).Value
               .NumberFormat = "hh:mm:ss"
               .Interior.Color = RGB(0, 0, 255)
          End With

          With .Offset(3, 2).Resize(dbr.Rows.Count, dbr.Columns.Count)
               .Value = dbr.Value
               .Interior.Color = RGB(0, 255, 0)
          End With
     End With
End Sub

luke777.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	customer	time	nrs
2	286	6:30:00	46				Som van nrs	Kolomlabels
3	214	6:30:00	46				Rijlabels	57	214	241	286	538	542	932	989	1029	1307	1535	1580
4	1535	6:30:00	46				6:30:00	23	46		46					53	46	46	46
5	1580	6:30:00	46				6:45:00		12		12						12	12	12
6	1307	6:30:00	46				7:30:00					46		46
7	1029	6:30:00	53				7:45:00					12	46	12
8	57	6:30:00	23				8:00:00			46			12
9	286	6:45:00	12				9:00:00								23
10	214	6:45:00	12
11	1535	6:45:00	12
12	1580	6:45:00	12				ID#		286	214	1535	1580	1307	1029	57	932	538	542	241	989
13	1307	6:45:00	12				Part (ignore)		///	///	///
14	932	7:30:00	46				Time
15	538	7:30:00	46				6:30:00		46	46	46	46	46	53	23
16	932	7:45:00	12				6:45:00		12	12	12	12	12
17	542	7:45:00	46				7:00:00
18	538	7:45:00	12				7:15:00
19	542	8:00:00	12				7:30:00									46	46	46
20	241	8:00:00	46				7:45:00									12	12
21	989	9:00:00	23				8:00:00											12	46	23
22
23
24
25							ID#		57	214	241	286	538	542	932	989	1029	1307	1535	1580
26							Part (ignore)		///	///	///
27							Time
28							06:30:00		23	46		46					53	46	46	46
29							06:45:00			12		12						12	12	12
30							07:30:00						46		46
31							07:45:00						12	46	12
32							08:00:00				46			12
33							09:00:00									23
34
Blad1