Loop to two columns and Concatenate values

Printable View

04-11-2013, 10:00 PM
ivandgreat

1 Attachment(s)

Loop to two columns and Concatenate values

Hi,

Anyone could help me on this,

Loop To Last EndPt

Br,
Ivan

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-vbihZ-7W
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-vmq-LHHz
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vst3j_7i
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwBqjIR5Nj
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwBw8El0r5
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwC63GbRuM
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwC9fyKZdo
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwCEn8DBQe
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bw0Bey8gQO
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
04-12-2013, 11:38 AM
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

Code:

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(strDataStartCell).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(strOutPutCell) .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:
04-14-2013, 12:22 AM
ivandgreat

unable to see the codes.. :(
04-14-2013, 01:05 AM
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

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.
04-14-2013, 01:45 AM
ivandgreat

Quote:

Originally Posted by 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

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
04-14-2013, 10:42 AM
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.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=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=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=312724#p312724
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?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
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
04-14-2013, 11:06 AM
Rick Rothstein

Quote:

Originally Posted by LalitPandey87

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.
04-14-2013, 05:39 PM
LalitPandey87

My code is giving exact the same output as he shown in the attachment.
04-14-2013, 06:41 PM
ivandgreat

Quote:

Originally Posted by LalitPandey87

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
04-14-2013, 06:55 PM
LalitPandey87

Change this line

Code:

Const strOutPutCell As String = "H1" 'Change accordingly

with this one

Code:

Const strOutPutCell As String = "A1" 'Change accordingly
04-14-2013, 07:17 PM
Rick Rothstein

Quote:

Originally Posted by ivandgreat

@ 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

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
04-14-2013, 07:33 PM
LalitPandey87

Quote:

Originally Posted by Rick Rothstein

@LalitPandey87,

See the above link for the sample data ...

Hi Rick,

I have checked the same on second sample data and it works fine.
04-14-2013, 07:50 PM
Rick Rothstein

Quote:

Originally Posted by LalitPandey87

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:
04-14-2013, 07:59 PM
LalitPandey87

%p
04-14-2013, 08:02 PM
Rick Rothstein

Quote:

Originally Posted by LalitPandey87

%p

:laugh:
04-14-2013, 08:20 PM
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.