+ Reply to Thread
Results 1 to 19 of 19

Organizing the differences between identical and new entries.

  1. #1
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Question Organizing the differences between identical and new entries.

    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.
    Attached Files Attached Files
    Last edited by Cyberpawz; 01-24-2012 at 02:55 PM.

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Organizing the differences between identical and new entries.

    What do you mean by "Need the search to depend on..."? Are you planning on providing the user a search box?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: 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?

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    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
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    Quote Originally Posted by rylo View Post
    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?
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    Anyone have a clue? I am pretty much stuck till I can get this working...
    Attached Files Attached Files
    Last edited by Cyberpawz; 01-26-2012 at 09:45 AM.

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    Hi

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

  8. #8
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    It works, but How does it work?

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    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

  10. #10
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    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.

  11. #11
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    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

  12. #12
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    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?
    Last edited by Cyberpawz; 02-01-2012 at 04:07 PM.

  13. #13
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    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

  14. #14
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    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.
    Attached Files Attached Files
    Last edited by Cyberpawz; 02-02-2012 at 09:09 AM.

  15. #15
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    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

  16. #16
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    GAH! I really need to stop attempting to do things when I'm sick. thanks. I will have a question in a minute.
    Last edited by Cyberpawz; 02-03-2012 at 11:05 AM.

  17. #17
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    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.

  18. #18
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Organizing the differences between identical and new entries.

    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
    Last edited by rylo; 02-03-2012 at 04:14 PM.

  19. #19
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Organizing the differences between identical and new entries.

    Somewhat, thanks for the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1