|
本帖最后由 335805371 于 2014-6-13 11:03 编辑
D列是想要生成的,根据A列前三个字母,与B列匹配,如果匹配到了取对应C列加A列截取掉前三个字母后的值,匹配不到取A列原值。
注:A列有几万行数据,B列有二百多行数据,C列与B列相对应,C列单元值可能为空
A B C D
需要过滤的航班号 三字码 二子码 新生成的航班号
AAR201 DKH RK AR201
ABW139 CAL KJ RA139
BAW3304 ABW RA BAW3304
CAL063 NEG KJ063
THY342 AAR AR THY342
HRY3324 CAL EU HRY3324
DKH879 RK879
CAL234 KJ234
AAR0987 AR0987
EYT209 EYT209
- Sub test()
- Dim d As Object
- Dim ar
- Dim i As Long
- Dim sr As String
- Set d = CreateObject("scripting.dictionary")
- Cells(2, 4).Resize(Rows.Count - 1).ClearContents
- ar = Cells(1, 1).CurrentRegion
- For i = 2 To UBound(ar)
- If Not IsEmpty(ar(i, 2)) Then
- If Not d.exists(ar(i, 2)) Then d.Add ar(i, 2), ar(i, 3)
- End If
- Next i
- For i = 2 To UBound(ar)
- sr = Left(ar(i, 1), 3)
- If d.exists(sr) Then
- ar(i, 4) = Replace(ar(i, 1), sr, d(sr))
- Else
- ar(i, 4) = ar(i, 1)
- End If
- Next i
- Cells(1, 1).Resize(UBound(ar), 4) = ar
- End Sub
复制代码
|
|