0

I need a way to compare two spreadsheets in a workbook, find the differences, then write those differences on the spreadsheet in sequential order in the same column below the existing numbers.

Sheet1 has participating dealer numbers. All dealer numbers are listed in Column C beginning in cell 5. All dealers participating fill in downward. (note: these dealers are listed in a District Order so some of the cells will have the word TOTAL in it instead of a dealer number) Column B hosts the District Letter. I also need to carry over this letter as well.

"Sheet1":

A    207475   
A    207775  
B    TOTAL   

In Sheet2 I have listed the complete list of Dealers to compare against.

"Sheet2":

A    206600   
A    207475  
B    206624 

I would like for my missing dealers to show below a section I have set up as missing dealers like the below example: (ignoring the District Totals) (yet capturing the District letter in Column B as well)

MISSING DLRS.

A    207775   
A    206600  
B    206624 
1
  • I am currently using: =IF(ISERROR(MATCH(Sheet1!B1,'Accord 2DR 4C'!$C$8:$C$145,0)),Sheet1!B1,"") Placed in a column at the far right of all my other data. It brings results to the right and leaves spaces between the results. I need something placed in the same column C without any spaces between the results. Apr 1, 2014 at 18:23

1 Answer 1

1

This is one of those problems that should really be solved by using a database, since excel isn't terribly well suited for it. BUT, excel can do anything, so here's how I would solve it using nothing but cell formulas.

  1. On your second sheet in a column (hidden if you prefer) BEFORE your column of dealers, add a countif formula to determine if the dealer is in Sheet1 or not. Here I have wrapped the countif in a IF formula, and if the countif returns a 0 (the dealer was not found), I have it spit out the ROW I am currently on... this will be important in the next step. Assuming your list of dealers on sheet2 is also in "C" starting at row 5 IF(COUNTIF(Sheet1!C:C,sheet2!C5)=0, ROW(),"")
  2. In a column directly after that last one we just created we are going to use the RANK formula to number the dealer rows from 1 to whatever where the dealer was not matched on Sheet1 (this is why we needed the ROW()). =RANK(A5,$A$5:$A$500,1) (here I assumed that Step 1 column was 'A'
  3. Now we have all the dealers that are missing ranked from top to bottom with consecutive numbers. In a new sheet, or wherever you want your list of Dealers not found in Sheet1, make a column and number the rows 1 through however many missing dealers there could be. I will assume Sheet3, Column A, starting at Row 1
  4. In the next column over do a vlookup function to search for the first missing dealer with =Vlookup(A1,Sheet2$A$5:$A$500,3, false) and copy that function down all the rows.

It's a little complicated, and an unorthodox use of both ROW() and RANK(), but it keeps you out of a databases, VBA, or worse yet... manual.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .