Loop To Last EndPt

[ivandgreat]

Hi,

anyone could help me to have a macro that will loop in a columns when cell data is found empty @ column IDB will search the adjacent value of column IDA @ column IDB until found nothing follows and concatenate the cell value @ column D and put it in column E.

this is the table below,

Item	IDA	IDB	Stor	Avail
1	2000	2001	10
2	2001	2002	20
3	2002	2004	30
4	2004		40
5	2005	2004	50

<tbody>
</tbody>

The output table below,

Item	IDA	IDB	Stor	Avail
1	2000	2001	10	40 / 30 / 20 / 10
2	2001	2002	20	40 / 30 / 20
3	2002	2004	30	40 / 30
4	2004		40	40
5	2005	2004	50	40 / 50

<tbody>
</tbody>

br,
ivan

 

[Rick Rothstein]

Hi Rick,

This is show the mechanic works,

First locate empty value in IDB, here found 2 item, in item 4 and 8, record the value in col Stor (40 and 100 respectively)
-@ first item found (item 4 with value 40) copy it @ col Avail then identify the value in IDA which are in the same row, which is 2004, here you have to locate the value in IDB
- With IDA value 2004, were found in item 3 and 5,
- Do first @ item 3, concatenate the recorded value (from item 4 with value 40) with value in Stor, which is 30, you'll have 40 / 30, then identify again the value in IDA (same row), which is 2002, locate it in col IDB (found in item 2), concatenate the recorded value, yo'll have 40 / 30 / 20, do the same loop until the value in IDA were unable to found in IDB.
- Still with IDA value 2004, if found nothing go to the next value, which is now IDB value 2005 (item 5), do the same loop.
- After finishing the above loop, go to the next empty value @ IDB and do the same logic as above.

End loop when IDB and IDA is both empty.

Hmm! I posted a follow-up question but I don't see it now, so let me ask it again (the best I can remember it). For the first blank (going upward), you find the 2004 on the left in the cell right above the blank... you then look to the left of that cell's and find its number in the cell above... and you continue moving upward that way until (?) you reach the top of the table (not sure exactly how it will end before the top if circumstances dictate). Note... you are always moving upward. Now, for the second blank... you find the value to its left in the cell two above the blank and you then move downward from there (not upward like you did for the first blank)! Can you explain your process in more detail so that I can understand what is controlling the movement above the blank?

 

[ivandgreat]

Hi Rick,

I just attached a much larger data in the other forum.

Loop to two columns and Concatenate values

bt,
ivan