A heck of approach to teaching. Easy to understand human voice, and organized manner in presenting what it matters. Bob clearly understand the principle of "Less Is More" and more importantly, how to use this value-add principle effectively. Thank you for taking the time to do this right. - God bless.
I absolutely love this function! Can't believe I only learn it 6 years after you shared this tutorial. Unfortunately I can't find the tutorial zip file, would you be kind enough to share it again? Thanks.
That's brilliant! Tks. I have two question, pls. When you say the "same information" between the worksheets to create the relationship you mean the value?. 2. Is possible to change the format to info add in value field? Because, when I change one automatic change for all that I have add in value. For exemplo: 1 is for sum (total the jobs; and other in average/time. TKS in advance you are changing my work life.
My data has the same tables but order numbers are not the same. They're always unique values. I have two sheets with annual performance and need to combine the two sheets into one pivot to track product level performance. Any idea how to do that?
Good morning Bob. I have a small problem, I cannot find the file "Pivot Consolidate.xlsx" anywhere or maybe I am looking in the wrong place, please help. Kind regards, Thomas
I have no Duplicate values in (the Table, set as Primary) my Order number, I think Excel is counting blank cell as duplicate values. Please tell me how to solve it?
Nice Vid Thanks a lot. @ 7:24 you say there's nothing visibly different but actually the table icons change over on the right, they gain a 'filled in' header bar rather than empty :)
Exactly what I have been looking for in pivot table. Why haven't I known this earlier! I used to consolidate into one sheet for this job. Thank you very much.
What if there is no primary key? What if I am looking to summarize values (similar to GROUP BY)? Basically, my UserID's appear in two tables, and am looking to join tables on those ID's in order to perform aggregate functions (sums, averages, maxima).
Great video, but I'm struggling on why the relationships have to be the order number from Payment>Order and Payment>Customer. In MS Access I typically related the unique table (in this case Customer) to the other related tables, in this case Order and Payment info. Here's my SQL from Access and it give me the correct sales amount by State. Can you anyone help shed some light on why Excel doesn't behave the same and why? Thanks! SELECT [Customer Info].State, Sum([Payment Info].[$ Sale]) AS [SumOf$ Sale] FROM ([Customer Info] INNER JOIN [Order Info] ON [Customer Info].[Order #] = [Order Info].[Order #]) INNER JOIN [Payment Info] ON [Customer Info].[Order #] = [Payment Info].[Order #] GROUP BY [Customer Info].State;
Sir, in the filed list, I can see the Order number in one table, but not in the other table. Even it is in my both table, but it is not showing in filed list
I have a MS Access database having only one form. There is a total field in that form. The form is coloumnar i.e. one record per sheet. For example, in sheet1, my total field value is 10 and in the second sheet my total is 20. Can i add two separate totals in separate sheets (i.e. 10+20) and add the result in the third sheet total field. Kindly help me.
I have used pivot tables and certain types I have no problem with. But I have a requirement to look at a new type of information (disease names) where they are spread across multiple columns (up to 50) but each row is associated with a year, from 1950 to 2020. So the need is to calculate all the different diseases and the number of times they are seen, with each year, across all the columns. Could you help or do you know anyone who could? Thank you, John
When I try to create the relationships, it is telling me that duplicate values exist in the columns. is there a workaround for this because the data set that I am using is for students attending a class and will contain duplicates.
Hi, what can I do if I want to filter the value on a range? for example, if I have a From-To column and I want to analyze just that data package? Excel does not admit the range because does not have unique values
This is really helpful, but if there is a duplicate value in ORDER# column, is there anyway we can still run this process? Really need it as I do have a few duplicate values in the common column.
Hey Sir, kindly can you give me a solution for the problems which I face while duplicating tabs in my excel sheet then it asks that there is data already there and sometimes it takes main 2 or more minutes to click OK on dialog box which appears. Kindly, tell me if there is another shortcut.
Hoping this older post may still be monitored. Thank you for this simply explained video! I've created a pivot using data from two worksheets within the same workbook. Both have the same heading names for Part Numbers and Part Quantities. The quantities from one sheet show correctly by item. The quantities from the second sheet show the summed quantity of all Part Quantities in the Part Quantity column. Any advice on how to return the correct quantities by item? I tried this in MS BI too and received the same results. Thanks in advance!
I am getting msg that "both selected columns contain duplicate values. Atleast one of the columns selected must contain only unique values to create a relationship between the tables" . Kindly guide please
I was just watching the video and I found it extremely helpful.Unfortunately the only thing I fail to understand is why do we begin creating the connections always by selecting the "payment info" tab as first?I tried beginning with "customer info "(created a pivot with it) and then related to "order info" and "payment info"but it did not work properly.I guess it is a data tables relationship principle I'm failing to understand completely?
I have followed all the steps but when in a pivot table I am selecting data from multiple sheets for e.g.:-order # from customer info and sales from payment info excel shows me a dialogue box which recommends me to create a relationship. Please help me out on this error.
Omg thank you, I’m trying to update our inventory in quickbooks without hand entering each line item and I couldn’t figure out how to combine data. This is going to make life so much easier
It is more helpful to stick to the topic. Just cover the procedures to execute the desired goal. Please do not include anything else. I am already confused.
hi enjoyed your video and i need help from your side.I want to copy data from one sheet to another sheet.I need when i paste it should go to the exact matching serial number.can you help with that
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Great video! Just a question: If there are for example 5 order numbers (1, 2, 3, 4, 5) in one sheet, does it have to be the same for other sheets? What if another sheet has 1, 2, 3, 4, 5, 6, 7? How will the relation work for order number 6 and 7?
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Hi, my option for relationships is not active yet i chose the option for "add this data to the data model". Also, at the pivot table fields i cant see the options for the different worksheets
Not bad! The main point of concern here is that he is using Excel 2013, not Excel 2010, so you may not be able to do exactly what he does, but, again, the overall video is not bad.
The unique field/column that you are creating the relationships for (eg: order#) MUST NOT have duplicates. You either have to select a column that is unique in values (meaning it is unique and not repetitive eg: GeographyID, ClassCode, Serial#, Sales#, Receipt#), or you will have to select a column that is closest to being a unique value and have the duplicates removed from the data ribbon. Then you can start creating your data relationships by tying up the unique columns between each table.
I am having the same problem and the data does not have any duplicates. At first, I thought it might be blank rows but I have doubled checked and that isn't the case. Has anyone experience it and found the solution?
Dear , thank you for your tutorial , it was helpful i didnt know that 2013 does this , the issue that when creating the same pivots does it need to be as the same criteria ? i know having a unique value is the common thing between all sheets , but will it show for you ? i will try it , thanks
Very good, but dissapointed since I could not use it with my data. (I have to summarize multiple years data which each have their own tab and each year's tab has data by region on it.... so what I'll call the control# is what I consider the primary key, but so for each control # there are multiple regions reporting results for that control#. and how would I relate the tables, relate 2012 to 2013, then 2012 to 2014 or what?
anybody else notice that when Bob finished creating the relationships between the tables the table icon on the PT fields pane darkened up a little..? 6:57
![[Live] ศึกมหึมันส์มวยไทยสังเวียนเดือด เวทีมวยชั่วคราวสวนสยาม | จันทร์ 30 กันยายน 2567](http://i.ytimg.com/vi/pM1k6f7JXUc/mqdefault.jpg)
This was one of the best tutorial videos I have seen. A very relaxed but comprehensive style in delivery.
great... finally I was able to understand how to create a data model if you don't have the powerpivot feature.. Thanks a lot
This tutorial was crispy clear & easy to understand! Very helpful 🙌🏾...Thank you & GREAT JOB!!! 👍🏽
Thanks for this, but how can I combine my tables with the same labels using excel functions
A heck of approach to teaching. Easy to understand human voice, and organized manner in presenting what it matters. Bob clearly understand the principle of "Less Is More" and more importantly, how to use this value-add principle effectively. Thank you for taking the time to do this right.
- God bless.
I absolutely love this function! Can't believe I only learn it 6 years after you shared this tutorial.
Unfortunately I can't find the tutorial zip file, would you be kind enough to share it again? Thanks.
You"re an incredibly good teacher. Thank you!
best tutorial, thanks for share ...
That's brilliant! Tks. I have two question, pls. When you say the "same information" between the worksheets to create the relationship you mean the value?. 2. Is possible to change the format to info add in value field? Because, when I change one automatic change for all that I have add in value. For exemplo: 1 is for sum (total the jobs; and other in average/time. TKS in advance you are changing my work life.
Nicely done Bob....to the point, clear explanations and good verbal presentation...thanks
My data has the same tables but order numbers are not the same. They're always unique values. I have two sheets with annual performance and need to combine the two sheets into one pivot to track product level performance. Any idea how to do that?
i'm also trying to do this
Good morning Bob. I have a small problem, I cannot find the file "Pivot Consolidate.xlsx" anywhere or maybe I am looking in the wrong place, please help. Kind regards, Thomas
I have no Duplicate values in (the Table, set as Primary) my Order number, I think Excel is counting blank cell as duplicate values. Please tell me how to solve it?
Nice Vid Thanks a lot. @ 7:24 you say there's nothing visibly different but actually the table icons change over on the right, they gain a 'filled in' header bar rather than empty :)
Exactly what I have been looking for in pivot table. Why haven't I known this earlier!
I used to consolidate into one sheet for this job.
Thank you very much.
This was fantastic, it just didn't answer the question I was looking for. (crying emojis)
Can we share the workbook after enabling Table function for all the worksheets?? For multiple users to work at the same time?
This videos has 9 years old but bet 90% of excel users doesn't know about this useful feature
Bob, I have 5 tabs with identical column headings, how do I do the pivot table ???
What if there is no primary key? What if I am looking to summarize values (similar to GROUP BY)? Basically, my UserID's appear in two tables, and am looking to join tables on those ID's in order to perform aggregate functions (sums, averages, maxima).
Hello Mr Excel. I have runtime error 5 on code line : sc2.SlicerItems(SI1.Name).Selected = SI1.Selected.
Would you please advise solution.... thanks
Amazing. I was able to do it. Newer videos here teach how to use vlookup to add fields. Very difficult and long. This one easy and reliable.
If i use vlookup will that be more easy going. As this way is advance way
Great video, but I'm struggling on why the relationships have to be the order number from Payment>Order and Payment>Customer. In MS Access I typically related the unique table (in this case Customer) to the other related tables, in this case Order and Payment info. Here's my SQL from Access and it give me the correct sales amount by State. Can you anyone help shed some light on why Excel doesn't behave the same and why? Thanks!
SELECT [Customer Info].State, Sum([Payment Info].[$ Sale]) AS [SumOf$ Sale]
FROM ([Customer Info] INNER JOIN [Order Info] ON [Customer Info].[Order #] = [Order Info].[Order #]) INNER JOIN [Payment Info] ON [Customer Info].[Order #] = [Payment Info].[Order #]
GROUP BY [Customer Info].State;
Sir, in the filed list, I can see the Order number in one table, but not in the other table. Even it is in my both table, but it is not showing in filed list
does it always have to be the identifier the same for vlookup always on the left? thanks
I have a MS Access database having only one form. There is a total field in that form. The form is coloumnar i.e. one record per sheet. For example,
in sheet1, my total field value is 10 and in the second sheet my total is 20. Can i add two separate totals in separate sheets (i.e. 10+20) and add the result in the third sheet total field. Kindly help me.
how to creat pivot both sheets have a duplicates only order is different please help me it is very usefull to me as soon as possible sir
I have used pivot tables and certain types I have no problem with. But I have a requirement to look at a new type of information (disease names) where they are spread across multiple columns (up to 50) but each row is associated with a year, from 1950 to 2020. So the need is to calculate all the different diseases and the number of times they are seen, with each year, across all the columns. Could you help or do you know anyone who could? Thank you, John
Hi John,
If You still require assistance, please contact Me on goodnessshallprevail@gmail.com.
Thank You!
When I try to create the relationships, it is telling me that duplicate values exist in the columns. is there a workaround for this because the data set that I am using is for students attending a class and will contain duplicates.
Use a student ID number rather than their name.
Pivot table relationship create duplicate entries how to fix that?
Hi, what can I do if I want to filter the value on a range? for example, if I have a From-To column and I want to analyze just that data package? Excel does not admit the range because does not have unique values
Sir macro ki sahayta se marksheet me photo automatically kaise add kare please sir video bataiye.
This is really helpful, but if there is a duplicate value in ORDER# column, is there anyway we can still run this process? Really need it as I do have a few duplicate values in the common column.
Hey Sir, kindly can you give me a solution for the problems which I face while duplicating tabs in my excel sheet then it asks that there is data already there and sometimes it takes main 2 or more minutes to click OK on dialog box which appears. Kindly, tell me if there is another shortcut.
In a PivotTable using a data model (linked tables), how do you do calculated fields? The option to add them becomes grayed-out.
Wonderful. Solved my problem that I would like to do comparison between data of sale & purchase. Thank you & happy new year 2020.
Hoping this older post may still be monitored. Thank you for this simply explained video! I've created a pivot using data from two worksheets within the same workbook. Both have the same heading names for Part Numbers and Part Quantities. The quantities from one sheet show correctly by item. The quantities from the second sheet show the summed quantity of all Part Quantities in the Part Quantity column. Any advice on how to return the correct quantities by item? I tried this in MS BI too and received the same results. Thanks in advance!
I am getting msg that "both selected columns contain duplicate values. Atleast one of the columns selected must contain only unique values to create a relationship between the tables" .
Kindly guide please
WOWWWW! This is so great. A very helpful video. You used just 9 minutes to take me out of my sleepless nights. Thank you sir!
is there really no way to do this with Excel 2010??? (Dont have the "add this data to the data model" part).... Its sooo frustrating.
One of the best video with all the explanation... 👍☺️☺️
I was just watching the video and I found it extremely helpful.Unfortunately the only thing I fail to understand is why do we begin creating the connections always by selecting the "payment info" tab as first?I tried beginning with "customer info "(created a pivot with it) and then related to "order info" and "payment info"but it did not work properly.I guess it is a data tables relationship principle I'm failing to understand completely?
I have followed all the steps but when in a pivot table I am selecting data from multiple sheets for e.g.:-order # from customer info and sales from payment info excel shows me a dialogue box which recommends me to create a relationship. Please help me out on this error.
Omg thank you, I’m trying to update our inventory in quickbooks without hand entering each line item and I couldn’t figure out how to combine data. This is going to make life so much easier
It is more helpful to stick to the topic. Just cover the procedures to execute the desired goal. Please do not include anything else. I am already confused.
for example, what if your oranges were more expensive and a extra charge of 2.55% (only the oranges) how am I able to show that on the same table?
Unfortunately this feature is not available in the Apple's IOS excel version.
this is useful without using power pivot thanks
Hi, can you make a video on how to add up all the quantity of a specific (unique) item in a data?
Thank you
hi enjoyed your video and i need help from your side.I want to copy data from one sheet to another sheet.I need when i paste it should go to the exact matching serial number.can you help with that
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would
like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would
like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Thanks dear, honestly it is very useful and helpful, more than everything your teaching way is very good.....
Excellent Bob I really got a lot of very useful information and I now understand!!!
I was creating a tutorial of my own but I was stuck because I forgot to change to a table with headers. This tutorial has helped me, thank you.
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would
like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Great video! Just a question:
If there are for example 5 order numbers (1, 2, 3, 4, 5) in one sheet, does it have to be the same for other sheets? What if another sheet has 1, 2, 3, 4, 5, 6, 7? How will the relation work for order number 6 and 7?
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would
like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
*It''s very Advanced and tricky for report* *Ontime Edu* 👌 👌
When I setup the relationship the items from the other table is giving me constant value. Can you please help?
Hi, my option for relationships is not active yet i chose the option for "add this data to the data model". Also, at the pivot table fields i cant see the options for the different worksheets
It was interesting, it will be great help to many
Very useful video. Very timely help. Thank you.
the relationshipis fail because my data both selected columns contain duplicate value. why? i see in this video also have duplicate value.
Good to watch, informative.
I wish you can show us how to draw a graph from this
How about Excel in Office 365?
The host teaches very easy to understand. Thank you for sharing! ❤️
Please advise if this method can be used with Excel 2010.
Thank you.
Very good and informative video
Stumbled onto by accident but must say it was a very good instructional video. Thanks for putting this together
its a great tool but if u hv mutiple values in other sheet with key value then how it will work. please suggest
Thanks a lot and i am using it n it is working .
Why do people say 'two times' instead of 'twice'??
Thank you. This is what I have been looking for👍👍👍
Can you assist with a specific issue? I would like to work with you by phone re errors on my vlookup and pivot charts please
good video but 10pct of the video is renaming the tabs and tables, could have been done beforehand
You're a legend. What a great video, well explained. :-D
Am unable to find this opeion on Excel 2007 version. How can we get it ?
can you do this with ranges rather than tables? if so, how?
Great video! The presenter explains the technique well explained, concise, and informative. Thank you!
Not bad! The main point of concern here is that he is using Excel 2013, not Excel 2010, so you may not be able to do exactly what he does, but, again, the overall video is not bad.
Angelo Sammarco
This was extremely helpful. If we don't have an order number but only month and year, can we relate that instead?
👍
Thanks, very good info after "PIVOT TABLE"
the Relationships don't work for me, says data has duplicates, any thoughts, I've tried to select multiple collums to match up, no luck.
I faced the same, by any chance did u find a solution or work around. thanks
Querobine dsouza nothing
Ok, pls can you described how your data looks like may be i can help. Because, at times it is the "Append" that works not "Merge"
The unique field/column that you are creating the relationships for (eg: order#) MUST NOT have duplicates. You either have to select a column that is unique in values (meaning it is unique and not repetitive eg: GeographyID, ClassCode, Serial#, Sales#, Receipt#), or you will have to select a column that is closest to being a unique value and have the duplicates removed from the data ribbon. Then you can start creating your data relationships by tying up the unique columns between each table.
I am having the same problem and the data does not have any duplicates. At first, I thought it might be blank rows but I have doubled checked and that isn't the case. Has anyone experience it and found the solution?
Very nicely explained .Thanks
Thanks
Dear , thank you for your tutorial , it was helpful i didnt know that 2013 does this , the issue that when creating the same pivots does it need to be as the same criteria ? i know having a unique value is the common thing between all sheets , but will it show for you ? i will try it , thanks
what should i do when i go to insert the pivot table and "add this data to the Data Model" is not "select-able"?
+Vanessa Riley : Are you view the excel workbook in compatibility mode? If so, save it as a normal excel workbook and try again.
Very good, but dissapointed since I could not use it with my data.
(I have to summarize multiple years data which each have their own tab and each year's tab has data by region on it.... so what I'll call the control# is what I consider the primary key, but so for each control # there are multiple regions reporting results for that control#.
and how would I relate the tables, relate 2012 to 2013, then 2012 to 2014 or what?
Share your data then it may be understood properly and solution provided.. it works perfectly with different data I have tried
I appreciate you sharing this nice tool. This, I call as Magic, and can smartly consolidate and reconcile Financial Data. Thanks a Ton.
Thanks for share sir 👍
This pivot table can be used for more than 4 sheets?
I guess this will work on more than 4 sheets so long that they have unique data to relate all the tables.
What shall i do if there is repeating og data among the data sheets??
anybody else notice that when Bob finished creating the relationships between the tables the table icon on the PT fields pane darkened up a little..? 6:57
Very useful and explained in simple language
One of the Best vedios I had ever seen about excel
Hello Sir! I have been looking for this kind of video for long. In fact, I am using excel 2010 and Data Model is missing. How can I deal with that?
ENEA MYFTARI Buy excel 2013?