[發問] 兩工作表比對 新增修改

li_hsien

這問題讓我有點頭大@@

整體作業流程是這樣的

我有2份EXCEL(X,Y)，去除掉重複值合成1份EXCEL(Z)
(X,Y->Z)

格式欄位都一樣

目前我是用外部資料的方式

每次打開Z都會更新X,Y的資料

將X,Y的資料抓進Z的sheet2,sheet3
(X=Z的sheet2, Y=Z的sheet3)

這麼做是想說我在Z上作業就好了

如果單純把sheet2,sheet3的資料抓進sheet1,刪除重複值就是我要的了


但現在最大問題是
最後產出的sheet1有幾個欄位會讓使用者填寫,但這些欄位是在X,Y的時候不用填寫的
(設定資料連結的時候只設定sheet2,sheet3會做的欄位,sheet1才會填寫的欄位沒有做連結,因為如果都做連結那更新後就會變空的)



這樣的話我每次做一次更新合併
就會讓原本填寫在sheet1上的資料錯亂掉(因為sheet2,sheet3會變動)

EX:
欄位->            A            B               C(在sheet1時才會輸入資料)

在sheet2       000        111     
在sheet3       222        333

>sheet1        000        111         AAA
                         222        333         BBB


<<更新後>>
欄位->            A            B               C(在sheet1時才會輸入資料)

在sheet2       000        111   
                         444        555
在sheet3       222        333

>sheet1        000        111         AAA
                         444        555         BBB   
                         222        333         



表達上有些複雜

不知各位大大有沒有什麼想法可以改善

麻煩了 謝謝  :  )

li_hsien

謝謝大大

當初好像把流程想得太複雜 哈

後來有整理出個方法

目前應該可行

不知有無其他方式

附檔是直接把userA,userB抓進來了
(把外部資料來源省略)

欄底部分的欄位是MATCH後才填寫的

userA,userB不能填

1. Sub match_Click()
   
2.    
   
3.     'Copy userA
   
4.     For i = 2 To Sheets(2).Range("A1").End(xlDown).Row
   
5.         '判別來源有無重複,且MATCH表沒有的(貼最下面)
   
6.         If Application.match(Sheets(2).Range("D" & i), Sheets(2).Range("D:D"), 0) = i And _
   
7.                 IsError(Application.match(Sheets(2).Range("D" & i), Range("D:D"), 0)) = True Then
   
8.             userA_rows = Range("A65536").End(xlUp).Row + 1
   
9.             Sheets(2).Range("A" & i & ":D" & i).Copy Range("A" & userA_rows & ":D" & userA_rows)
   
10.         '判別來源有無重複,且MATCH表上有的(貼在MATCH位置)
    
11.         ElseIf Application.match(Sheets(2).Range("D" & i), Sheets(2).Range("D:D"), 0) = i And _
    
12.                 IsError(Application.match(Sheets(2).Range("D" & i), Range("D:D"), 0)) = False Then
    
13.             site_A = Application.match(Sheets(2).Range("D" & i), Range("D:D"), 0)
    
14.             Sheets(2).Range("A" & i & ":D" & i).Copy Range("A" & site_A & ":D" & site_A)
    
15.         End If
    
16.     Next
    
17. 
    
18.     'Copy userB
    
19.     For j = 2 To Sheets(3).Range("A1").End(xlDown).Row
    
20.         '判別來源有無重複,且MATCH表沒有的(貼最下面)
    
21.         If Application.match(Sheets(3).Range("D" & j), Sheets(3).Range("D:D"), 0) = j And _
    
22.                 IsError(Application.match(Sheets(3).Range("D" & j), Range("D:D"), 0)) = True Then
    
23.             userB_rows = Range("A65536").End(xlUp).Row + 1
    
24.             Sheets(3).Range("A" & j & ":D" & j).Copy Range("A" & userB_rows & ":D" & userB_rows)
    
25.         '判別來源有無重複,且MATCH表有的(貼在MATCH位置)
    
26.         ElseIf Application.match(Sheets(3).Range("D" & j), Sheets(3).Range("D:D"), 0) = j And _
    
27.                 IsError(Application.match(Sheets(3).Range("D" & j), Range("D:D"), 0)) = False Then
    
28.             site_B = Application.match(Sheets(3).Range("D" & j), Range("D:D"), 0)
    
29.             Sheets(3).Range("A" & j & ":D" & j).Copy Range("A" & site_B & ":D" & site_B)
    
30.         End If
    
