Formula help Please

[Kenja824]

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

http://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.

 

[Denial]

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.

 

[lilliput1]

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.

 

[sdz]

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.

 

[Kenja824]

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

http://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.

 

[3DDave]

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

 

[Kenja824]

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

http://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.

 

[3DDave]

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.

 

[Kenja824]

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

http://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.

 

[3DDave]

Ok, then this won't help.

https://files.engineering.com/getfi...eee2-4ab1-aa16-84ef838e1b4e&file=CAPTURE.xlsm

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.

 

[Kenja824]

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

http://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.