Jump to content


Photo

Reconcile information

Pivotables and Reconcile

  • Please log in to reply
7 replies to this topic

#1 hongminjincn

hongminjincn

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 03 April 2015 - 01:50 AM

Hey,

It has been very helpful for my work by learning through this professional video series. Thanks a lot!

Now I got things I can not figure out how to do it which is considered pretty common in Financial Analyst field.

 

I have a workbook contain two worksheets "Changepoint Raw Data" tab and "Labor Distribution Raw Data" tab. (data resource is fictitious)  I need use side by side Pivot Tables to reconcile the information to be sure that both tables show the same amount of hours per ELCID. And how many ELCID records do not match?

I think I can embed two Pivot tables created by these two sheets, but I do not know how to do next to compare and find out records do not match? and how to reconcile the records? Please find attached file of the workbook.

 

Appreciate for help,

B. Regards,

Hongmin

 

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 03 April 2015 - 09:38 AM

Hi Hongmin,

 

The first thing I've done is to convert both of your data sets into Tables named Changepoint and LaborDistribution. This makes it easier to create pivot tables based upon them.

If you're not familiar with Tables, you can find an introduction to them in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

 

It's then relatively simple to create pivot tables from your data by using the skills taught in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

I've created two pivot tables that show a sum of Hours for each ELCID and placed them side by side:

 

pivot1.png

 

This almost works for your purposes, but it won't allow you to reconcile your figures easily because the Changepoint table contains one more ELCID than the LaborDistribution table (FIJUDICIAL) so the two tables go out of sync after a certain point.

 

You could resolve this by making sure that both data sets contain the same ELCID values, but you could also solve this by using Excel 2013's new Data Model features to define a relationship that joins the tables together.

 

To achieve this gracefully, I first created a table that contains all of the ELCID values and then created relationships to join the ChangePoint and LaborDistribution tables to it:

 

pivot2.png

 

For an introduction to creating relationships, see the video lesson: Expert Skills Lesson 6-2 Create a simple data model.

 

Once the relationships have been defined, I can create a single 'OLAP' pivot table that is able to work with data from both of your tables at the same time:

 

pivot3.png

 

The last thing you wanted was to check whether the Changepoint and Labor Distribution values match for each ELCID. In an ordinary pivot table, you could do this using a Calculated Field, but unfortunately these aren't supported in OLAP pivot tables. The only solution at this point in time is to place a formula alongside the pivot table to check whether the values match.

 

You can do this using a simple IF function:

=IF(B2=C2,0,1)

 

This will place a 1 in the cell if the values do not match, so you can find out how many values do not match by simply summing the column.

For an introduction to the IF function, see the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

I have also applied conditional formatting to make it easier to spot the records that do not match.

For more on conditional formatting, see the video lesson: Essential Skills Lesson 4-15 Use simple conditional formatting.

 

pivot4.png

 

Of course, one more thing to consider is that you don't really need to use pivot tables for this at all! You could achieve the same thing by simply using formulas with the SUMIF function like this one:

=SUMIF(Changepoint[ELCID],[@ELCID],Changepoint[HRS])

 

The references like Changepoint[ELCID] are structured table references. You can learn about these in the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

You can learn more about the SUMIF function in the video lesson: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

 

I have attached a copy of my example workbook showing all three different ways you could do this in the Std Pivot Table, OLAP Pivot Table, and Formulas Only worksheets.

 

Attached File  Copy of Pivotable practice.xlsx   1.11MB   216 downloads

pivot5.png

 

 


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 hongminjincn

hongminjincn

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 03 April 2015 - 02:16 PM

Hi Jonathan, 

Thank you so much for your prompt and very detailed reply. This web has been so helpful! Appreciate!

 

Have a wonderful day



#4 hongminjincn

hongminjincn

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 04 April 2015 - 06:24 AM

Hey Jonathan,

 

One more follow up question, how can I find out  the Changepoint table contains one more ELCID than the LaborDistribution table very quickly and exactly on (FIJUDICIAL)? I guess you did not compare so many rows in two side by side Pivot tables to find that out? ----Thank you so much for help!

Hongmin



#5 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 04 April 2015 - 08:03 AM

Hi Hongmin,

 

I actually spotted it after creating the two pivot tables. When I was comparing them side by side, I noticed that they went out of sync after a certain point, showing that there must be an extra ID in one of the tables.

 

To extract the unique ELCID values in a more reliable way, I copied all values into a new table (on the ELCID worksheet) and then used Excel's Remove Duplicates feature to remove any duplicates and create a unique list.

 

You can find a video lesson showing how to use Remove Duplicates in: Expert Skills Lesson 2-13 Remove duplicate values from a table.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#6 hongminjincn

hongminjincn

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 04 April 2015 - 07:39 PM

Hey Jonathan,

I got a little confused as the unique ELCID in the Changepoint table is not duplicated but actually "FIJUDICIAL"  is the one more ELCID than that in LoaborDistrution table.

 

I use side by side two Pivot table and "EXACT" function: =EXACT(A4,D4) in one column of another side of two pivot tables to get True or False, I look at all rows and get the first row showing False,  find this is the EXACT one, then I go back Changepoint table to filter out "FIJUDICIAL", now all rows showing true. Do you think this way is ok or too slow? or you have any other methods?

 

Appreciate for help,

Have a good weekend!

Hongmin



#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 06 April 2015 - 05:00 PM

Hi Hongmin,

 

I'd say there's no problem with that solution if you find that it's the best for you.  Personally I might prefer the formula-based approach using SUMIF, but I'm not sure that it would be significantly faster.

 

The OLAP solution is maybe a little bit overkill in this situation, but it's good to know that the option exists for more complicated requirements.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#8 hongminjincn

hongminjincn

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 10 April 2015 - 04:30 AM

HI Jonathan,

I just got the time to figure out using SUMIF, I think this solution is better and THANKS for your expertise.

Have a good day,

Hongmin






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users