5 Replies Latest reply on Jun 5, 2012 9:45 AM by Jason Paulsen

    Combining data in 3 tabs

    Jason Paulsen

      I have 3 tabs in one excel file. There is a column in each tab that can be used as the key. I have tried to join them using the key in each tab, but the results don't make sense.

      Tab 1 contains registration information.

      Tab 2 contains registration information from a different source.

      Tab 3 is the same as Tab 2.

      It is possible that the values in Tab 2 and 3 exist in Tab 1.


      I am trying to identify those matches.  When I use the join method based on matching the key columns from 1 to 2 and 1 to 3, I don't get the right results.

       

      Thoughts?

        • 1. Re: Combining data in 3 tabs
          Russell Christopher

          Hard to give any useful advice without a little bit more information

           

          Please describe what doesn't look right about the results. Missing rows? Extra rows? From where? Posting a sample would be even better.

          • 2. Re: Combining data in 3 tabs
            Jason Paulsen

            Tab 1 has over 250k rows. When I pull in a dimension, I would expect to see several hundred rows of "matches" between the Tab 1 and Tab 2.  I've uploaded a sample file of how the data is structured, but obviously small in scope.

             

            I want to create a report that shows Tab 2 email addresses and those member groups their assigned to from Tab 1. The same thing for Tab 3.  I'm only comparing Tab 1 to Tab 2, and Tab 1 to Tab 3. Tab 2 and 3 are not being compared to each other.

             

            Thanks!

            • 3. Re: Combining data in 3 tabs
              Russell Christopher

              Jason, please delete that file, it looks like it still contains customer info. I'll upload something that I think should help in a couple minutes.

              • 4. Re: Combining data in 3 tabs
                Russell Christopher

                Here you go. You were trying to match registrants across all 3 sheets based on AccountID, but in your sample data, there were no matching AccountIDs between Sheet1 and Sheet2 or Sheet3.

                 

                I reworked your data so that:

                • Sheet2 has 3 registrants that match Sheet1
                • Sheet3 has 3 matching rows against Sheet1
                • Sheet3 also contains 2 rows that don't match anything at all.

                 

                Therefore, we have 6 matching rows in all. NOTHING matches AccountID 6 from Sheet 1.

                 

                I created a LEFT join between Sheet1 and Sheet2 on AccountID, and I did the same thing between Sheet1 and Sheet3. This means "give me all the rows from the left (Sheet1) regardless of whether those rows match anything on the right (Sheet2 or Sheet3),,,,BUT give me the rows from Sheet2 or Sheet3 that actually match AccountIDs from Sheet1.

                 

                The result is that all 7 rows (from Sheet1) come back. However, Only 6 of these rows actually match something in Sheets2 and Sheet3 - See "All Results" tab in the attached workbook.

                 

                We don't want to see that 1 "non matched" row, so in tab "Hide Non-matching Rows", I added a filter which removes any rows where the email address from Sheet2 or Sheet3 doesn't contain a  value. I actually created a little field ("Other Email Address") that shows the "other" email address for matching accountids in sheets2 and sheet3.

                 

                The secret is the "LEFT" join type, when all is said and done.

                 

                Have a good weekend!

                • 5. Re: Combining data in 3 tabs
                  Jason Paulsen

                  Thanks for catching the remaining data. And I apologize for the confusion.  I meant I was trying to match the email addresses between all the tabs.  But I believe your example and explanation above hit the mark! Thank you very much!