Excel -help!

2009-03-22 4:20 pm
I have two set of numbers in two reports to sort it out, of which, some are the same numbers, some may be single only, how can i sort out the numbers which are not both exist in these reports?

e.g. report A: 1, 2, 3 , 4, 5,.......97, 98, 99, 100..etc.
report B: 1, 3, 4,.......97, 99, 100...etc.

i.e. ' 2, 98,' are not exist in both reports.

I have heard there is a 'parallgram' in Excel function can help me,

Please teach me, with many thanks!!

回答 (2)

2009-03-25 1:58 am
✔ 最佳答案
First of all, I have no idea of 'Parallgram' from my limited knowledge. But I can show you the method that I know. If you got two sheets with data in column A1 to A6 respectively and wish to compare the difference, simply follow the steps :
1. in sheet 1, B2=IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$6,FALSE))=TRUE, "missing in sheet A", "appeared in two records")
do the same in sheet 2 and change the sheet no into sheet1 in the formula.
2. use the Autofilter function to sort out the missing data.
3. OK
4. please test before you apply it to your work.



2009-03-30 13:45:05 補充:
Assuming that the data is from 1 to 1000, and all the data are in numeric format.
In record A, insert a column as B and type the following formula in B1

2009-03-30 13:45:39 補充:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("A1:A"&MAX($A$1:$A$1000))),$A$1:$A$1000,0)),ROW(INDIRECT("A1:A"&MAX($A$1:$A$1000)))," "),ROW())
press “ctrl+shift+enter”
drag down to B1000

2009-03-30 13:45:50 補充:
You will find that the missing numbers will appear. Do the same in Record B. After that, copy the filtered record into record B after the end of column B to form a master list that not appear in both Record A and Record B.
OK
2009-03-28 4:27 pm
Thanks, may be i presented in a wrong method, it can,t sort it out.

Regards.


收錄日期: 2021-04-13 17:58:52
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20090322000051KK00319

檢視 Wayback Machine 備份