Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Formula help Please

Status
Not open for further replies.
Kenja824

Kenja824

Automotive
Nov 5, 2014
949
0
0
US
In the image below, I need a formula I can put in column D.

D1 Formula would take F1 and find its match in the same column, then return contents of the H in the same row as the match.

Example: So D1 formula gets F1. Finds its match in column F, which happens to be F10. It returns H10.


Obviously there will be many more ID numbers in D and H, and the numbers will be a lot more jumbled.


Capture_msdyyg.png


Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Replies continue below

Recommended for you

Sort by date Sort by votes
I apologize. This is a confusing one and hard to explain. Hopefully this will do better....

I attached a working file. All information not needed is cleared due to company privacy issues. I dont want to take a chance of losing my job. lol

Column "I" is the Spot ID numbers. There are no duplicates in this file but sometimes we do get files that have some duplicates.

The problem is I need to find the opposite hand ID. These opposite ID spots should have an opposite spot that has identical location but the Y direction will have either a negative or positive (opposite of the original spot). Most of the time the spots are within 1mm.

Example (not shown in image)
Spot ID 89B670 has a location of X: 1389.6890, Y: 803.1803, 1377.7136
Spot ID 89B641 has a location of X: 1389.6890, Y: -803.1803, 1377.7136
These would be each others opposite hand ID numbers. So in column H, for the row that has 89B670, I would like it to find the opposite hand ID and return the ID number. In this case it is 89B641 and visa versa for the other spot.

Often the spots location from one side to the other can be off by a bit. So I was just looking to round the location numbers to whole numbers and that would find the majority of them for us. Which would still save us a lot of time.

I had no idea how to create a formula that could round the numbers in three columns and compare all three of them to the rest of the rows to do this. So I came up with a formula in column D that would round the numbers and cncatenate them, placing dashes in between them and removing the negative symbol. This would make it so for most spots, there would be a duplicate ID in column D. The two spots in the example above have the same IDs in column D.

So now my problem is finding a way to do a sort of VLookup that would find the duplicate and return the SPOT ID of the duplicate.





Capture_xnufir.png


Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
That is why I am not worrying about the 1mm. I just rounded the numbers to whole numbers first in column D. I would only need the formula to find the duplicates.

Example: H3 would have a formula that looks at D3, then finds its duplicate elsewhere in column D, and returns the cell from column I in the same row as the duplicate.

Seems like it would be a simple Vlookup, only the number it looks for is in the same column as the range it looks for it in. Thats where I get lost.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Sorting to the actual coordinates does not really work right because the spots location coordinates go 8 digits deep and we can have several on the same plain. It helps groups them closer but you need to spend time looking for the opposite each time still.

However that made me realize that the formula I created in the D column can be sorted and it places them right next to each other. I would prefer to have the opposite ID given to us so there is not human errors on looking at the wrong row for the duplicate and such, but it is a lot better than nothing.

Thanks


Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
The sheet you provided had no matches; there are (X, Y, Z) and (X, -Y, Z) pairs.

After sorting I tried:

=IF(AND(ABS(ABS(P2)-ABS(P3))<epsilon,ABS(ABS(Q2)-ABS(Q3))<epsilon,ABS(ABS(R2)-ABS(R3))<epsilon),"match","") (in this case on row 3) where I defined the name "epsilon".

I renamed a cell to "epsilon" so that any arbitrary closeness could be applied.
 
Upvote 0 Downvote
Something else I looked at was taking the sqrt(X^2 + Y^2 + Z^2) and sorting on that value. I noticed that

1453-899-1019
1453-899-1020

is a poorly matched pair, differing in the whole digit, but very little in the vector magnitude.
 
Upvote 0 Downvote
Skip's formula didnt do what I wanted but it gave me an idea how to make it work.

Instead of it searching for the match in the Y Pos, I have it looking for a match in my D column.

I placed this formula in cell G12 and it found its opposite hand ID.
=INDEX(H13:H$749,MATCH(D12,$D13:$D$749,0),1)

The problem is when you copy it down, it keeps the range it searches only those below it. So once it gets to an opposite hand ID that was found, it wont be able to look above to find the opposite hand ID of that spot.

