Loop column then concatenate column (Scripting Dictionary)

[SamKhem]

Dear Senior members

I would like request you guide by if column C = USD then join column B with D and E with scripting dictionary as below.

Book1
	A	B	C	D	E	F
1	AA	BB	USD	DD	EE	FF
2	11	22	JPY	33	44	55
3	AA	BB	THB	DD	EE	FF
4	11	22	USD	33	44	55
5	AA	BB	JPY	DD	EE	FF
6	11	22	THB	33	44	55
7	AA	BB	USD	DD	EE	FF
8	11	22	JPY	33	44	55
9	AA	BB	THB	DD	EE	FF
10	11	22	USD	33	44	55
11	AA	BB	JPY	DD	EE	FF
12	11	22	THB	33	44	55
13	AA	BB	USD	DD	EE	FF
14	11	22	JPY	33	44	55
15	AA	BB	THB	DD	EE	FF
16	11	22	USD	33	44	55
17	AA	BB	JPY	DD	EE	FF
Sheet1

 

[Cubist]

I don't see how B is being join together with D and E. Which column is the expected result?

 

[SamKhem]

I don't see how B is being join together with D and E. Which column is the expected result?

concatenate column B & Column D & Column E. Could show result in column G?

 

[Cubist]

Try:

Sub JoinBDE()
    Dim ws As Worksheet
    Dim a, b
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'change as needed
    a = ws.Range("A1").CurrentRegion.Value
    
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        If a(i, 3) = "USD" Then b(i, 1) = a(i, 2) & a(i, 4) & a(i, 5)
    Next i
    
    ws.Range("G1").Resize(UBound(b, 1), 1).Value = b
End Sub

Book2
	A	B	C	D	E	F	G
1	AA	BB	USD	DD	EE	FF	BBDDEE
2	11	22	JPY	33	44	55
3	AA	BB	THB	DD	EE	FF
4	11	22	USD	33	44	55	223344
5	AA	BB	JPY	DD	EE	FF
6	11	22	THB	33	44	55
7	AA	BB	USD	DD	EE	FF	BBDDEE
8	11	22	JPY	33	44	55
9	AA	BB	THB	DD	EE	FF
10	11	22	USD	33	44	55	223344
11	AA	BB	JPY	DD	EE	FF
12	11	22	THB	33	44	55
13	AA	BB	USD	DD	EE	FF	BBDDEE
14	11	22	JPY	33	44	55
15	AA	BB	THB	DD	EE	FF
16	11	22	USD	33	44	55	223344
17	AA	BB	JPY	DD	EE	FF
Sheet1

 

[SamKhem]

It possible to run with million rows ? could you add scripting dictionary for more quick run?

Try:

Sub JoinBDE()
    Dim ws As Worksheet
    Dim a, b
   
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'change as needed
    a = ws.Range("A1").CurrentRegion.Value
   
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        If a(i, 3) = "USD" Then b(i, 1) = a(i, 2) & a(i, 4) & a(i, 5)
    Next i
   
    ws.Range("G1").Resize(UBound(b, 1), 1).Value = b
End Sub

Book2
	A	B	C	D	E	F	G
1	AA	BB	USD	DD	EE	FF	BBDDEE
2	11	22	JPY	33	44	55
3	AA	BB	THB	DD	EE	FF
4	11	22	USD	33	44	55	223344
5	AA	BB	JPY	DD	EE	FF
6	11	22	THB	33	44	55
7	AA	BB	USD	DD	EE	FF	BBDDEE
8	11	22	JPY	33	44	55
9	AA	BB	THB	DD	EE	FF
10	11	22	USD	33	44	55	223344
11	AA	BB	JPY	DD	EE	FF
12	11	22	THB	33	44	55
13	AA	BB	USD	DD	EE	FF	BBDDEE
14	11	22	JPY	33	44	55
15	AA	BB	THB	DD	EE	FF
16	11	22	USD	33	44	55	223344
17	AA	BB	JPY	DD	EE	FF
Sheet1

 

[Cubist]

It possible to run with million rows ? could you add scripting dictionary for more quick run?

It's working with an array that limits the interaction with the Excel sheet so it should be quick. Adding a dictionary here is unnecessary and it's not going to speed it up.

 

[SamKhem]

It's working with array with limited interaction with the Excel sheet. Adding dictionary here is unnecessary and it's going to speed it up.

Thank always assist with excel question. Have a nice weekend.