Do While or Loop Until

[mdavid]

Hi,
need to compare cell value of column A in 2 sheets, if they're equal, then create a comma separated list from values in col B like sheet2 from Sheet1.:
Sheet1 input:
A | B
1 | c
2 | d
| e
| f
3 | g
4 | h
5 | g
| I

Sheet2 output:
A | B
1 | c
2 | d, e, f
3 | g
4 | h
5 | g, I

Having trouble with the logic, any help or code snippets appreciated
Thanks
David

 

[Pete Wright]

Hi,

could you upload a file with the data or at least post a screenshot?
It's not really clear to me how your input and output sheets look like.

Kind regards
- Pete

 

[bosco_yip]

Combine 2 sheets into 1 sheet for the output result in better referencing

1] Helper column C2, copied down :

=IF(B3="",B2,IF(LOOKUP(9^9,A$3:A3)=LOOKUP(9^9,A$2:A2),B2&", "&C3,B2))

2] Output result E2, copied down :

=IFERROR(INDEX(A$2:A$9,MATCH(0,INDEX(COUNTIF(E$1:E1,A$2:A$9&""),0),0)),"")

3] Output result F2, copied down :

=IF(E2="","",VLOOKUP(E2,A$2:C$9,3,0))

p.s. helper column could be hidden if necessary

Regards
Bosco

 

[mdavid]

Thanks very much Bosco. Would it be possible to do this in VBA code?

Thanks
David

 

[ANKUSHRS1]

Sub mytest()
Dim mydic As New Dictionary
Dim c As Range
 
 Set c = Range("a1").CurrentRegion
 
arr = c
    
    For i = 1 To UBound(arr, 1)
    
    If arr(i, 1) = "" Then
    arr(i, 1) = arr(i - 1, 1)
    End If
    
    If mydic.Exists(arr(i, 1)) = True Then
     
    mydic(arr(i, 1)) = mydic(arr(i, 1)) & "," & arr(i, 2)
    Else
    mydic(arr(i, 1)) = arr(i, 2)
    End If
    
    Next
    
    Sheets(2).Range("a1").Resize(mydic.Count, 1) = Application.Transpose(mydic.Keys)
    Sheets(2).Range("B1").Resize(mydic.Count, 1) = Application.Transpose(mydic.Items)


End Sub