How to convert a list of XYZ coordinates to XY table

[TinWhisker26p2]

I have a list of x, Y, Z coordinates. The data represents real-world grid locations (1764 or 42 x 42 points) . The data is not in order and the values float around target grid points.

How could I convert the data table into an X-Y matrix of Z values?
I can do it with brute force, but this is not practical for my application.

Attached an image of a sample list and table

 

[Fluff]

Hi & welcome to MrExcel.
As long as you are happy to rearrange the data so that it's in one range, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	x	y	z
2	1.01	5.01	50			1	2	3	4	5
3	0.98	1.01	55		1	55	58	51	48	51
4	1.02	2	43		2	43	46	55	57	43
5	1	4.04	40		3	51	47	49	56	57
6	1.04	3.02	51		4	40	50	47	52	48
7	2.01	3	47		5	50	50	53	46	57
8	1.97	0.98	58
9	1.95	4.97	50
10	2.04	4	50
11	2	2.04	46
12	3.02	3.04	49
13	3	4.02	47
14	3.04	5.04	53
15	3.01	1.02	51
16	2.97	1.95	55
17	4.01	1.97	57
18	3.98	1	48
19	4.02	4.95	46
20	4	3.01	56
21	4.04	3.98	52
22	5.01	1.04	51
23	4.97	2.97	57
24	4.95	4.01	48
25	5.04	2.01	43
26	5	5	57
Master

Cell Formulas
Range	Formula
F3:J7	F3	=LET(Ary,SORT(ROUND(A2:C26,0),{1,2}),Cols,MAX(INDEX(Ary,,1)),INDEX(INDEX(Ary,,3),SEQUENCE(ROWS(Ary)/Cols,,0)+SEQUENCE(,Cols,,Cols)))
Dynamic array formulas.

 

[Fluff]

Another option in case you can have missing data points.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	x	y	z
2	1.01	5.01	50			1	2	3	4	5
3	0.98	1.01	55		1	55	58	51	48	51
4	1.02	2	43		2	43	46	55	57	43
5	1	4.04	40		3	51	47	49	56	57
6	1.04	3.02	51		4	40	50	47	52	48
7	2.01	3	47		5	50	50	53	46	57
8	1.97	0.98	58
9	1.95	4.97	50			1	2	3	4	5
10	2.04	4	50		1	55	58	51	48	51
11	2	2.04	46		2	43	46	55	57	43
12	3.02	3.04	49		3	51	47	49	56	57
13	3	4.02	47		4	40	50	47	52	48
14	3.04	5.04	53		5	50	50	53	46	57
15	3.01	1.02	51
16	2.97	1.95	55
17	4.01	1.97	57
18	3.98	1	48
19	4.02	4.95	46
20	4	3.01	56
21	4.04	3.98	52
22	5.01	1.04	51
23	4.97	2.97	57
24	4.95	4.01	48
25	5.04	2.01	43
26	5	5	57
27
28
Master

Cell Formulas
Range	Formula
F3:J7	F3	=LET(Ary,SORT(ROUND(A2:C26,0),{1,2}),Cols,MAX(INDEX(Ary,,1)),INDEX(INDEX(Ary,,3),SEQUENCE(ROWS(Ary)/Cols,,0)+SEQUENCE(,Cols,,Cols)))
F9:J9	F9	=SEQUENCE(,MAX(ROUND(A2:A100,0)))
E10:E14	E10	=SEQUENCE(MAX(ROUND(B2:B100,0)))
F10:J14	F10	=LET(Ary,FILTER(ROUND(A2:C100,0),A2:A100<>""),IFNA(INDEX(INDEX(Ary,,3),MATCH(F9#&E10#,INDEX(Ary,,1)&INDEX(Ary,,2),0)),""))
Dynamic array formulas.

 

[TinWhisker26p2]

Thanks. My first few attempts with solution #1 and my full 1764 data. I got a #spill! error. I got past this by driving to the distant ends of the sheet and entered dummy data. Then, I got #value! errors. I think I need to play with a smaller data set first to see where my error might be.
Thanks for giving me a good place to start. I think Choice #1 would work.

 

[Fluff]

If you are getting a #Spill error, it means that there are some non empty cells in the way of the formula.

 

[TinWhisker26p2]

Hi Fluff, I could not get your suggestions to work.
I did find an easier work-around versus my original brute-force method. This takes several easy steps. My X and Y locations don't line up perfectly: 1.001, 0.998, 1.004....., but these are just addresses on my grid. The values hover around 42 discrete values. Why not force them into integers, and then sort...

I created three new columns: (#, X2 and Y2): # just lists integers 1 to 1764 (42x42 array). I sort my data by X (data), and fill X2 column 1, 1, 1...., 2, 2, 2, ..... 42, 42, 42). I re-sort (data & X2) by Y (data) and number (Y2) column in the same way: 11111..., 22222...., 33333........42. Then I do custom sort, first by X2, then by Y2 so my data reads (1, 1, Z); (1, 2, Z)..... I copy just my Z column and paste in a new column (say starting in Cell F1 down to Cell F1764). For Cell G1: G1 = F43 and copy down. H1 = G43.... Copy down and over until my columns are just copying zeros. Copy the array of cells from F1 to AV42, paste on new tab as values and BOOM!

It's not elegant, but it works.

 

[Fluff]

Glad you sorted it & thanks for the feedback.