s7659109

½Ð°Ýª©¥D:¥Îvlookup¤@©w­n±Æ§Ç,­Y­n¤u§@ªíacc ¤¤aÄæ­n¥[¤J±Æ§Ç¥\¯à¸Ó¦p¦ó¼g?

­ã´£³¡ªL

¦^´_ 9# s7659109


With Range("D2:D" & [A65536].End(3).Row)
¡@¡@.Formula = "=IF(ISNA(VLOOKUP(C2,accnum,2,)),"""",VLOOKUP(C2,accnum,2,))"
¡@¡@.Value = .Value
End With
Range("E:E").Replace 0, "", Lookat:=xlWhole

s7659109

ÁÂÁ¤G¦ì¤j¤jªº¦^µª,«e­±ªº°ÝÃDok,¦ýÁÙ¦³¥t¤@°ÝÃD:dÄæ,¥Hvlookup¨ú±o¸ê®Æ(¥u·|¤½¦¡,¤w³]¤F); eÄæ0²MªÅ

­ã´£³¡ªL

Sub NumberCode()
Dim Arr, i&, xD, T$, TC$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([D2], [A65536].End(3))
For i = 1 To UBound(Arr)
¡@¡@T = Arr(i, 1) & Arr(i, 2):  TC = xD(T)
¡@¡@If TC = "" Then xD(T) = "|" & Arr(i, 4): GoTo 101
¡@¡@If TC <> "|" & Arr(i, 4) Then xD(T) = "²§±`"
101: Next i
For i = 1 To UBound(Arr)
¡@¡@T = Arr(i, 1) & Arr(i, 2): Arr(i, 1) = ""
¡@¡@If xD(T) = "²§±`" Then Arr(i, 1) = "x"
Next i
[G2].Resize(UBound(Arr)) = Arr
End Sub
¡@
¡@

a5007185

¦^´_ 6# s7659109

§ÚVBA¬O©ÓÄò¤§«eªºCode¡A
½Ð°Ñ¦Ò
0801.zip (18.47 KB)

¤U¸ü¦¸¼Æ: 5

2018-8-13 17:11

1. Sub NumberCode()
   
2.     Dim i%, Str$, ArrStr$
   
3.     i = 2
   
4.     ArrStr = "" '²MªÅ¤w°O¿ý¨ìªº±ø¥ó
   
5.     Do Until Range("C" & i) = ""
   
6.         '±NAÄæ¡BBÄæ¡BDÄæ¦X¨Ö¬°¤@­Ó¦r¦ê§@¬°±ø¥ó
   
7.         Str = Range("A" & i) & Range("B" & i)
   
8.         '­Y¸Ó±ø¥ó¥¼°O¿ý¹L¡A«h°O¿ý¤§¡C
   
9.         If InStr("," & ArrStr & ",", "," & Str & ",") = 0 Then ArrStr = ArrStr & "," & Str
   
10.         '¼´¥XStr¦b°}¦CArrStr¤¤ªº¯Á¤Þ­È
    
11.         Range("F" & i) = "No. " & UBound(Split(Split(ArrStr, Str)(0), ","))
    
12.         i = i + 1
    
13.     Loop
    
14.     ConsistentJudgment ArrStr, UBound(Split(ArrStr, ","))
    
15. End Sub
    
16. 
    
17. Sub ConsistentJudgment(ArrStr, R%)
    
18.     Dim i%, Str$, N%, StrMeno$
    
19.     ReDim ArrMemo(R) As String
    
20.     '¬ö¿ýMemo¸ê®ÆºØÃþ
    
21.     i = 2
    
22.     Do Until Range("C" & i) = ""
    
23.         Str = Range("A" & i) & Range("B" & i)
    
24.         StrMeno = Range("D" & i)
    
25.         N = UBound(Split(Split(ArrStr, Str)(0), ","))
    
26.         If InStr("," & ArrMemo(N) & ",", "," & StrMeno$ & ",") = 0 Then ArrMemo(N) = ArrMemo(N) & "," & StrMeno
    
27.         i = i + 1
    
28.     Loop
    
29.     '¦^õX§P©wµ²ªG
    
30.     i = 2
    
31.     Do Until Range("C" & i) = ""
    
32.         Str = Range("A" & i) & Range("B" & i)
    
33.         StrMeno = Range("D" & i)
    
34.         N = UBound(Split(Split(ArrStr, Str)(0), ","))
    
35.         If UBound(Split(ArrMemo(N), ",")) > 1 Then Range("G" & i) = "x"
    
36.         i = i + 1
    
37.     Loop
    
38. End Sub

½Æ»s¥N½X

s7659109

¦p¦ó§ï¬°vba¤è¦¡¡H

a5007185

¦^´_ 4# naruto018


    ³Ì«á­±¨M©w®æ¦¡¤Æ¥i¥Hª½±µ§PÂ_GÄæ¬O§_¬°"x"

naruto018

¦^´_ 3# s7659109

¼Ð¥Üx¡G
G2¶ñ¤J¡A¨ä¾l¤U©Ô

1. =IF(COUNTIFS(A:A,A2,B:B,B2)=COUNTIFS(A:A,A2,B:B,B2,D:D,D2),"","x")

½Æ»s¥N½X

¼Ð¥Ü¤Ï¶À¡G
®æ¦¡¤Æ±ø¥ó
¨Ï¥Î¤½¦¡¨M©w®æ¦¡¤Æ

1. =COUNTIFS($A:$A,$A2,$B:$B,$B2)<>COUNTIFS($A:$A,$A2,$B:$B,$B2,$D:$D,$D2)

½Æ»s¥N½X

½d³ò¡GA2:G27

s7659109

¬O¡Aa.bÄ欰¤@²Õ¡]¬Û¦P¦r¦ê¡^¡A¦ýdÄ檺¦r¦ê¤]­n¦C¤J¦Ò¼{¡A­n¤@­P¡]¨Ò¡Grows(2:12)³£¬Oa ¥HªÅ¥Õªí¥Ü¡^,¦ý­Y¦³¤£¦P¡A¥X²{¤Ï¶À»Px´£¥Ü¡A¦prows(13:18)¦³¤£¦PªÌ

a5007185

¦^´_ 1# s7659109

§Ú¥ý´y­z¤@¤U§Aªº°ÝÃD¡A
§A¬Ý¤@¤U¬O¤£¬O³o¼Ë¡H

¥ý¥HAÄæ»PBÄ檺¸ê°T°µ¤À²Õ¡A
­Y²Õ¤ºDÄæ¥X²{¨âºØ¥H¤Wªº¸ê°T´NHighlight¡H