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.
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.
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.
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!
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!