Iterations with a Vlookup UDF

Hey all,

I have done a fair share of vba programming and am familiar with a lot of the capabilites but am very much a novice in certain areas of programming. One of them would be interations with for loops. I have many sets of data which I am analyzing via various macros that perform different functions. Currently where I need help is with integrating a UDF to find all matches in a data range using Vlookup. This search is based off frame number and reports all of the x-coordinates found in the frame number. However, I'm almost positive to get this program to run dynamically in a macro it needs to be part of a for loop to detect and iterate through any number of frames a data set may contain. I've posted an example workbook to make things a little easier to visualize. As always feel free to ask questions and also any suggestions to reformat and clean up the UDF output would be greatly appreciated.

I guess you'd need to upload the Add-in if you want anyone to review your code.

Regards

Hi Impartial Derivative (cute name! ?)

Find the attached with a User Defined Function that will return the number of rows in a Frame (if you put it on the first line of the frame - I think). See if it helps.

I've only done a few of the cells with the UDF formula. See if it makes sense.

Hi Impartial Derivative,

I'm so confused!!??

Looking at your last attached in the first set of rows from row 4 to Row 19. I've given you a UDF to count the number of rows in this set. Now you are asking for the 5 closest distances to something - I know not what.

Is distance defined as the pythagorean distance between each set of points? Or do you want the closest X to all other X values in Column J going into L2?

Closest to what is my question. Are you looking for which Frame is closest to the Frame 0? If so then there will be only one answer (maybe). Then you say you are looking for the closest 5 points to what? Closest 5 other points in this set that are closest to the J Col K Col coordinates?

Trying to understand the question is sometimes the hard part!

Marvin,

I completely understand that this can be difficult to understand so thank you for asking for further clarification. The updated spreadsheet that I posted is a lot better example of what I'm looking to do with this macro.My first example was very poorly worded as I was simply looking for some kind of loop to apply a UDF to an entire range, however I did not explain myself well at all. As background information, I am currently researching cell motility and cell movement in respect to their nearest neighboring cells using time-lapse imaging.

Therefore, every trajectory or block of data is a different cell. The data displayed consists of x and y coordinates and intensity values which I am discarding in this macro. What I am trying to do is search my data set and report the nearest neighbors. From other publications I have determined that neighboring cells must be withing 20 pixels of the cell. Therefore, I need to find a way to some how search my data by frame number ( I was assuming a modified version of vlookup or index) and then report the x and y coordinates of the other particles if they satifisfy the criteria for being a neighboring cell.

I am assuming I would calculate the distance as SQRT(x2-x1)^2+(y2-y2)^2 and this would need to be <=20 to be a neighbor. The UDF I posted can return all of the x values in the data set for a certain frame number however it concatenates the data and displays them all in one cell. I'd really like to give each particle their own cells for formatting clarity.

Ok Then,

Lets start with a simple question. We are finding the neighbors of a cell we are going to call BOB. Are the rows (in your last sheet) where BOB is in 16 picture frames? I'm thinking it is?

If that is how your data shows up... Then in another cell were going to call FRED. His locations are in rows 22 through 24? He seems to slipped out of the picture frame after the first two frames of the picture.

So each set of X,Y coordinates if for a different cell you are tracking?

If this is what the data represents - it wasn't ovbious. Let me know if my above assumptions are correct.

Is your example a representative amount of data for the actual application, or might it be substantially larger? And if so, how much larger?

OK Impartial Derivative,

I'm having way too much fun with the problem. I better give it back and see if it gives you any answers that are useful.

1. I rearranged your data into a table. See it in Cols A to D
2. I created a table of distances between any two points in range G3 to AJ 32, This was distance formula from point on top to point on left of table. Good old Pythagorean
3. The table needs to be based on which frame you are looking at so I put a filter based on cell H2.
4. I added a spinner control to click on to change the frames or you can do it by hand.
5. I added a distance between points, just in case you wanted to include larger distance to definition.
6. I wrote some simple code to run through all the frames and count the times a cell was a neighbor. This is in rows 36 and down.

Great fun but will it help you determine who are the friendliest neighbors cells?

See a better attachment in the next post

Hi,

See the attached for one that really works. I've been having problems with "Save As" after installing PKZip last niight. Find attachment #7 that should work like I want it.

Originally Posted by MarvinP

Hi,

See the attached for one that really works. I've been having problems with "Save As" after installing PKZip last niight. Find attachment #7 that should work like I want it.

Marvin,

You very much changed my initial spreasheets format but you made a great workbook nonetheless. I am very impressed however I have few questions about further calculations I am looking to incorporate to the spreadsheet. First off I'm looking to test for directionality of the cells, basically just assigning a direction to the cells and then using this data to calculate the angle between two neighboring cells. Also, I need I was looking to do a basic average distance calculation amongst neighboring cells in a given frame. For both of these calculations, I'm wondering if there is a way to extract the data of the cells in the table and complete these calculations(I'm pretty sure this is no big deal). The largest of my concerns is just as a previous poster asked, the data sets get extremely large and are saved as tab delimited workbooks. How am I to import new data to this spreadsheet and will this be a really cumbersome way to perform my calculations with large data sets? Thanks again

Hi,

Sure - two points of a given cell (between frame numbers) could determine directionallity. Two Cells directions and angle between their directions... (Vector Cross Product stuff?). Importing tab delimited is no problem. NOW for the last part about looping through all the all combinations of cells. This could take a while with a few hundred cells to compare against a few hundred others. Pivot Tables are much faster than VBA loops.

You need to ask the question before starting to analyzing the data. Like: Do cells that live longer swim in the wake of other cells? OR Are some cells like football blockers for the cell who is carrieng the ball? I'm afraid you are going to end up with piles of data with no hypothisis and therefore no conclusion.

I understand exactly what you are saying and personally I have not analyzed the data as much as my grad student. I'm an engineering student and the professor and grad student I work for assume that because I am studying engineering that I am also a computer programmer. Specifically, we are looking at different motality and cooperativity patterns when certain growth factors such as EGF and TGF Beta are introduced to the cells. This could be applied to the proliferation and growth of certain proto oncogenes.

As far as my calculations and programs, I have been creating a series of macros to analyze data and specifically this macro I was trying to create was to identify nearest neighboring cells, calculate the angle between these cells and the STDEV of these angles in order to gain some insight to their cooperativity. The cell samples can be anywhere around 500 cells so the data can at times be massive. I'm just looking for the most efficient and easiest way to accomplish these calculations whether via macro or via loading a file into a workbook such as the one you created. I guess my question now is whether I should potentially try to write a macro to reformat my tab delimited data to be portrayed like your workbook and then add my last few desired calculations or if this will be too cumbersome with our amount of data.

Hi,
I think you start a new thread and attach the tab delimited file. Perhaps two of them and show how you want them to look after some VBA Macro stuff.

We get to look at what you start with and what you need it to look like after the macro and poof, you have it.

BTW - The table I gave you to show cell neighbors was not a Pivot Table. It was a table I made up on my own. It was like a table of distances between intersecting cell numbers. Look at one of the cells and see the Pythagoread theorum. It is all in the Dollar Signs which hold the row or column constant. You need to study each little part of the formulas, or ask to see what they do. It is NOT magic but simple steps that grow to do lots of things. Start learning the little things and the big picture will become clearer.

I'm never heard of cooperativity standards. And how is that related to angle traveled?