I had this random assignment thrown on me at work to reconcile some accounts. I'm taking Quickbooks and creating a pivat table in excel, and then using our time card system to make another pivat table next to the QBs one and comparing them. Basically I'm comparing to make sure that the time card and QBs match. Right now, I'm just visually looking at all of the numbers with my eyes and highlighting when they don't match.
One issue is that the QBs codes are the full code (Chargecode:2050.xx-323) and the timecard one is the same thing just without the charge code portion (2050.xx-323). My question is, if I have two pivate tables side by side, can I add a function that will compare the two if they are similar and highlight if they are the same, or just some way to see if they are different?
This is much easier to show how to do than explain. Want to attach a (redacted) flat file of the lead sheet with a quick example of how you have it set up?
Probably use a sumif (if the chargecode references won't be unique - probably safer than doing a vlookup) formula to compare the values.
You can use the concatenate function to fix the cross-reference. While you can spell out Concatenate, you can also just abbreviate by using the & symbol.
i.e., ="Chargecode:"&A2 would append the Chargecode text in front of the account to allow you to match via formulas. Then you'd do something like sumif where the Range is the column from the table with Chargecode already in it, the Criteria is the cell value is a "fixed" concatenated chargecode value, and the Sum_Range is the column of balances from the table with Chargecode already in it.
Edit: Attached a most rudimentary example. [I'd probably set it up differently (wouldn't use the pivot tables, since the data ranges would need to be constantly reset & validated for completeness), but it should give you an idea of how the concatenate & sumif formulas work.]
Edit: Just wanted to say thanks again, but I figured it out. I ended up doing =match(D6,A6:A6) then =match(D7,A7:A7) then highlighting both and dragging them down so Excel updated the formula as it went down
Hey, thank you very much for your help. I've attached what my setup looks like. I only use pivate tables because it is quick to pull the info I need from the master sheets based on how our timecard system and QBs export to excel.
I'm less concerned about the hours actually matching- I need to do that too but it is pretty easy to verify the hours manually. The thing I'm looking for is verifying that the charge codes are correct... so not sure if that is possible since the data is different. They both have the codes in them, but the QBs also have the label on them. The codes are just really long so a little concerned that eventually when I get to like 1852:238-7 my eyes might displace some numbers and I miss some if that makes sense.
Not sure that I understand how you are using the Match formula (D6 is null), and I'm not sure if your data will always be constructed in such a way that the Quickbooks and Timecards will align, but you can just use the equal sign to ask Excel if something is the same or different.
In Cell G4, enter the following as an example -
=TRIM(A4)="Chargelabel:"&TRIM(E4)
Then you can set conditional formatting such that if the values are false, it will highlight the cell.
If you are using Excel 2010 then, there is no need to worry about any formula you can simply remove and highlight all duplicate values with the help of just one click.
I had this random assignment thrown on me at work to reconcile some accounts. I'm taking Quickbooks and creating a pivat table in excel, and then using our time card system to make another pivat table next to the QBs one and comparing them. Basically I'm comparing to make sure that the time card and QBs match. Right now, I'm just visually looking at all of the numbers with my eyes and highlighting when they don't match.
One issue is that the QBs codes are the full code (Chargecode:2050.xx-323) and the timecard one is the same thing just without the charge code portion (2050.xx-323). My question is, if I have two pivate tables side by side, can I add a function that will compare the two if they are similar and highlight if they are the same, or just some way to see if they are different?
Thanks a ton !
Thanks to Spiderboy4 for the sig!
Probably use a sumif (if the chargecode references won't be unique - probably safer than doing a vlookup) formula to compare the values.
You can use the concatenate function to fix the cross-reference. While you can spell out Concatenate, you can also just abbreviate by using the & symbol.
i.e., ="Chargecode:"&A2 would append the Chargecode text in front of the account to allow you to match via formulas. Then you'd do something like sumif where the Range is the column from the table with Chargecode already in it, the Criteria is the cell value is a "fixed" concatenated chargecode value, and the Sum_Range is the column of balances from the table with Chargecode already in it.
Edit: Attached a most rudimentary example. [I'd probably set it up differently (wouldn't use the pivot tables, since the data ranges would need to be constantly reset & validated for completeness), but it should give you an idea of how the concatenate & sumif formulas work.]
Hey, thank you very much for your help. I've attached what my setup looks like. I only use pivate tables because it is quick to pull the info I need from the master sheets based on how our timecard system and QBs export to excel.
I'm less concerned about the hours actually matching- I need to do that too but it is pretty easy to verify the hours manually. The thing I'm looking for is verifying that the charge codes are correct... so not sure if that is possible since the data is different. They both have the codes in them, but the QBs also have the label on them. The codes are just really long so a little concerned that eventually when I get to like 1852:238-7 my eyes might displace some numbers and I miss some if that makes sense.
Thanks to Spiderboy4 for the sig!
In Cell G4, enter the following as an example -
=TRIM(A4)="Chargelabel:"&TRIM(E4)
Then you can set conditional formatting such that if the values are false, it will highlight the cell.