A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Formula That Checks For Changes



 
 
Thread Tools Display Modes
  #1  
Old November 26th 16, 09:15 PM posted to microsoft.public.excel.worksheet.functions
tb
external usenet poster
 
Posts: 60
Default 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
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
Ads
  #2  
Old November 27th 16, 07:58 AM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,448
Default 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
> 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
  #3  
Old November 28th 16, 09:24 PM posted to microsoft.public.excel.worksheet.functions
tb
external usenet poster
 
Posts: 60
Default 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
> > 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
  #4  
Old November 28th 16, 11:03 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,448
Default 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.
Please explain your table layout that we can help you.


Regards
Claus B.
--
Windows10
Office 2016
  #5  
Old November 29th 16, 06:48 PM posted to microsoft.public.excel.worksheet.functions
tb
external usenet poster
 
Posts: 60
Default 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.
> 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
  #6  
Old November 29th 16, 08:06 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,448
Default 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
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
  #7  
Old November 29th 16, 09:39 PM posted to microsoft.public.excel.worksheet.functions
tb
external usenet poster
 
Posts: 60
Default 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
> 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
  #8  
Old November 30th 16, 05:58 AM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,448
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula that checks balances xp Excel Programming 3 May 4th 10 06:53 AM
need help with creating a formula which checks more than 40 cells Faizan Excel Worksheet Functions 1 January 3rd 10 09:13 AM
Formula which checks multiple criteria before counting JHolmes Excel Discussion (Misc queries) 10 February 22nd 08 03:57 PM
Formula that checks several criteria before returing the value??? Apinun Excel Discussion (Misc queries) 4 September 19th 06 10:17 AM
Formula/function that checks for a set of values. Memnok Excel Worksheet Functions 8 March 24th 06 05:59 PM


All times are GMT +1. The time now is 06:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.