Iterating and Transfer Data

Re: Iterating and Transfer Data

See if this helps you any.

Re: Iterating and Transfer Data

Quote from Muppley

the columns I need the information from may not be adjacent. Can this still be used?

Short answer: you should be able to use it just fine.

Long answer: it may need some slight modification.

My solution: create links in the output sheet to the desired columns from the input sheet. This way you can also move the calculations off of the input sheet. See attached example. (Note: the background colour is just to mark which cells I have linked to, it is completely unnecessary.)

Re: Iterating and Transfer Data

Quote from Muppley

That works brilliantly and exactly what I am looking for.
But I am a bit confused and I hope you can explain. If I wanted to create a list of, let's say, 9 columns instead of 3, what do I need to do to modify the calculations part and row 2 of the output sheet in your example?

I have put together an example with four columns, plus one status column. But first my attempt at an explanation.

The 'data links' section is simply a copy of all the columns that you want, lined up for the lookup table (if you look at the formulas, they are just simple cell references). So if you want nine columns instead of three, you would add links to the remaining columns here (in my posted example I have moved the status column to differentiate it from the data columns). Note: you can put these columns in any order you want, it does not have to be the same as on the input sheet.

The calculations section does not need to be changed for any change in the number of data columns, since it only checks the status column to see if it is a one & then determines a number for the current row. The result being that every incomplete row has the next even number in the sequence 2,4,6,... These row values are used for the vlookup function in the output.

For the output, you merely need to ensure that there is one column for each column in the data links & that the numbers at the top of each column form a proper sequence 2,3,4,... These numbers are used as the data column in the lookup function.

As to the output function itself, as an example, here is the function from the top left output cell:

=IF($I3<=MAX($D$3:$D$9),VLOOKUP($I3,$D$3:$H$9,J$2),"")

First there is an IF function to determine whether the current lookup value ("$I3") is less than or equal the maximum 'to find' value ("MAX($D$3:$D$9)"). If it is, then find the appropriate value & display it. If it is not, then display an empty string. This is just to make your output cleaner, as it would otherwise just display the last item in the input list in every output cell after the final incomplete value has been discovered. (You can try getting rid of the IF to see what I mean).

Next is the VLOOKUP function, which is a very helpful function, though a little tricky to use. The first argument is the lookup value ("$I3"); this is the value that the function is trying to find (in this case it is the series of even numbers 2,4,6,...). The next argument is the lookup table ("$D$3:$H$9"); this is the range of cells containing the column in which the lookup value will be searched for (the leftmost column of the table), as well as all of the possible output columns of the table (your data links in this case). The last argument that is being used here is the output column ("J$2"); this is the column of the lookup table from which to obtain the output data.

The way that VLOOKUP works is that it searches from top to bottom through the leftmost column of the lookup table & finds the last instance of the lookup value. Then it returns the data contained in the cell with the row that is found & the column of the lookup table that is specified in the function call. One thing to keep in mind is that the output column is relative to the output table, not the sheet itself. So if your output column is 2, it will output a value from the second cell of the lookup table, not the second cell of the sheet.

Ok, hopefully this explanation makes some sense to you. If you have any specific questions, just let me know. & here is the new example sheet I put together: