返回列表 上一主題 發帖

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

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

[版主管理留言]
  • GBKEE(2014/1/11 13:28): 請附檔說明

這問題讓我有點頭大@@

整體作業流程是這樣的

我有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         



表達上有些複雜

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

麻煩了 謝謝  :  )
用功到世界末日那一天∼∼∼

謝謝大大

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

後來有整理出個方法

目前應該可行

不知有無其他方式

附檔是直接把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.     'Copy userB
  18.     For j = 2 To Sheets(3).Range("A1").End(xlDown).Row
  19.         '判別來源有無重複,且MATCH表沒有的(貼最下面)
  20.         If Application.match(Sheets(3).Range("D" & j), Sheets(3).Range("D:D"), 0) = j And _
  21.                 IsError(Application.match(Sheets(3).Range("D" & j), Range("D:D"), 0)) = True Then
  22.             userB_rows = Range("A65536").End(xlUp).Row + 1
  23.             Sheets(3).Range("A" & j & ":D" & j).Copy Range("A" & userB_rows & ":D" & userB_rows)
  24.         '判別來源有無重複,且MATCH表有的(貼在MATCH位置)
  25.         ElseIf Application.match(Sheets(3).Range("D" & j), Sheets(3).Range("D:D"), 0) = j And _
  26.                 IsError(Application.match(Sheets(3).Range("D" & j), Range("D:D"), 0)) = False Then
  27.             site_B = Application.match(Sheets(3).Range("D" & j), Range("D:D"), 0)
  28.             Sheets(3).Range("A" & j & ":D" & j).Copy Range("A" & site_B & ":D" & site_B)
  29.         End If
  30.     Next

  31.     '與userA,userB比對 刪除沒有的
  32.     For x = 2 To Range("A1").End(xlDown).Row
  33.         If IsError(Application.match(Range("D" & x), Sheets(2).Range("D:D"), 0)) = True And _
  34.             IsError(Application.match(Range("D" & x), Sheets(3).Range("D:D"), 0)) = True Then
  35.             Rows(x).Delete
  36.         End If
  37.     Next
  38. End Sub
複製代碼
match.zip (20.51 KB)
用功到世界末日那一天∼∼∼

TOP

回復 2# li_hsien

是比對重複ID嗎?請說明重複的定義
要記錄的是每個ID的最後出現的資料,還是最先出現的資料?
試試紀錄最後出現的ID資料
  1. Sub ex()
  2. Dim Sh As Worksheet, A As Range
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. For Each Sh In Sheets(Array("User A", "User B"))
  5. With Sh
  6.   For Each A In .Range(.[D1], .[D1].End(xlDown))
  7.   ar = Application.Transpose(Application.Transpose(A.Offset(, -3).Resize(, 4)))
  8.   mystr = Join(ar, Chr(10))
  9.      dic(A.Value) = Split(mystr, Chr(10))
  10.   Next
  11. End With
  12. Next
  13. Sheets("Match A & B").[A1].Resize(dic.Count, 4) = Application.Transpose(Application.Transpose(dic.items))
  14. End Sub
複製代碼
學海無涯_不恥下問

TOP

回復 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

還是另有更好的作法


謝謝大大 :  )
用功到世界末日那一天∼∼∼

TOP

  1. Sub ex()
  2. Dim A As Range, Sh As Worksheet
  3. Set d = CreateObject("Scripting.Dictionary")
  4. For Each Sh In Sheets(Array("User A", "User B", "Match A & B"))
  5. With Sh
  6.    For Each A In .Range(.[D1], .[D1].End(xlDown))
  7.       d(A.Value) = Application.Transpose(Application.Transpose(A.Offset(, -3).Resize(, 6).Value))
  8.    Next
  9. End With
  10. Next
  11. Sheets("Match A & B").[A1].Resize(d.Count, 6) = Application.Transpose(Application.Transpose(d.items))
  12. End Sub
複製代碼
回復 4# li_hsien
學海無涯_不恥下問

TOP

回復 5# Hsieh

請問大大

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

為什麼要連用兩次Application.Transpose

且我看不出哪一段是比對兩者不同
進行新增或刪除的部分
用功到世界末日那一天∼∼∼

TOP

回復 6# li_hsien

這是利用字典物件的不重複索引特性,將所有的資料寫入字典內容
因為儲存格範圍被視為二維陣列,但因為單列的資料用transpose函數做二次轉置,就會變成一維陣列
將這些一維陣列存入字典內容,字典內容就是多個一維陣列所組成,再經過二次轉置就可成為真正的二維陣列
因為你的match工作表需保留原內容,在最後才讀入就不會被A,B所覆蓋
學海無涯_不恥下問

TOP

回復 7# Hsieh

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

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

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

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


謝謝板大的不吝指教
用功到世界末日那一天∼∼∼

TOP

回復 8# li_hsien


    一維陣列是指單列資料的集合,是橫向的。
其陣列索引樣式為arr(0)
二維陣列則為多列,所有資料分佈成面。
其陣列索引樣式為arr(0,0)
因為儲存格範圍,EXCEL會視為二維陣列
所以如果儲存格範圍為單列,如[A1:F1]
如果一次轉置,會變成直向陣列,是二維陣列
再一次轉置後,變成橫向,就是一維陣列。
學海無涯_不恥下問

TOP

回復 9# Hsieh

早安 哈哈

你好早喔

謝謝板大的說明
用功到世界末日那一天∼∼∼

TOP

        靜思自在 : 修行要繫緣修心,藉事練心,隨處養心。
返回列表 上一主題