0

Goal:
Retrieve data row if there are NOT any matching value between 2 sheets from a excel file 2007

Problem:
I don't know how to do it.

These sheets has same column that is 4 column but they have different value in every row. All these four columns are unique.

These sheets has same data but having different quantity of rows.

// Fullmetalboy

A simplified explanation is:

Sheet 1
Column
a   b   c  d
------------
f  w    w  w
w  e    e  e
p  p    p  p 
w  w    w  w

Sheet 2
Column
a   b   c  d
------------
f  w    w  w
w  e    e  e
w  w    w  w
c  c    d  d


The row data that do not exist in the counterpart's sheet is:
p  p    p  p 
c  c    d  d
HelloWorld1
  • 13,688
  • 28
  • 82
  • 145
  • Does the two excel sheets have the same number of rows ? Should row1 of the first excel sheet be compared to row1 to the other excel sheet ? – VirtualTroll Dec 02 '11 at 19:53
  • You want to loop through rows on the first sheet looking for matching rows on the second sheet with the same values in column 4? What do you mean by return record in this context? :-) – William Stearns Dec 02 '11 at 19:54
  • Amine: they dont have the same number. Second question is no. William: Yes to your question but should be happening on both side and it should be all columns not the fourth column. If there is row that is missing in the second sheet that value should display. If a row from second sheet do not find in the first sheet the value should also be displayed. Please tell me if you need more instruction! – HelloWorld1 Dec 02 '11 at 20:13
  • So basically you want to have the sheets synchronize the rows based on the uniqueness of the first 4 columns. If sheet1 has a new row that doesn't exist in sheet2, you want it replicated in sheet2. And then the reverse logic as well. It's possible, but doesn't feel like it should be happening in a spreadsheet. Button activated macro would probably work best, although you could hook it to an event, but might be a bit slow on older machines. – William Stearns Dec 02 '11 at 20:30
  • I have updated the text information and please inform me if you need more explaination! – HelloWorld1 Dec 02 '11 at 22:05
  • May be of interest: http://stackoverflow.com/questions/3051610/compare-two-excel-sheets/3056429#3056429 or http://stackoverflow.com/questions/4379213/excel-macro-match-and-lineup-rows/4381166#4381166 – Fionnuala Dec 02 '11 at 22:09
  • 1
    Insert a fith column in each sheet which joins the values from the other 4 columns (along with a suitable separating character). Then just use vlookup() or similar between the two joined columns. – Tim Williams Dec 02 '11 at 23:37

1 Answers1

0

In outline: add a column (say A) to indicate whether Sheet1 or Sheet2 while copying Sheet1 and appending Sheet2 to it in a new sheet starting at A1. Delete all 'header' rows bar Row1. Sort columns B/C/D/E. Use conditional formatting formula =OR(AND(B2=B3,C2=C3,D2=D3,E2=E3),AND(B3=B4,C3=C4,D3=D4,E3=E4)) in B3, select a fill colour, apply to $B$3:$E${last occupied row}. Filter on colour and delete visible.

pnuts
  • 58,317
  • 11
  • 87
  • 139