I was looking up trying to add two ranges to search and cant make it work.

This is the formula I found and tried to use, but is only giving me a "#N/A"....

=INDEX(H$3:H$2000,MATCH(D6,COUNTIF(D$3:D5,D7:D$2000)>0,0))

If you place this formula in G6 you should be able to get a better idea what I am looking for.

Hopefully this will help.

------------

Skip

Your formula, from what I can gather, will find the first match and return the ID for that match. Unfortunately in using the coordinates, there can be 20 spots that are on the same plane in any one or even two directions. That formula will only find the first one.

My D column has this formula in the cells
=CONCATENATE(ROUND(P3,0),"-",ABS(ROUND(Q3,0)),"-",ROUND(R3,0))

This gives every spot its unique ID. The only spot that will have the same ID is its opposite hand ID.

------------

3DDave

Forgive me, but I am not even following you at all. Im thinking you are speaking above my level of understanding here.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
I want to try it. I just dont know how to convert to radius. [ponder] I can use a formula to convert using =Radian but I dont see what that does to help. Are you meaning to somehow convert all three coordinates to one radius or something?

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Ah, I see. It is a much more efficient way of doing what I do in the D column. Thank you for that.

----------

I apologize for making all this more difficult than it needs to be. I know finding the opposite hand ID for each spot number (if it has one) would be much easier with VB coding.

Currently, we are given an excel file that lists all the spots and all of the information for each spot. They can be as large as well over a thousand spots. Coordinates, what materials they weld. Part names they weld etc...

I am somewhat okay with a lot of formulas but I suck with VB coding. So when I got into helping them, I started seeing ways to add a formula here and there to the excel file to make info we need easier to find. Slowly it has morphed into much more. I can spend 20 minutes just setting up the excel sheet when I get it.

I had the choice of asking for someone to create coding to do everything that would also add an opposite hand ID column. Or I could ask help with one formula. I thought this would have been easier for those helping. lol

Sorry.

If I can just figure out how to get the Index Match to search two ranges, I think I will have it now.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
I think I have the formula I needed.

=IFERROR(INDEX(K3:K$3,MATCH(D4,D3:D$3,0)),INDEX(K5:K$2000,MATCH(D4,D5:D$2000,0)))

However only in the first cell it is needed, I have to use this formula...

=INDEX(K5:K$2000,MATCH(D4,D5:D$2000,0))

For some reason the other formula tries to force the range into row 2 and returns its own ID.

Column D will have 3DDave's =SQRT(R3^2 +S3^2 +T3^2) formula.

Probably not the proficient way about things, but it gets the job done.

Thankyou my friends for all of your help.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Attached is a copy of my real working file. I cleared out a lot of columns not needed for this. I changed info inti generic info.

I actually got the formulas working pretty good now.

I changed the iferror in my formula to look at everything below first and then above the cell I am searching for the duplicate of. This got rid of the problem of needing to change the formula for the first cell only.

We ran into a problem with a work mates file. His had duplicates of every spot, left and right hand due to having two different styles. To distinguish between styles I added a Concatenate for the furthest right two letters in a column. ...... Unfortunately doing this made the SQRT formula return a number with far more digits and caused a difference in many spots that should be different. So I had to switch back to my concatenate of the coordinates being rounded.

I tried to round the SQRT formula but either I did it wrong or it just doesnt take.

-----------

In the current work file attached, the two columns in gray are the updated formulas I added today.

So far, these do seem to work good.

You will notice currently it is sorted so the duplicates are together. When we are using the file to do our job, this is not always a top option for us. That is why I am doing this.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
You can round the sqrt.

I see that's not there in the latest sheet. Because if it was I could help. But if it's not there, then there's no helping.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kenja824
  • Locked
  • Question
Formula help please
Replies
11
Views
20
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
Im looking for some formula help please --- Again. lol
2
Replies
37
Views
38
Kenja824
Kenja824
Kenja824
  • Locked
  • Question
New Formula Help 1
Replies
3
Views
10
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
23
Kenja824
Kenja824
Kenja824_home
  • Locked
  • Question
Excel - Looking for a formula please 2
Replies
14
Views
23
BridgeSmith
BridgeSmith
Back
Top