iT邦幫忙

0

excel比對多欄資料問題

https://ithelp.ithome.com.tw/upload/images/20180629/20109231bpSkuhBqyj.png

https://ithelp.ithome.com.tw/upload/images/20180630/20109231X4q5CW4JSZ.png

請問AA欄該如何設定公式
欲比對【比對資料】活頁的P欄、U欄、Z欄與【訂單來源】活頁的AP欄、AY欄、BI欄
三個欄位資料完全相符時,帶出【訂單來源】活頁的A欄訂單NO.到AA欄

如果比對無不符資料時,則帶出「查無對應資料」字樣

PS.資料共約3千多~1萬多筆,故此為範例資料
另AA欄帶出排序希望為尾字編號由小至大排序

以上跪求大師幫忙~~~~~~~~~~

測試檔

看更多先前的討論...收起先前的討論...
【訂單來源】活頁 長得怎樣?
@pcw 訂單來源活頁在測試檔內有ㄛ
sorry, 我的mac沒裝office
我質疑 【訂單來源】活頁 的 A欄中 有 尾字編號
@pcw 訂單來源的畫面已上傳,另訂單NO.的編號廠商提供即有,未人工調整過~~~
2
paicheng0111
iT邦高手 1 級 ‧ 2018-07-01 13:50:25
最佳解答

1 - 在【訂單來源】活頁 建立輔助欄位

  1. 在某個未使用的欄位(例如:XX)建立輔助欄位,XX2寫上=AP2&"_"&AY2&"_"&BI2
  2. XX2用填滿控點往下拉。

2 - 在【比對資料】活頁 建立輔助欄位1輔助欄位2

  1. 在某個未使用的欄位(例如:XX)建立輔助欄位1,XX2寫上=P2&"_"&U2&"_"&Z2
  2. XX2用填滿控點往下拉。
  3. 在某個未使用的欄位(例如:XY)建立輔助欄位2,XY2寫上=COUNTIF($XX$2:$XX2,XX2)
  4. XY2用填滿控點往下拉。

3 - 在【比對資料】活頁 建立陣列公式

  1. AA2寫上=IFERROR(INDEX(訂單來源!$A:$A,LARGE(IF(訂單來源!XX:XX=XX2,ROW(訂單來源!XX:XX),""),XY2)),"查無對應資料")
  2. 按下Ctrl+Shift+Enter

@pcw 你真的好厲害喔!!!又解決我的一個大問題了~~~~非常感謝你喔!!!

才想到,你已經做完了,漂亮。
只不過有點疑問,不知道 相同SP與品名,但出現不同帳款,這樣是不是會有問題?

@msmplayv121068
這裡的高手多的是,我只是比較有空而已。

@richardsuma
建立輔助欄位的目的(除了輔助欄位2),就是把SP_品名_帳款當作key。
所以應該不會有問題。

0
richardsuma
iT邦大師 1 級 ‧ 2018-06-30 00:12:15

參考看看:
https://ithelp.ithome.com.tw/questions/10164231

抱歉,你資料的三個欄位值有重複,無法達到你想要的答案?

@richardsuma 因為三個欄位資料都會有重複,唯一值為【訂單來源】活頁的A欄訂單NO.不會重複,所以目前是使用人工手動篩選的方式去比對後,再手動複製貼上對應的訂單NO.,非常耗時~~~~

0
小魚
iT邦大師 1 級 ‧ 2018-06-30 00:52:23

如果P1比對AP1, P2比對AP2 可以使用If
如果P1比對所有AP裡面有沒有,可以考慮使用VLOOKUP

看更多先前的回應...收起先前的回應...

@小魚 因為三個欄位資料由上至下都會有重複,所以可能沒辦法用Vlookup或index+match來比對抓取資料,因為醬都只會抓到由上至下的第一筆訂單NO.~~~

蟹老闆 iT邦大師 1 級 ‧ 2018-06-30 03:21:58 檢舉

https://ithelp.ithome.com.tw/upload/images/20180630/20006149MoJiBnhsPV.jpg
msmplayv121068
一定是哪兒搞錯了,當手工作業時條件一定不止這三個,應該還有其它沒說出來的條件,又或者應該從訂單來源查找比對資料。

小魚 iT邦大師 1 級 ‧ 2018-06-30 07:39:43 檢舉

這樣的資料設計不是很好...

@蟹老闆 因為公司系統很爛,只能導出【比對資料】的excel檔,裡面的資料不會有訂單NO.,而且是所有資料導出無法篩選,然後訂單來源是廠商提供當月需要做帳的檔案,而我需要做的事,就是從2份檔案內除了要比對出相同資料的單號,還要知道廠商這次的檔案有沒有漏掉哪些訂單沒提供給我。所以~~~~~如果有更好的方法可以提供,我就真的是萬分感激不盡了!

@小魚 +1

小魚 iT邦大師 1 級 ‧ 2018-06-30 15:04:08 檢舉

我想問一下,訂單編號同樣的,出現了四個同樣品名,代表那一次買了四個同樣的東西嗎?

@小魚 沒錯!!分成四張單,所以訂單NO.才會有-1、-2、-3、-4以此類推.....

小魚 iT邦大師 1 級 ‧ 2018-06-30 19:39:24 檢舉

這根本判斷不出來吧,
如果VBA應該可以。

@小魚
我覺得用陣列公式可以做到,但要加輔助數列。
可惜手邊只有沒裝office的mac。

@pcw 網路爬文過陣列確實可以做到,但我找不到同時比對三個條件完全符合帶出所需資料的文,所以......

@msmplayv121068
我認為可以把三個欄位的值用文字串接成一個輔助欄位。
另外何不把網路爬文的資料也分享一下,讓大家一起集思廣益。

我要發表回答

立即登入回答