How to create a cycle between two list's in Excel ?

Solved

I need help, please. I will try to explain the problem.

I have a list of parameters, which I will call k, to better understand the problem. I need to put each element individually from list k in a system that will generate another list x and I need to get the maximum value of list x for each individual element in list k.

Is there any method that I can use from Excel or a VBA tutorial that can help me?

EDIT_1:

Sorry for not being able to express myself properly.

-Both lists are numbers;

- I intend the biggest number in list X that is assigned to each number in list K.

I'm trying to automate this process, i took a print os me sheet in order to give a better understanding of the problem.

Image: https://ibb.co/yXhcXXW

Posted Dec 5, 2020, 11:02 PM UTC · edited

212
jentrxm1•

Your explanation isn't clear enough for me.

7
WYUYBDAAHNFIAGT•

Had me up until the last part. The maximum value of list x for each element in k?

2
DignusCognitu•

Are you saying that you have a set of values, k, and for each value in k you will generate another set x with a function? And you need to get the maximum value in x for each value in k?

1
FirstDefinitionOP•

yes sir that's exactly it

1
schfourteen-teen•

Check out Data Tables. Setup the sheet where you can type a single value of k and then it will generate the x values.

The data table comes into play because it let's you provide a list of values that you want to plug into your sheet and return the result for each one. So the data table part is where you would make your list of k values.

1
FirstDefinitionOP•

Thank you very much was exactly what I needed

2
PedroFPardo4•

An example, even with just a few fake numbers, will help with your explanation.

First question: The list of elements in X are numbers?

Second question: When you say Maximum...

You mean

(1) The biggest 'number' in list X assigned to each element of K

or

(2) The element of K with the most number of elements of X assigned to them?

1
FirstDefinitionOP•

Yes the list X are numbers. I mean (1), the biggest number in list X that is assigned to each number in list K.

I took a print of my sheet: https://ibb.co/yXhcXXW

1
mikeyj7774•

Say your list of values "k" goes from cells A1 to A20. I would write a user defined function in VBA, call it myFunction that returns an array. Research this, it won't take very long to write. Let's assume it outputs an array 20 values long.

In cell B1, I would have =myFunction(A1). Copy and paste this from B1 over to W1 (one column for each returned element). Hit Ctrl-Shift-Enter for Excel to populate an array in these cells.

Copy and paste the cells from B1 to W1 down to B2 to W20.

In some unused cell in each row of the outputted data, write =max(Bx:Wx) where x is the row number. Copy and paste that down.

1
Obriquet•

Need something to work with, that's not very clear

1
GraysonFerrante•

I'd use a kDic scripting.Dictionary and add the K values. Then grab an array of kDic.keys and for each array element plug the value in to your sheet. I'd the read the x result area from that one plugged value into an array using the range and go thru the result array looking for the highest. I'd probably create a max array with both the max and the K value on the same row, and once done I'd pop that max array into a blank area of the sheet using range set equal to the max array. This set would use cells(21,26) for e.g. row 21 column z(26=z) to say which four corners.

Would take me an hour to write and another hour or two to refine. Hope the tidbits give you direction in your googling.

1
Phebian12•

Hello!

i will help you to solve this problem

-1