Loop to two columns and Concatenate values

[ivandgreat]

Hi,

Anyone could help me on this,


Loop To Last EndPt (http://www.mrexcel.com/forum/excel-questions/696146-loop-last-endpt.html)


Br,
Ivan


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vOQApTgb (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vOQApTgb)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vbihZ-7W (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vbihZ-7W)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vfmpSO0F (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vfmpSO0F)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vjfTJ7lX (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vjfTJ7lX)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vmq-LHHz (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vmq-LHHz)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vst3j_7i (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vst3j_7i)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBqjIR5 Nj (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBqjIR5 Nj)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBw8El0 r5 (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBw8El0 r5)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC63GbR uM (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC63GbR uM)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC9fyKZ do (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC9fyKZ do)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwCEn8DB Qe (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwCEn8DB Qe)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bw0Bey8g QO (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bw0Bey8g QO)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[LalitPandey87]

Try this:
In my case data started from cell A1 and output data started from H1 so please change code accordingly if you need some changes






Option Explicit

Sub LMP_Test()

Dim varArrData() As Variant
Dim varArrTemp() As Variant
Dim varArrTemp1() As Variant
Dim lngLoop As Long
Dim lngIndex As Long
Dim varVal1 As Variant
Dim varVal2 As Variant
Dim strOutput As String

Const lngIDACol As Long = 2 'Change accordingly
Const lngIDBCol As Long = 3 'Change accordingly
Const lngStorCol As Long = 4 'Change accordingly
Const lngAvailCol As Long = 5 'Change accordingly
Const strDataStartCell As String = "A1" 'Change accordingly
Const strOutPutCell As String = "H1" 'Change accordingly
Const strSheetName As String = "Sheet2" 'Change accordingly
Const strConcatDelima As String = " / " 'Change accordingly

varArrData = ThisWorkbook.Worksheets(strSheetName).Range(strDat aStartCell).CurrentRegion.Value
varArrTemp = varArrData
varArrTemp1 = varArrTemp
For lngLoop = LBound(varArrTemp) + 1 To UBound(varArrTemp)
'varVal1 = varArrTemp1(lngLoop, lngIDACol)
varVal2 = varArrTemp1(lngLoop, lngIDBCol)
strOutput = vbNullString
strOutput = varArrTemp1(lngLoop, lngStorCol) & IIf(strOutput <> "", strConcatDelima, "") & strOutput
DoLoop:
If varVal2 = "" Then GoTo ContinueForLoop
lngIndex = GetArrayIndex(varVal2, varArrTemp1, lngIDACol)
If lngIndex > 0 Then
strOutput = varArrTemp1(lngIndex, lngStorCol) & IIf(strOutput <> "", strConcatDelima, "") & strOutput
'varVal1 = varArrTemp1(lngIndex, lngIDACol)
varVal2 = varArrTemp1(lngIndex, lngIDBCol)
Else
lngIndex = 0
varVal1 = vbNullString
varVal2 = vbNullString
End If
lngIndex = 0
GoTo DoLoop
ContinueForLoop:
varArrTemp1(lngLoop, lngAvailCol) = strOutput
strOutput = vbNullString
Next lngLoop
With ThisWorkbook.Worksheets(strSheetName).Range(strOut PutCell)
.Resize(, UBound(varArrTemp1, 2)).EntireColumn.Clear
.Resize(UBound(varArrTemp1), UBound(varArrTemp1, 2)).Value = varArrTemp1
End With

Erase varArrData
Erase varArrTemp
Erase varArrTemp1
lngLoop = Empty
lngIndex = Empty
varVal1 = Empty
varVal2 = Empty
strOutput = vbNullString

End Sub

Function GetArrayIndex(ByVal Val As Variant, ByVal varArr As Variant, Optional lngColNo As Long = 1) As Long

Dim varDataArr As Variant

GetArrayIndex = 0
On Error Resume Next
With WorksheetFunction
varDataArr = .Index(Application.Transpose(varArr), lngColNo)
GetArrayIndex = .Match(Val, varDataArr, 0)
End With
On Error GoTo -1: On Error GoTo 0: Err.Clear

varDataArr = Empty

End Function




:cheers:

[ivandgreat]

unable to see the codes.. :(

[Rick Rothstein]

I see your code does what the OP showed for his small sample data set, but I am not convinced it is an all-encompassing solution although I cannot tell for sure because, after repeated efforts in the original forum, I cannot seem to get him to post a larger data sample. Another reason I am unsure if your code is the "ultimate" solution has to deal with the question I raised back in the other forum...

Loop To Last EndPt (http://www.mrexcel.com/forum/excel-questions/696146-loop-last-endpt.html#post3444731)

Once the OP answers that question and provides a larger data set to see how it all applies, I think then we will know if your code "does it all" or not and, if not, we should be able to figure out how to modify it so that it does.

[ivandgreat]

I see your code does what the OP showed for his small sample data set, but I am not convinced it is an all-encompassing solution although I cannot tell for sure because, after repeated efforts in the original forum, I cannot seem to get him to post a larger data sample. Another reason I am unsure if your code is the "ultimate" solution has to deal with the question I raised back in the other forum...

Loop To Last EndPt (http://www.mrexcel.com/forum/excel-questions/696146-loop-last-endpt.html#post3444731)

Once the OP answers that question and provides a larger data set to see how it all applies, I think then we will know if your code "does it all" or not and, if not, we should be able to figure out how to modify it so that it does.


Hi Rick,

I just add much larger data for this forum with expected output. Please have a look.

br,
ivan

[LalitPandey87]

where is the data. i am not able to see it or you missed it :confused:

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

where is the data. i am not able to see it or you missed it :confused:
I think the OP is answering questions across forums. The data he posted in his attachment in this forum is more than the sample data he posted in the MrExcel forum and I believe his last message is just telling me that as a response to my message (#4) in this thread. Unfortunately, the OP still did not provide an explanation of why the methods of moving upward from the blanks is handled differently for the two spaces he showed in the sample data he posted to the MrExcel forum (unfortunately, his attached file in this forum does not show the problem he exposed with his sample data in the MrExcel forum); hence, I am still confused as to what the underlying rule is for filling out the Avail column; so, unless he posts what that underlying rule is, I know I will not be able to help him further... hopefully you are understanding it sufficiently to carry on. I'll jump back in if the OP eventually posts a clear description explaining the problem I wrote to him about for his MrExcel sample data.

[LalitPandey87]

My code is giving exact the same output as he shown in the attachment.

[ivandgreat]

My code is giving exact the same output as he shown in the attachment.

@ LalitPandey87, thanks it works fine.

What should i edit to only have the results (col Avail), instead of duplicating the whole table.

br,
ivan

[LalitPandey87]

Change this line





Const strOutPutCell As String = "H1" 'Change accordingly




with this one





Const strOutPutCell As String = "A1" 'Change accordingly

[Rick Rothstein]

@ LalitPandey87, thanks it works fine.

@ivandgreat and LalitPandey87,

Try LalitPandey87's code against the second sample data you showed over in the MrExcel forum here...

Loop To Last EndPt (http://www.mrexcel.com/forum/excel-questions/696146-loop-last-endpt.html#post3441708)

I get the code crashing Excel completely (if you do not stop it earlier enough using CTRL+Break). So my question is... was that second sample data real or not (it was the entire reason I did not give you a solution).


@LalitPandey87,

See the above link for the sample data and this link for why I did not understand how to provide any code to the OP (and for a hint at what I think is causing your code to crash when used against it)...

Loop To Last EndPt (http://www.mrexcel.com/forum/excel-questions/696146-loop-last-endpt.html#post3443762)

[LalitPandey87]

@LalitPandey87,

See the above link for the sample data ...


Hi Rick,

I have checked the same on second sample data and it works fine.

[Rick Rothstein]

I have checked the same on second sample data and it works fine.
I tried it again after reading your message and the data still crashed your code. Playing a hunch, I rebooted my computer and now, sure enough, your code works perfectly with that data. My computer must have picked up a "glitch" along the way which had been affecting Excel in some way. Sometimes I just hate computers! :feelingbeatup:

[LalitPandey87]

%p

[Rick Rothstein]

%p
:laugh:

[ivandgreat]

Hi Rick,

Appreciate if you have another solution for this, just to have other option and also learned from different approach.

Thanks again to both of you.