12

microsoft excel question (Read 356 times)

R2E


"run" "to" "eat"

    what is the best way to find the shared members between two sets, using excel?

     

    i have a list of 323 items with columns -- item number, product name, product creator, price. i have another list of 800+ items which can be formatted to include the same columns but the data beyond item number may not match because the lists are from different sources. 

     

    what i want to know -- do any of the 323 appear on the 800+ list? 

    i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

      vlookup?

        vlookup?

         

        Probably your best bet.  Which data would you expect to be in both data sets (item number)?

        And you can quote me as saying I was mis-quoted. Groucho Marx

         

        Rob

        R2E


        "run" "to" "eat"

          i can't figure out how that works. it looks like i would still have to feed 323 individual numbers in. i can't tell how it is any different than ctrl+f.

           

          what am i missing about vlookup?

          i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

          R2E


          "run" "to" "eat"

            yes, item number is the same in both. 

             

            okay, actually, it's part of item number. one list has ISBN13 and one has ISBN10, but there is a unique string in each to match on.

            i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

              yes, item number is the same in both. 

               

              okay, actually, it's part of item number. one list has ISBN13 and one has ISBN10, but there is a unique string in each to match on.

               

              As long as they are exact matches -- vlookup should work.  However, if they don't match exactly, you could have trouble and have to do some excel judo to make things work...

              And you can quote me as saying I was mis-quoted. Groucho Marx

               

              Rob

              R2E


              "run" "to" "eat"

                okay. 

                 

                how the hell does vlookup work???????????????

                 

                i have googled it and read stuff, but it is not penetrating my brain.

                i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                R2E


                "run" "to" "eat"

                  okay, i copied my 323 item numbers to a new sheet, column A. copied my 830 item numbers to the same sheet column J. 

                   

                  put this into D1 -- 

                   

                  =VLOOKUP(A1,J:J,1,FALSE)

                   

                  and copied it down the D column. now the D column is showing me a lot of item numbers and some N/A. if i am interpreting this correctly, it means that if i am seeing an item number, then there is a match, and if not, no match.

                   

                  right?

                  i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                  MrH


                    Right. And now if you sort the calculated column you are left with a group that is shared, and a group that is not.

                    The process is the goal.

                    Men heap together the mistakes of their lives, and create a monster they call Destiny.

                      "what i want to know -- do any of the 323 appear on the 800+ list? "

                       

                      I think you want to put the formula in column K, and refer to the data in columns A-D....  Within Colunn D, for fun, place "YES" within each row of observations #1 - #323.

                       

                      so...

                      vlookup(j1,a1:d800,4,false)

                       

                      where A1 could equal J1

                      where column D (4th column) contains information that you want to show within column K.

                       

                      So, Column K would show "YES" every time that any of the 323 appear on the 800 list.

                      2014 Goals:

                      #1: Do what I can do. <DOING>

                      #2: 365 Hours training

                       

                      R2E


                      "run" "to" "eat"

                        okay. this seems to be working in the scenario where i have copied the columns into the new sheet. 

                         

                        what if i want to do this in the original sheets?

                         

                        i have added a column to the 323 sheet, to hold the vlookup. i copied the working formula from the setup sheet to the real sheet and replaced the range with the range from the 830 sheet -- and got an entire column of N/A. 

                         

                        i switched it back -- left vlookup in the 323 sheet, but pointed it at the fake sheet where i only have the item number colums. boom. works. point it at the real sheet, doesn't work. 

                         

                        i don't mind copying the two columns to a set up sheet, but should it work in the real environment?

                        i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                        R2E


                        "run" "to" "eat"

                          oops -- wait, wait -- in the setup sheet i changed the numbers from "number stored as text" to real actual numbers. that is why it worked in the setup sheet. 

                           

                          the 830 sheet has numbers stored as text. 

                          i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                          R2E


                          "run" "to" "eat"

                            without changing the content of the 830 sheet, how do i get this to be a number? then i could point at the original sheet.

                             

                            '[status list 012612 (2).xls]Sheet 1'!$B$6:$B$835

                            i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                            R2E


                            "run" "to" "eat"

                              while you're at it.... what about this?

                               

                              =IF(VLOOKUP(B2,[Book1]Sheet1!$J:$J,1,FALSE), yes, no)

                               

                              when i try this, i get an error. how can i use vlookup and the IF function to get a yes or no to show in the column if there is a match?

                              i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                              R2E


                              "run" "to" "eat"

                                looks like i am talking to myself again.

                                 

                                i got this far -- 

                                 

                                =IF(ISNA(VLOOKUP(B3,[Book1]Sheet1!$J:$J,1,FALSE)) = TRUE, "no", "yes")

                                 

                                now alls i need it to make the original sheet values into numbers. but, i don't think i am going to bother with that -- i will simply copy the column to the far side of the sheet of 323 and point at ti there.

                                 

                                thanks, y'all!

                                i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams

                                12