I have a spreadsheet with the following columns:
* Col. A: Part numbers
* Cols. B, C, and D: Current data that pertains to part numbers in col.
A
* Cols. E, F, and G: Proposed new data that pertains to said part
numbers. Such data is obtained by formulas linked to other
spreadsheets.

What I would like to do is have a formula in col. H that inserts an "X"
if any data in cols. B-D is different than that in cols. E-G. If none
of the data is different, then it should insert a blank ("").

Thanks for your help.
--
tb

Hi,

Am Sat, 26 Nov 2016 21:15:18 -0000 (UTC) schrieb tb:

I have a spreadsheet with the following columns:
* Col. A: Part numbers
* Cols. B, C, and D: Current data that pertains to part numbers in col.
A
* Cols. E, F, and G: Proposed new data that pertains to said part
numbers. Such data is obtained by formulas linked to other
spreadsheets.

What I would like to do is have a formula in col. H that inserts an "X"
if any data in cols. B-D is different than that in cols. E-G. If none
of the data is different, then it should insert a blank ("").

in H1 try:
=IF(ISERROR(MATCH(G1&H1&I1,$B$1:$B$200&$C$1:$C$200 &$D$1:$D$200,0)=ROW()),"x","")
and insert the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

On 11/27/2016 at 1:58:43 AM Claus Busch wrote:

Hi,

Am Sat, 26 Nov 2016 21:15:18 -0000 (UTC) schrieb tb:

I have a spreadsheet with the following columns:
* Col. A: Part numbers
* Cols. B, C, and D: Current data that pertains to part numbers in
col. A
* Cols. E, F, and G: Proposed new data that pertains to said part
numbers. Such data is obtained by formulas linked to other
spreadsheets.

What I would like to do is have a formula in col. H that inserts an
"X" if any data in cols. B-D is different than that in cols. E-G.
If none of the data is different, then it should insert a blank
("").

in H1 try:
=IF(ISERROR(MATCH(G1&H1&I1,$B$1:$B$200&$C$1:$C$200 &$D$1:$D$200,0)=ROW(
)),"x","") and insert the formula with CTRL+Shift+Enter


Regards
Claus B.

Thanks, Claus.

A confession... I did not give all the details involved in this issue
becasue I thought that it would not make much of a difference. I was
wrong.

In the real spreadsheet, there are many more columns beside B, C, and D
(and the corresponding E, F, and G).

Is there any way to optimize your formula based on this new piece of
information? Including every column used in the MATCH function will
take me forever and create a very long formula.

Thanks.
--
tb

Hi,

Am Mon, 28 Nov 2016 21:24:55 +0000 (UTC) schrieb tb:

In the real spreadsheet, there are many more columns beside B, C, and D
(and the corresponding E, F, and G).

Is there any way to optimize your formula based on this new piece of
information? Including every column used in the MATCH function will
take me forever and create a very long formula.

if you have many more columns to compare and also a lot of data an array
formula is very slow. You better use VBA.
Please explain your table layout that we can help you.


Regards
Claus B.
--
Windows10
Office 2016

On 11/28/2016 at 5:03:06 PM Claus Busch wrote:

Hi,

Am Mon, 28 Nov 2016 21:24:55 +0000 (UTC) schrieb tb:

In the real spreadsheet, there are many more columns beside B, C,
and D (and the corresponding E, F, and G).

Is there any way to optimize your formula based on this new piece of
information? Including every column used in the MATCH function will
take me forever and create a very long formula.

if you have many more columns to compare and also a lot of data an
array formula is very slow. You better use VBA.
Please explain your table layout that we can help you.


Regards
Claus B.

The columns for current data go from B to J. Columns for new data go
from K to T. Column A holds the part number codes.

The problem is with the number of rows... As we keep on adding new
part numbers, the number of rows keeps on getting bigger. Right now
there are over 15,000 rows.

--
tb

Hi,

Am Tue, 29 Nov 2016 18:48:19 +0000 (UTC) schrieb tb:

The columns for current data go from B to J. Columns for new data go
from K to T. Column A holds the part number codes.

The problem is with the number of rows... As we keep on adding new
part numbers, the number of rows keeps on getting bigger. Right now
there are over 15,000 rows.

with 15000 rows or more a macro is also slow.
You better go another way and only mark the correct data. That is easy
with the advanced filter. But you need headers in both tables.
Then try:

Sub CompareData()
Dim LRowC As Long, LRowD As Long

With ActiveSheet
LRowC = .Cells(.Rows.Count, "A").End(xlUp).Row
LRowD = .Cells(.Rows.Count, "K").End(xlUp).Row

.Range("K1:T" & LRowD).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
.Range("A1:J" & LRowC), Unique:=False

.Range("U1:U" & LRowD).SpecialCells(xlCellTypeVisible) = "OK"
.ShowAllData
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

On 11/29/2016 at 2:06:58 PM Claus Busch wrote:

Hi,

Am Tue, 29 Nov 2016 18:48:19 +0000 (UTC) schrieb tb:

The columns for current data go from B to J. Columns for new data
go from K to T. Column A holds the part number codes.

The problem is with the number of rows... As we keep on adding new
part numbers, the number of rows keeps on getting bigger. Right now
there are over 15,000 rows.

with 15000 rows or more a macro is also slow.
You better go another way and only mark the correct data. That is easy
with the advanced filter. But you need headers in both tables.
Then try:

Sub CompareData()
Dim LRowC As Long, LRowD As Long

With ActiveSheet
LRowC = .Cells(.Rows.Count, "A").End(xlUp).Row
LRowD = .Cells(.Rows.Count, "K").End(xlUp).Row

.Range("K1:T" & LRowD).AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _ .Range("A1:J" & LRowC), Unique:=False

.Range("U1:U" & LRowD).SpecialCells(xlCellTypeVisible) = "OK"
.ShowAllData
End With
End Sub


Regards
Claus B.

Thanks, Claus!
First I will be searching for an online tutorial about Excel macros, as
I don't know how to implement them.
--
tb

Hi,

Am Tue, 29 Nov 2016 21:39:07 +0000 (UTC) schrieb tb:

First I will be searching for an online tutorial about Excel macros, as
I don't know how to implement them.

open Excel = Alt+F11 = Insert = Standard Module and paste the code
into that module.


Regards
Claus B.
--
Windows10
Office 2016