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,

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Item[/TD]
[TD]IDA[/TD]
[TD]IDB[/TD]
[TD]Stor[/TD]
[TD]Avail[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2000[/TD]
[TD]2001[/TD]
[TD]10[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]20[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2002[/TD]
[TD]2004[/TD]
[TD]30[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2004[/TD]
[TD]
[/TD]
[TD]40[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2005[/TD]
[TD]2004[/TD]
[TD]50[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


The output table below,

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Item[/TD]
[TD]IDA[/TD]
[TD]IDB[/TD]
[TD]Stor[/TD]
[TD]Avail[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2000[/TD]
[TD]2001[/TD]
[TD]10[/TD]
[TD]40 / 30 / 20 / 10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]20[/TD]
[TD]40 / 30 / 20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2002[/TD]
[TD]2004[/TD]
[TD]30[/TD]
[TD]40 / 30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2004[/TD]
[TD]
[/TD]
[TD]40[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2005[/TD]
[TD]2004[/TD]
[TD]50[/TD]
[TD]40 / 50[/TD]
[/TR]
</tbody>[/TABLE]


br,
ivan

 

[ivandgreat]

Anyone could help me on this. thanks!

 

[Rick Rothstein]

Anyone could help me on this. thanks!

Maybe... need a question answered first though. Will there only ever be one empty cell in Column IBD like your sample shows? If not, then show us a sample with two blanks (make sure the 2nd blank is not on the last row) so we can see how to transition between the two blanks.

 

[ivandgreat]

Maybe... need a question answered first though. Will there only ever be one empty cell in Column IBD like your sample shows? If not, then show us a sample with two blanks (make sure the 2nd blank is not on the last row) so we can see how to transition between the two blanks.

There will be a lot empty value for IDB but i think you could
consider column IDA.

IDA won't have any empty and won't be duplicated.

Thanks a lot.

 

[Rick Rothstein]

There will be a lot empty value for IDB but i think you could
consider column IDA.

IDA won't have any empty and won't be duplicated.

Huh? Sorry, that does not make sense to me. My understanding of what you want is to take the value in Stor column (where there is a blank cell in the IDB column) and concatenate it (with a slash delimiter) upward and downward with the value in the Stor column above and below the blank cell, then to concatenate those concatenations with the cells above or below them, and so on (all of which is to go into the Avail column). The problem with that arrangement is knowing what should be done between two blank cells since the order of concatenations is different for cells between them depending on the direction being traveled in from each blank. I have no idea what the IDA column has to do with that process unless there is something about your data that you know and are assuming, for whatever reason, that we know also. If you would provide the example data I asked for in my first message (showing two blanks in the IDB column and data before, between and after them), I think someone here will be able to figure out a solution for you or, at worst, come up with some more questions to clarify things some more.

 

[ivandgreat]

I just thought that it will start coding to find the empty value on IDB then from there it will get the value to be concatenated with the other rows.

Column IDA will tell you which row it will go next since it will be used to find it in column IDB.

ex. If column IDB found empty, input Stor value at column Avail then from this row IDA have a value of 2004 (carry the Stor value 40), this value will be located on column IDB which found in row 3 and 5, then concatenate with the value carried (40) with Stor value which will give you Avail value of 40 / 30 and 40 / 50, respectively. Then this two rows will look the IDA value, row 5 with 2005 unable to find it in column IDB so stops there, but row 3 with 2002 will find in column IDB at row 2 so it will concatenate the value from Its Avail value, so it will have 40 / 30 / 20 then same loop for the next IDA until found nothing match in column IDB.

hope you could help me on this.

regards,
ivan

 

[ivandgreat]

2nd Sample with output


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Item[/TD]
[TD]IDA[/TD]
[TD]IDB[/TD]
[TD]Stor[/TD]
[TD]Avail[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2000[/TD]
[TD]2001[/TD]
[TD]10[/TD]
[TD]40/30/20/10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]20[/TD]
[TD]40/30/20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2002[/TD]
[TD]2004[/TD]
[TD]30[/TD]
[TD]40/30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2004[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2005[/TD]
[TD]2004[/TD]
[TD]50[/TD]
[TD]40/50[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2003[/TD]
[TD]2006[/TD]
[TD]20[/TD]
[TD]100/20[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2007[/TD]
[TD]2003[/TD]
[TD]50[/TD]
[TD]100/20/50[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2006[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[ivandgreat]

Hi,

Anyone could help me on this, thanks a lot.

br,
ivan

 

[Rick Rothstein]

2nd Sample with output


[TABLE="class: grid, width: 300"]
<TBODY>[TR]
[TD]Item
[/TD]
[TD]IDA
[/TD]
[TD]IDB
[/TD]
[TD]Stor
[/TD]
[TD]Avail
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2000
[/TD]
[TD]2001
[/TD]
[TD]10
[/TD]
[TD]40/30/20/10
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2001
[/TD]
[TD]2002
[/TD]
[TD]20
[/TD]
[TD]40/30/20
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2002
[/TD]
[TD]2004
[/TD]
[TD]30
[/TD]
[TD]40/30
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2004
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]2005
[/TD]
[TD]2004
[/TD]
[TD]50
[/TD]
[TD]40/50
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2003
[/TD]
[TD]2006
[/TD]
[TD]20
[/TD]
[TD]100/20
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2007
[/TD]
[TD]2003
[/TD]
[TD]50
[/TD]
[TD]100/20/50
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]2006
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[/TR]
</TBODY>[/TABLE]

I have been looking at your question on-and-off for a couple of days now and I cannot get my head around the mechanism used to fill in the Avail column. For the first blank in IDB at row 4, it looks like you check the number in IDA to its left (on row 4) and see if the number in IBD above it (row 3) is the same... if so, you concatenate the Stor values, then you check the IDA value to the left of that match (on row 3) with the IDB value above it (on Row 2) and if they match, you do another concatenation and you keep repeating that process until (I guess) the match stop or you reach the top of the table. However, that does not seem to be the mechanism you follow for the second blank (on row 8)... for that one, you do not attempt to match the value to its left with the value above it... you travel up column IDB until you find a match on row 6 and then, instead of traveling upward looking for matches like you did for the first blank, you travel back downward until you hit the blank where you stop. Also you did not do like I asked originally and show me number below the last blank (which might have given a clue to what is going on) so I do not know what should happen for values below the second blank. Can you clarify the mechanism (in detail please) as to how you decide what is being concatenated where?

 

[ivandgreat]

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.

thanks in advance.

br,
ivan