Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:13 AM
Formula which checks multiple criteria before counting JHolmes Excel Discussion (Misc queries) 10 February 22nd 08 04: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 06:59 PM


All times are GMT +1. The time now is 08:35 PM.

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

About Us

"It's about Microsoft Excel"