Im trying to match and replace values from a list

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3173266%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EIm%20trying%20to%20match%20and%20replace%20values%20from%20a%20list%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3173266%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20have%20a%20large%20dataset%20where%20I%20need%20to%20match%20a%20distance%20along%20a%20line%20and%20replace%20it%20with%20a%20pair%20of%20coordinates%2C%20but%20each%20distance%20has%20multiple%20depths.%20For%20example%3A%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EA%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20C%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E5%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%202%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%202%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20have%20my%20data%20in%20distance%20and%20depth%20(Columns%20A%20and%20B)%2C%20but%20I%20want%20it%20in%20the%20corresponding%20coordinates%20and%20depth%20for%20a%20final%20output%20of%20Column%20C%2C%20Column%20D%2C%20Column%20B.%20The%20issues%20that%20I'm%20having%20is%20that%20the%20layers%20taper%2C%20so%20they%20aren't%20the%20same%20size%2C%20and%20its%20a%20massive%20data%20set.%20I%20am%20trying%20to%20find%20a%20way%20to%20search%20the%20Column%20A%20so%20that%2C%20for%20instance%2C%20everywhere%20where%20the%20distance%20is%202%2C%20I%20can%20assign%20the%20corresponding%20coordinates%2C%20in%20this%20case%20(0%2C1)%2C%20for%20that%20distance.%20My%20data%20set%20has%20distances%20ranging%20from%200-1665%2C%20so%20repetitive%20find%20and%20replace%20isn't%20really%20an%20option.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense%20and%20thanks%20for%20any%20guidance%20you%20can%20provide.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3173266%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3173266%22%20slang%3D%22en-US%22%3EIm%20trying%20to%20match%20and%20replace%20values%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3173266%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20dataset%20where%20I%20need%20to%20match%20a%20distance%20along%20a%20line%20and%20replace%20it%20with%20a%20pair%20of%20coordinates%2C%20but%20each%20distance%20has%20multiple%20depths.%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20C%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%3C%2FP%3E%3CP%3E1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%3C%2FP%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%3C%2FP%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CP%3E5%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%202%3C%2FP%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20have%20my%20data%20in%20distance%20and%20depth%20(Columns%20A%20and%20B)%2C%20but%20I%20want%20it%20in%20the%20corresponding%20coordinates%20and%20depth%20for%20a%20final%20output%20of%20Column%20C%2C%20Column%20D%2C%20Column%20B.%20The%20issues%20that%20I'm%20having%20is%20that%20the%20layers%20taper%2C%20so%20they%20aren't%20the%20same%20size%2C%20and%20its%20a%20massive%20data%20set.%20I%20am%20trying%20to%20find%20a%20way%20to%20search%20the%20Column%20A%20so%20that%2C%20for%20instance%2C%20everywhere%20where%20the%20distance%20is%202%2C%20I%20can%20assign%20the%20corresponding%20coordinates%2C%20in%20this%20case%20(0%2C1)%2C%20for%20that%20distance.%20My%20data%20set%20has%20distances%20ranging%20from%200-1665%2C%20so%20repetitive%20find%20and%20replace%20isn't%20really%20an%20option.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense%20and%20thanks%20for%20any%20guidance%20you%20can%20provide.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3173266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

I have a large dataset where I need to match a distance along a line and replace it with a pair of coordinates, but each distance has multiple depths. For example:

 

A        B        C        D

1        0         0        0

2        0         0        1

3        0         1        0

4        0         1        1

5        0         1        2

2        1

3        1

4        1

3        2

 

So, I have my data in distance and depth (Columns A and B), but I want it in the corresponding coordinates and depth for a final output of Column C, Column D, Column B. The issues that I'm having is that the layers taper, so they aren't the same size, and its a massive data set. I am trying to find a way to search the Column A so that, for instance, everywhere where the distance is 2, I can assign the corresponding coordinates, in this case (0,1), for that distance. My data set has distances ranging from 0-1665, so repetitive find and replace isn't really an option.

 

I hope that makes sense and thanks for any guidance you can provide.

1 Reply

@jeffheenan It is not clear what you are doing and what the targeted result is. The case you referenced had the value in column B first, then the "," and the column A value second.
This is a snap of an example that may achieve what you are trying to do or will be helpful in some way.

 

Rodney_Barnett_0-1646524852027.png