Thread: Organizing the differences between identical and new entries.

No. This is to have output. Search may be a wrong word to use, but perhaps query?

Hi

Have a look at the attached. I've only partially filled in the outputs, and you would have to probably extend the ranges, and maybe hide the "helper" columns, but see if this will get you going.

rylo

Originally Posted by rylo

Hi

Have a look at the attached. I've only partially filled in the outputs, and you would have to probably extend the ranges, and maybe hide the "helper" columns, but see if this will get you going.

rylo

I tried to download your file, and for some reason it said it was corrupted. Here is what I have been working on somewhat. Check the merged tab. I am running into an issue. For some reason in the Merged tab when I use the formula to view the "New" items, I get a #Value error, when an exact opposite of said script is used right next to it.

Can you help me and tell me why this isn't working?

Hi

I've reloaded my file again unzipped. See if you can open it this time.

It works, but How does it work?

Hi

The helper columns in NEW determine if there is a matching item in OLD. That is the SUMPRODUCT formula in column J. Column K just converts the returned count to be either NEW (it doesn't exist) or SAME (it exists).

Columns I and U in Matches have array formulas that determine the rows that contain the relevant entry types (NEW or SAME), and the SMALL command to determine the incremental next item. Columns A and M then use those results in an INDEX function to bring back the relevant data. The row > countif determines when to put in a blank so that there aren't error messages shown.

HTH

rylo

Can you show the script and explain that? I hate to ask this of you after being so helpful, but I look at the script and I somewhat understand, but there are bits of the formula I have never seen before, and trying to use it elsewhere may break it if I change the fields incorrectly without know it.

Hi

OK, here goes
=SUMPRODUCT(--(Old!$A$2:$A$13=New!A2),--(Old!$C$2:$C$13=New!C2))
SUMPRODUCT multiplies the items and then sums the results.
(--(Old!$A$2:$A$13=New!A2) This part looks for all the items in olda2:a13 that is the same as newa2. It will bring back a true / false result. The -- then converts the true false to a value.
=IF(J2,"SAME","NEW") - this is a normal if statement that gives the result of SAME for true, and NEW for false. A zero is a FALSE, while anything else is a TRUE. So if there is a zero result, then nothing is found, so it is a NEW item.

=SMALL(IF(New!$K$2:$K$13="SAME",ROW(New!$K$2:$K$13),""),ROW()-2)
Take the IF statement first. If the value in the range k2:k13 is new, it brings back the row number, otherwise it will bring back a blank.
The SMALL item will allow you to determine the 1st smallest item, or 2nd smallest item etc That is what the row()-2 is doing. As the first formula is in row 3, ROW()-2 gives you 1. As you go down the rows, this will increment. So it brings back the smallest result of the rows that finds SAME in the search range.
The formula has to be array entered as you are looking for multiple results.

=IF(ROW()-2>COUNTIF(New!K:K,"SAME"),"",INDEX(New!A:A,Matches!I3))
This IF statement does a test to see if the actioned row is greater than the number of SAME responses in column K. If it is then it will bring back a blank. If not, then it does the index component. I'm doing this so that there aren't error responses. The INDEX function takes the result from the previously explained formula which is a row number to bring back the detail from column A in sheet new.

One way to see what is happening is to go to the formula bar, highlight part of the function, and press F9. This will do an evaluation of that part of the formula and you can see what it brings back.

So if you highlight --(Old!$A$2:$A$13=New!A2) from the formula in NEW!J2, then press F9, you will get {0;0;0;0;0;0;0;0;0;0;0;0}. As they are all 0 values, you know that when it is multiplied against its counterpart in the second part of the formula, then all the results will be zero. Summing those will bring back a zero. If you do the same thing to the same subcomponents in J3, you will get {1;0;0;0;0;0;0;0;0;0;0;0} and {1;0;0;0;0;0;0;0;0;0;0;0}, so when multiply those and sum them, you get a result of 1.

HTH

rylo

Hi

Matches!I3: =IF(ROW()-2> COUNTIF(New!K:K,"SAME"),"",SMALL(IF(New!$K$2:$K$13="SAME",ROW(New!$K$2:$K$13),""),ROW()-2))

Remember that this formula has to be array entered. Copy down.

This should remove the #NUM! error message.

rylo

Hi

Heaping further blame in the flu, you haven't array entered the formula. Notice that the entry that you put into U3 isn't surrounded by {}, whereas the entry in U4 does have them. To array enter, edit the formula, hold down both the shift and ctrl keys, then press enter. This will array enter the formula.

rylo