Find Changes to conversions
Posted by Andonny on April 27, 2001 4:38 PM
Hi,
I have another difficult one and I am not too sure how to tackle it.
I require to check if anybody has changed the conversions between Previous and Current. The problem I have is that somebody might add or take off a conversion and then the comparison becomes difficult. Any suggestions on how to overcome this. In this particular case one Apple row has come off and one Kiwi row was added.
I don't mind macro or formula.
Thanks a million
Andonny
Previous
........primary....secondary...conversion
pear....kg......kg........1.5
apple...kg......cr........2.1
apple...kg......cn........3.4
apple...kg......ea........0.5
kiwi....ea......kg........4
kiwi....ea......ea........4.1
Current
........primary....secondary...conversion
pear....kg......kg........2............changed
apple...kg......cr........2.1..........
apple...kg......ea........1............changed(one conv missing)
kiwi....ea......kg........4............
kiwi....ea......ea........4.1..........
kiwi....ea......cr........10...........changed (added)
Posted by Aladin Akyurek on April 27, 2001 5:00 PM
Andonny
The following array formula will inform you whether your data have been altered:
="ISCHANGED: "&NOT(AND((PREVIOUS)=(CURRENT)))
where PREVIOUS is the name of the columnar range of interest (e.g., the cells containing fruit names) and CURRENT, again the name of the
cells containing fruit names in the possibly altered data.
Remember hitting CONTROL+SHIFT+ENTER to enter the array formula.
Hope this is sufficient.
Aladin
Posted by Andonny on April 27, 2001 5:21 PM
Hi,
Thank you so much for your help. You have made my life a lot easier at work.
With the array formula I managed to get #N/A. I must be applying it wrongly. I placed it into the column where "changed" is. I don't understand the first part of the fomula and therefore I wasn't able to decide what I was doing wrong.
Thanks a million
Andonny
Posted by Dave Hawley on April 27, 2001 7:42 PM
Hi Andonny
Have you looked as Tools>Track changes ? This will give you loads of detail!
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 28, 2001 12:17 PM
Andonny,
The previous formula was intended to globally assess whether any change occurred. That formula works. The way you tried to apply the formula tells me that you want detailed info. So I'm proposing you now a different system of formulas that produce more info.
I'll assume that the previous data occupy the range A2:D8 and consists of the following:
{0,"primary","secondary","conversion";"pear","kg","kg",1.5;"apple","kg","cr",2.1;"apple","kg","cn",3.4;"apple","kg","ea",0.5;"kiwi","ea","kg",4;"kiwi","ea","ea",4.1}, where the first entry (d.i. 0) means blank.
I'll also assume that the current, possibly altered, data occupy the range A12:D18 and consists of the following:
{0,"primary","secondary","conversion";"pear","kg","kg",2;"apple","kg","cr",2.1;"apple","kg","ea",1;"kiwi","ea","kg",4;"kiwi","ea","ea",4.1;"kiwi","ea","cr",10}, where the first entry (d.i. 0) means blank.
In E1 array-enter: =NOT(OR(AND(A3:A8=A13:A18,B3:B8=B13:B18,C3:C8=C13:C18,D3:D8=D13:D18)))
This formula results in either FALSE or TRUE. The value TRUE indicates globally that the previous and current data are different.
In E13 arrayenter: =IF($E$1,IF(ISNUMBER(MATCH(A13&B13&C13&D13,A3:A8&B3:B8&C3:C8&D3:D8,0)),"","CHANGED"),"") [ copy down this to E18 ]
This formula tells you per row of the current data whether it is "changed", meaning whether the values of the row in question exist as such in the previous data.
Applied to your example data, we get as an addtional column to the current data:
{0,"primary","secondary","conversion",0;"pear","kg","kg",2,"CHANGED";"apple","kg","cr",2.1,"";"apple","kg","ea",1,"CHANGED";"kiwi","ea","kg",4,"";"kiwi","ea","ea",4.1,"";"kiwi","ea","cr",10,"CHANGED"}
Aladin
PS. Let me know if you'd like to have a copy of the workbook showing the above machinary.
