Home 
Search 
Today's Posts 
#1




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. BD is different than that in cols. EG. If none of the data is different, then it should insert a blank (""). Thanks for your help.  tb 
#2




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. BD is different than that in cols. EG. 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




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. BD is different than that in cols. EG. 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




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




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




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




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




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


Similar Threads  
Thread  Forum  
Formula that checks balances  Excel Programming  
need help with creating a formula which checks more than 40 cells  Excel Worksheet Functions  
Formula which checks multiple criteria before counting  Excel Discussion (Misc queries)  
Formula that checks several criteria before returing the value???  Excel Discussion (Misc queries)  
Formula/function that checks for a set of values.  Excel Worksheet Functions 