×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

 Forum Search FAQs Links MVPs

(OP)
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.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

You have duplicates in F1? So when it gets to F10, what does that match? Does it return H1?

RE: Formula help Please

(OP)
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.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

So, for each spot, which is located at X Y Z you want to find the spot number(s) with X+/-t -Y+/-t Z+/-t where t is maybe 1.0mm

I don't think that is practical without some form of looping structure, ie VBA. Which I don't do any more.

Cheers

Greg Locock

New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

RE: Formula help Please

(OP)
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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

Sorting on the coordinates would co-locate the matching spot numbers.

RE: Formula help Please

(OP)
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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

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.

RE: Formula help Please

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.

RE: Formula help Please

(OP)
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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

Convert each coordinate into a radius. Then matching locations have a matching radius. Sort by radius.

Just try it.

RE: Formula help Please

(OP)
I want to try it. I just dont know how to convert to radius. 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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

RADIUS = sqrt(X^2 + Y^2 + Z^2)

RE: Formula help Please

(OP)
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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

(OP)
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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

Skip,

Just traded in my OLD subtlety...
for a NUance!

RE: Formula help Please

(OP)
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
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

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.

RE: Formula help Please

You might be able to remove the SQRT() operation and use the square of the radius instead of the actual radius.  It would be more efficient.

RE: Formula help Please

Instead of coming up with a formula why not sort all the rows and column with ascending X value then the the left and right hand part number and their location number will be displayed next to each other. If you are looking for a specific value in a particular column you can make it easier to find by writing this formula on an adjacent column and copying it down down all the rows:
=if(P3=1389.6890,1,"")
So the row with the match will have 1 displayed.
Also before sorting you should add a column named original order with row numbered in ascending order so you can resort the spreadsheet back to the original order when required.

RE: Formula help Please

I think this might work for you. =IFERROR(INDEX(I4:$I$749,MATCH(D3,D4:$D$749,0)),"")
I learned this from Leila Gharani. By making the range D4:$D$749 with a variable start and fixed end you are always searching the remainder of the range.

RE: Formula help Please

(OP)
lilliput1

The problem with that is while using the file, we have a TMP column we need to fill out.So every template we create we need to find the weldspots and type in what template number. If the spots are not sorted in order, it makes it a lot hard to find the spot without having to type each one into the FIND box.

SDZ

This is something that will definitely come in useful for me now and then. I wish I would have learned that a long time ago. However in this case, we need the opp hand spot to find its opp hand as well. Which means searching above and below. Still thanks for the new knowledge. :)

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

OK - I give up - why do you care to match them? What difference does it make?

RE: Formula help Please

(OP)
As I said above, I have a formula now that seems to work well. So there really is no need to work on this anymore.

A lot of templates we do are LH shown and RH Opposite. We will have the LH numbers already on the template and manually need to put in the RH numbers. It saves a lot of trouble when we have a way to just look at the Excel file and see the number already in the template and be able to see what the RH Opposite number is.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

OK - It looks like body welds on a car or other vehicle, but the data given before shows a number of cases where they don't match. If that's what is wanted then there's nothing else to care about.

RE: Formula help Please

(OP)
The formulas seem to work pretty good how I have it now. The worst thing now is that every new job I spend longer than I would like setting up the new file. lol

Maybe someday I will create a good dummy file and see if someone will write some code that I can just import and run and do it all. Unfortunately that will have to wait until things slow down some. Or I decided to take the time anyway. lol

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Formula help Please

Ok, then this won't help.

https://files.engineering.com/getfile.aspx?folder=...

If it would, you would paste the X,Y,Z,Spot# into the first 4 columns, select the rows you want to check and then select the "match_them" macro.

It's not particularly fast, but it looks for matches. The allowable gap is 3 millimeters as set by the epsilon variable in the macro. For the set of data, the biggest seemed to be around 0.4, but there's no information about the maximum expected difference.

I don't know what happens if you pick an entire column, but it's necessary and sufficient to pick only the entries in the first column. Picking more columns should not harm anything. Picking too many rows will slow it down.

RE: Formula help Please

(OP)
Thank you

Even though I would like to get this automated sometime, (also when things slow down for me) I will still want to look into this for my own knowledge of it. :)

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!