作者: BusMan29 時間: 2016-2-13 13:11 標題: Microsoft Excel - Filter
Suppose File A has 10 rows at column C. After filtering, only Row 1, 2, 6, 7, 9 are shown. Then, I need to copy the values of a column from File B to File A. At File B, there are 5 rows which has values and need to copy to File A. But, it is failed. Only Row 1, 2 have value while Row 6, 7, 9 have no value. Please advised if there is any way to copy those 5 rows to the filtered cells directly. Thank you.
作者: BusMan29 時間: 2016-2-17 21:47
I have done 2 testing.
Testing 1: Success
After filtering, Row 1, 2, 6, 7, 9 has the same value at Column C.
Then, I highlight these values at Column D and copy to another file.
All values are copied successfully.
Testing 2: Failed
I copy 5 values at a column of 5 rows to the filtered cells.
I found that the values are copied to the first 5 rows.
It seems that I cannot copy to Row 1, 2, 6, 7, 9.
Is there any way to copied the values to those filtered rows? Thank you.
作者: kkeennken 時間: 2016-2-17 22:15
本帖最後由 kkeennken 於 2016-2-17 22:19 編輯
回覆 2# BusMan29
No.
作者: bongbong3481 時間: 2016-2-17 23:24 標題: 標題
回覆 2# BusMan29
我會寫個vbs 去處理(我相信用excel 公式去做有d 難) ，首次取消filter, 然後一格一格咁由b file copy and paste 去A file 1 2 6 7 9, 最後再filter 番
作者: simontang 時間: 2016-2-18 19:29
you have such condition to filter, why don't use vlookup?
作者: BusMan29 時間: 2016-2-28 20:45
you have such condition to filter, why don't use vlookup?
simontang 發表於 18-2-2016 19:29
I do not know vlookup.
I think I have to Google.
HaHa.
作者: happy_gemini 時間: 2016-2-29 16:21
you have such condition to filter, why don't use vlookup?
simontang 發表於 2016-2-18 19:29
Vlookup might not help in this case.
作者: bongbong3481 時間: 2016-2-29 20:51
回覆 7# happy_gemini
我覺得樓主既情況， 寫ＶＢ ＣＯＤＥ好似快過寫條公式
作者: BusMan29 時間: 2016-3-5 08:05
回覆 happy_gemini
我覺得樓主既情況， 寫ＶＢ ＣＯＤＥ好似快過寫條公式 ...
bongbong3481 發表於 29-2-2016 20:51
I do not know programming.
作者: bongbong3481 時間: 2016-3-5 12:06
回覆 9# BusMan29
好奇問一問， 咁你個問題解決左未？ 如果用公式整， 介唔介意放條公式出黎參考下
作者: happy_gemini 時間: 2016-3-5 23:13
回覆 happy_gemini
我覺得樓主既情況， 寫ＶＢ ＣＯＤＥ好似快過寫條公式 ...
bongbong3481 發表於 2016-2-29 20:51
我意思是指vlookup在這種情況應該做不到樓主的要求.
理論上filter後, column C所有value相同, vlookup不可能配對到file B的相應rows. (除非file A在filter column C後有另一column儲存unique values對應file B.
作者: bongbong3481 時間: 2016-3-6 11:48
回覆 11# happy_gemini
請問一下， 如果唔FILTER， 用vlookup可否解決到？ （其實我唔識用vlookup）
作者: happy_gemini 時間: 2016-3-6 15:20
回覆 happy_gemini
請問一下， 如果唔FILTER， 用vlookup可否解決到？ （其實我唔識用vlookup） ...
bongbong3481 發表於 2016-3-6 11:48
要有實例先答到你.
作者: bongbong3481 時間: 2016-3-6 18:51
回覆 13# happy_gemini
如果類似以下情況，A 及E Col 有相同字母“A”， F Col既數字，一個一個咁搬去有相同“A”字既 C Col， 有冇辦法用公式做到？
圖片附件: img.jpg (2016-3-6 18:47, 32.37 KB) / 下載次數 45https://h2.hkepc.com/forum/attachment.php?aid=1870820&k=5e5dbdbfbfceff603f30845a9c5a5359&t=1782194590&sid=tbv4SErm0
作者: happy_gemini 時間: 2016-3-7 16:30
回覆 happy_gemini
如果類似以下情況，A 及E Col 有相同字母“A”， F Col既數字，一個一個咁搬去有相 ...
bongbong3481 發表於 2016-3-6 18:51
就這種情況, 其實先以Col A做一次排序, 再copy Col F過去就很簡單.
作者: bongbong3481 時間: 2016-3-7 20:00
回覆 15# happy_gemini
簡單成咁啊, 西厲！
作者: simontang 時間: 2016-3-7 20:54
不如樓主 upload 個 example file來看看吧...