Organizing the differences between identical and new entries.

[Cyberpawz]

I have an excel sheet that I need help with. I have quite a few tabs, and they will be self explanatory in time.

The tabs are Final, Organized, Matches, Old, New, Archives.

This is where I am running into trouble, I don't expect everything to be done for me, but I need a step in the right direction for two formulas.

The first formula is:

1. I have data that is entered into New and Old, I want them to have two outputs on Matches.
   • Matches is broken down into two sections, Same and New.
   • New are items that don't show up in old, and do in new.
   • Same are items that show up in both.
   • I need the search to depend on SO# and Part# before giving output, because there are going to be SO# that are the same, but Part# 99% of the time will be different.
2. I want to have it in Organized where in Matches the new go first, and the old goes after.

Is this possible?

I can't use anything fancy like a Pivot Table, people here at where I work will shy away from using it if they see something that they are not accustomed seeing.

Thanks in advance.

[arlu1201]

What do you mean by "Need the search to depend on..."? Are you planning on providing the user a search box?

[Cyberpawz]

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

[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

[Cyberpawz]

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?

[Cyberpawz]

Anyone have a clue? I am pretty much stuck till I can get this working...

[rylo]

Hi

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

[Cyberpawz]

It works, but How does it work?

[rylo]

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

[Cyberpawz]

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.

[rylo]

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

[Cyberpawz]

Thanks a lot for the help, I have one more request though... see how it shows #num, is there a way to keep that empty when it errors out?

[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

[Cyberpawz]

Thanks a lot I see what you did, I should of been able to do it myself, I blame my cold, seems I got the flu... lol...

I noticed I am running into the same issue I was before with the "new entries" When I change the code to:

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

It errors out... Is there a reason for this? It always seems I miss the simple things, and right now I can't see a difference between the two...here is my file attached.

[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

[Cyberpawz]

GAH! I really need to stop attempting to do things when I'm sick. thanks. I will have a question in a minute.

[Cyberpawz]

Ok, I need to know one thing, why do the {} work? Also, why do they disappear when I click to edit? I'm just glad it works thanks.

[rylo]

Hi

Disappear: When you go into edit mode, you have the raw formula to work with. If you want to remove things from array mode, then you just press enter. Otherwise you have to specificall enter array mode.

Why work: Hmm. Its not the {} that make it work. That is just how the array formula is displayed. Not sure I can really explain it. When the formula processes, it brings back multiple results. In normal mode, only the first result is available. There are some instances where a formula will work on the first result whether it is array entered or not. To access the subsequent entries, you need to array enter the formula. That said, there are various base formulas that do action multiple results without being array entered. SUMPRODUCT is one. COUNTIFS is another (2007). If you write your own function (ie a UDF), you can action multiple items, and bring back a single result, without the function being array entered. A common one is written if you have multiple items that match your criteria, and you want to concatenate the results.

There will be more qualified / knowledgeable posters out there that may be able to give a more technical answer. Hopefully some of them will read the post and respone.

Did a quick google search and amongst the many results came back with this from Chip Pearson.

Does that help?

rylo

[Cyberpawz]

Somewhat, thanks for the help