Remember Me?

#1
November 26th 16, 10:15 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 73
Formula That Checks For Changes

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

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 ("").

--
tb

#2
November 27th 16, 08:58 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,555
Formula That Checks For Changes

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

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
#3
November 28th 16, 10:24 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 73
Formula That Checks For Changes

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

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
#4
November 29th 16, 12:03 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,555
Formula That Checks For Changes

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.

Regards
Claus B.
--
Windows10
Office 2016
#5
November 29th 16, 07:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 73
Formula That Checks For Changes

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.

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

#6
November 29th 16, 09:06 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,555
Formula That Checks For Changes

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
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
#7
November 29th 16, 10:39 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 73
Formula That Checks For Changes

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
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

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
#8
November 30th 16, 06:58 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,555
Formula That Checks For Changes

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post xp Excel Programming 3 May 4th 10 06:53 AM Faizan Excel Worksheet Functions 1 January 3rd 10 10:13 AM JHolmes Excel Discussion (Misc queries) 10 February 22nd 08 04:57 PM Apinun Excel Discussion (Misc queries) 4 September 19th 06 10:17 AM Memnok Excel Worksheet Functions 8 March 24th 06 06:59 PM

All times are GMT +1. The time now is 04:19 AM.