MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.