# inputing xyz data into a spreadsheet

I have excel data in 3 columns x,y, and z on sheet 1. These are coordinates with a z value. I want to place the z value into the correct x,y coordinate grid on page 2 of the excel spread sheet. Such as at x=2, y=3, z=5 therefore the data in cell b3 of sheet 2 should be 5. The original data includes several hundered points. All in excel!

UPD:

If you can confirm that all your z coordinates on sheet 2 has corresponding x and y (i.e. there is no situation when you tries to find z coordinate for cell b3 and there is no pair x=2 and y=3 in sheet1), you can use this formula on sheet2:

=SUMPRODUCT((Sheet1!\$A:\$A=Row())*(Sheet1!\$B:\$B=Column()),Sheet1!C:C)

else, if you can't confirm that all your z coordinates on sheet 2 has corresponding x and y, use following formula:

In both formulas Sheet1!C:C is anddress of your z coordinates on sheet1, Sheet1!A:A and Sheet1!B:B is anddress of your x and y coordinates on sheet1.

This task would be best carried out with VBA, something like this:

```Sub Demo()
Dim wf As WorksheetFunction
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rSrc As Range
Dim rdst As Range
Dim vSrc As Variant
Dim vDst As Variant
Dim i As Long

Set wsSrc = Sheet1
Set wsDst = Sheet2

Application.ScreenUpdating = False
Set wf = Application.WorksheetFunction

' Get a reference to the source data
With wsSrc
Set rSrc = Range(.Cells(1, 3), .Cells(.Rows.Count, 1).End(xlUp))
End With

' Validate source data
If wf.Floor(wf.Min(rSrc.Range("1:2")), 1) <= 0 Then
MsgBox "Invalid data, contains co-ordinates <= 0"
Exit Sub
End If

' Copy Source to a variant array
vSrc = rSrc.Value

' Prepare Destination sheet
wsDst.Cells.Clear
With wsDst
Set rdst = Range(.Cells(1, 1), .Cells(wf.Max(rSrc.Columns(2)), wf.Max(rSrc.Columns(1))))
End With

' Populate destination sheet
For i = 1 To UBound(vSrc, 1)
rdst.Cells(CLng(vSrc(i, 2)), CLng(vSrc(i, 1))) = vSrc(i, 3)
Next
Application.ScreenUpdating = True
End Sub
```

Note, I have used writing directly to the destination sheet in the loop rather than a Variant Array, something I would not normally do. In this case the destination is a large, sparsely populated array. Depending on the max x, y values this can lead to Out of Memory errors. Tested with several 1000 data points, runs in a few seconds (OP says several 100 data points)