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!
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:
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)