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!

Answers


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:

=IF(COUNTIFS(Sheet1!$A:$A,ROW(),Sheet1!$B:$B,COLUMN()),SUMPRODUCT((Sheet1!$A:$A=ROW())*(Sheet1!$B:$B=COLUMN()),Sheet1!$C:$C),"not found")

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)


Need Your Help

Confirming Google Analytics Install in Flash Environment using AS3 Mode

flash actionscript-3 google-analytics tracking

Just created a new GA account and initialized my tracker in AS3. How can I confirm that it is installed and working correctly? In GA web view it shows "Tracking Not Installed" but when I use the "

How to copy newly added document with metadata to another document library?

sharepoint copy

I need to copy the item that user just added (for example. myresume.doc or financial.xls) with the metadata (doc lib obtains the columns from content type, ct obtains the columns from site colum) and