31.     Next
    
32. 
    
33.     '與userA,userB比對 刪除沒有的
    
34.     For x = 2 To Range("A1").End(xlDown).Row
    
35.         If IsError(Application.match(Range("D" & x), Sheets(2).Range("D:D"), 0)) = True And _
    
36.             IsError(Application.match(Range("D" & x), Sheets(3).Range("D:D"), 0)) = True Then
    
37.             Rows(x).Delete
    
38.         End If
    
39.     Next
    
40. End Sub

複製代碼

match.zip (20.51 KB)

下載次數: 12

2014-1-13 09:07

li_hsien

回復 3# Hsieh

是以ID為主要比對的項目沒錯

把USER A , USER B 彙整成MATCH  A & B
基本上USER A 與 USER B裡面資料若ID一樣
其他欄位也都會一樣
所以取先取後都可以

目前我碰上主要的問題是E,F欄位
是在MATCH後才會填寫的

所以在USER A 或USER B加欄位後再MATCH
原先填在E,F的欄位就不能對齊了

且如果再USER A或USER B有刪除則MATCH裡面的也會被刪除(E,F也會)

EX:

原本的MATCH

A            B            C                   D               E                        F
123       456       2013.01     AAA         TEST123         TEST456

在USER中加入一欄在原先ID AAA的上面
A            B            C                   D      
777       888       2013.02     BBB     
123       456       2013.01     AAA


則MATCH後會變成
A            B            C                   D               E                        F
777       888       2013.02     BBB          TEST123         TEST456
123       456       2013.01     AAA

正確應該為
A            B            C                   D               E                        F
777       888       2013.02     BBB         
123       456       2013.01     AAA         TEST123         TEST456


目前我附檔的程式碼好像可以做到

但不知O不OK

還是另有更好的作法


謝謝大大 :  )

li_hsien

回復 5# Hsieh

請問大大

我不太懂
Application.Transpose(Application.Transpose(A.Offset(, -3).Resize(, 6).Value))
的用意

為什麼要連用兩次Application.Transpose

且我看不出哪一段是比對兩者不同
進行新增或刪除的部分

li_hsien

回復 7# Hsieh

版主意思是儲存格實際上是二維
但字典的形式是一維

所以要先轉成一維放進字典
之後在轉一次變成二維放進儲存格中嗎???

以下是在儲存格呈現的方式
A   B   C   D   E     ->儲存格上這樣是二維??

A->這樣是一維嗎??
B
C
D
E
還是怎麼呈現都無關，只要是儲存格上皆是二維???


謝謝板大的不吝指教

li_hsien

回復 9# Hsieh

早安 哈哈

你好早喔

謝謝板大的說明

li_hsien

回復 5# Hsieh

請問一下板大

原先我是以D欄為主

現行改為
依三個欄位來比對

變成是看A,B,D三個欄位
ABD一起看來判別是一筆

也就是說光單看A或B或D會有重複

必須A,B,D一起看才能夠判定

這樣我必須怎麼修正???

請教一下大大

謝謝 :  )

li_hsien

回復 12# Hsieh

板大 如果我欄位沒有一致
(A,B一樣但和A&B的不一樣)

會變動那該如何表示呢???

因為用Transpose應該是整欄整列吧

li_hsien

回復 14# Hsieh

型號&類別&ID為主要判斷準則

UserA , UserB的欄位都一樣

將兩個工作表抓到matchA&B

藍底為matchA&B才會填的欄位


UserA,UserB抓過去的時候

必須對應欄位
如果UserA沒有的，但matchA&B有的欄位(EX:使用者)則留空

因為和matchA&B欄位不一樣
所以不知該如何用原先的方式對應


附檔裡的matchA&B為該產出的結果

麻煩幫我看看 謝謝 : )

match0117.zip (20.53 KB)

下載次數: 8

2014-1-17 11:00

li_hsien

回復 17# Hsieh

[attach]17319[/attach]

MATCH比對方式是對的

不過要COPY的只有藍色前面的部分
If d.exists(mystr) Then A.Resize(, 5) = d(mystr): d.Remove mystr
我把8改成5好像就可以了

不過有一個地方怪怪的

就是MATCH的來源是依UsetA,UserB來的

所以如果UserA,UserB沒有的
MATCH那邊必須整欄都刪掉

也就是說
UserA,UserB有的,Match才需要留著


大大的語法好"新"的感覺XDD

幾乎都沒看過 哈哈

我得研究研究了