How to list cells from two sheets that has different values in theirneighboring column?

[AA Arens]

Hi,

I have three sheets.

In the last sheet 3 I want to list numbers from the other sheets 1+2 that has a different value in its neighboring column.
In sheet 3 will be mentioned these numbers plus the values in the two next columns.

What is the formula?


Sheet 1

15 tree
26 home
12 car


Sheet 2

15 tree
26 office
12 car


Result Sheet 3

26 home office


Regards, Bart
Excel 2013

 

[Claus Busch]

Hi Bart,

Am Fri, 4 Oct 2013 21:04:20 -0700 (PDT) schrieb AA Arens:

Result Sheet 3

26 home office

if these values are unique then try in Sheet3 A1:
=INDEX(Sheet1!A:A,SMALL(IF(COUNTIF(Sheet1!B$1:B$100,Sheet2!B$1:B$100)=0,ROW($1:$100)),ROW()))
and enter the array formula with CTRL+Shift+Enter and drag down till you
get 0
In B1:
=VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0)
and in C1:
=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)

If the values are not unique check if the 2 columns are the same:
In Sheet3 A1:
=IF(Sheet1!A2&Sheet1!B2=Sheet2!A2&Sheet2!B2,"",Sheet1!A2)
and copy down


Regards
Claus B.

 

[AA Arens]

Hi,



I have three sheets.



In the last sheet 3 I want to list numbers from the other sheets 1+2 that has a different value in its neighboring column.

In sheet 3 will be mentioned these numbers plus the values in the two next columns.



What is the formula?





Sheet 1



15 tree

26 home

12 car





Sheet 2



15 tree

26 office

12 car





Result Sheet 3



26 home office





Regards, Bart

Excel 2013

Thanks Claus.

The numeric value in the first column of each sheet is unique, but it may appear in each sheet not in the same row. I predict I need to use the VLOOK statement.


Bart
Bart

 

[Claus Busch]

Hi Bart,

Am Sat, 5 Oct 2013 03:35:02 -0700 (PDT) schrieb AA Arens:

The numeric value in the first column of each sheet is unique, but it may appear in each sheet not in the same row. I predict I need to use the VLOOK statement.

then you have to look in Sheet2:
=INDEX(Sheet2!A:A,SMALL(IF(COUNTIF(Sheet1!$B$2:$B$101,Sheet2!$B$1:$B$100)=0,ROW($1:$100)),ROW(A1)))
and enter with CTRL+Shift+Enter.
The other values you get with VLOOKUP


Regards
Claus B.

 

[AA Arens]

Hi,



I have three sheets.



In the last sheet 3 I want to list numbers from the other sheets 1+2 that has a different value in its neighboring column.

In sheet 3 will be mentioned these numbers plus the values in the two next columns.



What is the formula?





Sheet 1



15 tree

26 home

12 car





Sheet 2



15 tree

26 office

12 car





Result Sheet 3



26 home office





Regards, Bart

Excel 2013

The changes are not triggered if I change a text value in both of the sheets 1 and 2.

To be more precise, I have 3 sheets, master (current, #1), raw (new data, #2) and mod (displays changes, #3)

The data in the first sheets are from B3 - B2002 for the numeric value and in C for the text ("car" etc).

Here is the formula I use in sheet mod (#3) with the numeric value in column J and the old and new data in K and L:

=INDEX(Raw!B:B,SMALL(IF(COUNTIF(Master!$C$3:$C$2002,Raw!$C$3:$C$2002)=0,ROW($3:$502)),ROW(J3)))

This should be the correct if I follow your solution. I know how to add in other rows using ctrl/shift/enter creating { }.

I changes text values in both sheets 1 and 2 but the change is not indicated in sheet 3.

Bart

 

[Claus Busch]

Hi,

Am Sat, 5 Oct 2013 04:47:16 -0700 (PDT) schrieb AA Arens:

The changes are not triggered if I change a text value in both of the sheets 1 and 2.

To be more precise, I have 3 sheets, master (current, #1), raw (new data, #2) and mod (displays changes, #3)

please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "LookUpTest"

In the first row your formula must have ROW(A1)


Regards
Claus B.

 

[AA Arens]

Hi,



I have three sheets.



In the last sheet 3 I want to list numbers from the other sheets 1+2 thathas a different value in its neighboring column.

In sheet 3 will be mentioned these numbers plus the values in the two next columns.



What is the formula?





Sheet 1



15 tree

26 home

12 car





Sheet 2



15 tree

26 office

12 car





Result Sheet 3



26 home office





Regards, Bart

Excel 2013

I used the same formula as in your spreadsheet, but changes are not visible.. The two sheets 1 and 2 has up to 2000 rows of data and the CPU value goesup to above 50%, so I abandoned the index solution and choosed the second soluton, whch is working fine. Thanks Claus.

 

[AA Arens]

I used the same formula as in your spreadsheet, but changes are not visible. The two sheets 1 and 2 has up to 2000 rows of data and the CPU value goes up to above 50%, so I abandoned the index solution and choose the secondsolution, which is working fine. Thanks Claus.

If A value from the first column from one of the sheets does not exist, I do get the values of sheet one and two with one of them an N/A error, while the other mention its value ("car" etc.). How to overcome this?

Bart

 

[Claus Busch]

Hi Bart,

Am Sun, 6 Oct 2013 05:41:05 -0700 (PDT) schrieb AA Arens:

If A value from the first column from one of the sheets does not exist, I do get the values of sheet one and two with one of them an N/A error, while the other mention its value ("car" etc.). How to overcome this?

do it with VBA:

Sub Test()
Dim LRow1 As Long
Dim LRow2 As Long
Dim rngC As Range
Dim c As Range
Dim i As Long

LRow1 = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
LRow2 = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
i = 1
Application.ScreenUpdating = False
For Each rngC In Sheets("Sheet1").Range("B3:B" & LRow1)
Set c = Sheets("Sheet2").Range("B3:B" & LRow2) _
.Find(rngC, LookIn:=xlValues)
If Not c Is Nothing Then
If rngC.Offset(, 1) <> c.Offset(, 1) Then
With Sheets("Sheet3")
.Cells(i, 1) = rngC
.Cells(i, 2) = rngC.Offset(, 1)
.Cells(i, 3) = c.Offset(, 1)
End With
i = i + 1
End If
End If
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus B.