Comparing 2 excel documents question

[Mikelowrey]

Hello,

I have 2 excel documents that I want to compare both have the same information, is just one has been updates where things have been removed. One has 80K entries, and the other has 75K, is there a way for me to see what were those items removed in comparison with the first document?

Thank you!

 

[RileyC]

Match the name/Id of the 80k document to the 75k document, if it throws and error that value was removed.

 

[No_Need_For_Macros]

Mikelowrey, Without knowing how your data arrangement and format, I can only give you a simple example.

Column C has the formula: =IF(A2<>B2,ROW(B2),"ok")
Column D has the formula: =IF(ISERR(SMALL(C$2:C$24,ROW(C2)-1)),"",SMALL(C$2:C$24,ROW(C2)-1))

 

[No_Need_For_Macros]

The above formulas compare Column A and Column B. Then Column D shows which Rows are different.

 

[Mikelowrey]

Mikelowrey, Without knowing how your data arrangement and format, I can only give you a simple example.

Column C has the formula: =IF(A2<>B2,ROW(B2),"ok")
Column D has the formula: =IF(ISERR(SMALL(C$2:C$24,ROW(C2)-1)),"",SMALL(C$2:C$24,ROW(C2)-1))

Thanks, will this work with 2 different documents? they are not sheets. I could try to put 2 sheets in one document

 

[AhoyNC]

You could use an array formula to get a list of removed items.
In the example below the formula in D2 is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed. Change ranges to match your data.

Excel Workbook
	A	B	C	D	E
1	List 1	List 2		# Items Removed	3
2	Item1	Item1		Item3
3	Item2	Item2		Item5
4	Item3	Item4		Item10
5	Item4	Item6
6	Item5	Item7
7	Item6	Item8
8	Item7	Item9
9	Item8
10	Item9
11	Item10
Sheet

 

[Mikelowrey]

You could use an array formula to get a list of removed items.
In the example below the formula in D2 is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed. Change ranges to match your data.

	A	B	C	D	E
Item1	Item1
Item2	Item2
Item3	Item4
Item4	Item6
Item5	Item7
Item6	Item8
Item7	Item9
Item8
Item9
Item10

<colgroup><col style="width:30px; "><col style="width:74px;"><col style="width:71px;"><col style="width:32px;"><col style="width:141px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]List 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]List 2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]# Items Removed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Item3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Item5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Item10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E1 =COUNTA($A$2:$A$11)-COUNTA(B2:B8) D2 {=IF(ROWS($I$2:I2)>$E$1,"",INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(MATCH($A$2:$A$11,$B$2:$B$8,0))=FALSE,ROW($A$2:$A$11)-ROW($A$2)+1),ROWS($I$2:I2))))} <tbody> </tbody>

Formula Array: Produce enclosing{ } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

thank you for this. Is it possible to do it by complete rows? What I am trying to say is, every row has about 15 columns with information from that business.

 

[sandy666]

I could try to put 2 sheets in one document

if you can do that then try FuzzyLookup

 

[No_Need_For_Macros]

With two different documents, often the best answer is to just right-click / copy the new sheets into the old one. If I renamed the sheets to "OldData" and "NewData", I would do my formula work in a third "FormulaSheet", starting in Column A, and it would look like:

Column A of "FormulaSheet": =IF(OldData!A2<>NewData!A2,Row(NewData!A2),"ok")
Column B of "FormulaSheet": =IF(ISERR(SMALL(A$2:A$24,Row(A2)-1)).

Again, this depends on how your arrange your data and both "old" and "new" sheets must have the same format.

 

[AhoyNC]

If you are trying to match whole rows then maybe something like below.
Add a helper column to the larger list (List1 below), which will return a "No" if not in List2.
Formula in A12 (Row Ref:#) is an array formula that must be entered with CTRL-SHIFT-ENTER. - Drag down row as needed.

Formula in B12 is copied down and across as needed.

Excel Workbook
	A	B	C	D	E	F	G	H
1	List 1			Helper		List 2
2	Header1	Header2	Header3	In List 2??		Header1	Header2	Header3
3	Item1	Data1	Data2			Item1	Data1	Data2
4	Item2	Data3	Data4			Item2	Data3	Data4
5	Item3	Data5	Data6	No		Item4	Data7	Data8
6	Item4	Data7	Data700	No		Item5	Data9	Data10
7	Item5	Data9	Data10
8
9
10	# Not in List 2	2
11	Row Ref: #	Header1	Header2	Header3
12	3	Item3	Data5	Data6
13	4	Item4	Data7	Data700
Sheet