stevenk331
New Member
- Joined
- May 19, 2011
- Messages
- 3
Hi. My apologies for asking a question that seems to get asked frequently. However, I was not able to figure out a solution from the previous posts, and I have not been able to find a video on the Mr. Excel or ExcelisFun youtube pages that go over "Duplicates" errors in Power Pivot. I am hoping someone can help me with this issue. I am relatively new to using Power Pivot so I have a number of things I cannot figure out. My situation is as follows, my problem will become clear once you understand the situation. Sorry for the long explanation, but I don't know how else to explain it.
I am combining data from 3 sources/tables to create a Pivot Table I can use to create journal entries I can upload to an accounting system for one of my clients (I do their bookkeeping). Normally, I would just journal the entries one-by-one manually, but in this particular case, my client has HUNDREDS of transactions and it would take a significant amount of time vs 20-30 minutes by the method I am trying. The first table is a lookup table of account numbers and client names. The second table is an Excel spreadsheet from the primary vendor which I converted to a table with details on all the transactions with my client. The third is a comma delimited file from the bank with all the transactions. The purpose of the first, the client name lookup table, is because the vendor spreadsheet only shows the "client #", and I need to include the name of the client for my accounting purposes in the journal entries to identify them easier later if I need to find a specific transaction for a specific client of the company in the accounting system. I had no problem linking the first two in Power Pivot, and I had no problem creating data models for all 3 and getting them into Power Pivot.
The "center" is the vendor spreadsheet. The issue is when I try to link the bank transactions to the vendor spreadsheet. The bank transactions only contains 4 columns, i.e. - date, amount, check #, description. I tried to "connect" the dates because obviously, on the bank side, I only see the net transactions related to the vendor which is how I can identify the detail on a specific date. For example, the vendor may have multiple products, some fees, then tax, and the net or total of the invoice. I can use the net or total amount from the bank on a specific date to find the detail which goes below from the vendor on the same date. The problem I get is the duplicate error when I try to link the vendor and bank table. I believe the issue is with the "Check #" column from the bank because it contains mostly blank or "null" cells as only a few checks are written from time-to-time. There are no blank rows, however, I can have a couple hundred transactions in a single month, and only 4-5 actual checks so I will have rows and rows of transactions with no check number or a null field, then a check #, and then dozens more transactions without a check #, etc.
Since it is possible that over a number of months, there can be multiple transaction from the vendor that are for the same amount (although rarely), I need to use the dates to be able to match the correct amount with the vendor detail for the correct date, etc. I am not sure how to fix the problem or perhaps there is a different issue that I am not seeing, but the blank check field is the only one that occurs to me. Does it make sense that a "null" field in a table would be the cause of the "duplicates" issue?
I actually could replicate the information from the vendor table without the bank data, but there is another problem I have been having using Pivot Tables in general and Power Pivot that I have not been able to figure out so the Power Pivot so far has seemed to work best. I have looked at DOZENS of the Mr. Excel and ExcelisFun videos, but I have not been able to figure it out. I would rather not go into another lengthy explanation on that issue.
I hope you can help me with this issue. Thank you again!
I am combining data from 3 sources/tables to create a Pivot Table I can use to create journal entries I can upload to an accounting system for one of my clients (I do their bookkeeping). Normally, I would just journal the entries one-by-one manually, but in this particular case, my client has HUNDREDS of transactions and it would take a significant amount of time vs 20-30 minutes by the method I am trying. The first table is a lookup table of account numbers and client names. The second table is an Excel spreadsheet from the primary vendor which I converted to a table with details on all the transactions with my client. The third is a comma delimited file from the bank with all the transactions. The purpose of the first, the client name lookup table, is because the vendor spreadsheet only shows the "client #", and I need to include the name of the client for my accounting purposes in the journal entries to identify them easier later if I need to find a specific transaction for a specific client of the company in the accounting system. I had no problem linking the first two in Power Pivot, and I had no problem creating data models for all 3 and getting them into Power Pivot.
The "center" is the vendor spreadsheet. The issue is when I try to link the bank transactions to the vendor spreadsheet. The bank transactions only contains 4 columns, i.e. - date, amount, check #, description. I tried to "connect" the dates because obviously, on the bank side, I only see the net transactions related to the vendor which is how I can identify the detail on a specific date. For example, the vendor may have multiple products, some fees, then tax, and the net or total of the invoice. I can use the net or total amount from the bank on a specific date to find the detail which goes below from the vendor on the same date. The problem I get is the duplicate error when I try to link the vendor and bank table. I believe the issue is with the "Check #" column from the bank because it contains mostly blank or "null" cells as only a few checks are written from time-to-time. There are no blank rows, however, I can have a couple hundred transactions in a single month, and only 4-5 actual checks so I will have rows and rows of transactions with no check number or a null field, then a check #, and then dozens more transactions without a check #, etc.
Since it is possible that over a number of months, there can be multiple transaction from the vendor that are for the same amount (although rarely), I need to use the dates to be able to match the correct amount with the vendor detail for the correct date, etc. I am not sure how to fix the problem or perhaps there is a different issue that I am not seeing, but the blank check field is the only one that occurs to me. Does it make sense that a "null" field in a table would be the cause of the "duplicates" issue?
I actually could replicate the information from the vendor table without the bank data, but there is another problem I have been having using Pivot Tables in general and Power Pivot that I have not been able to figure out so the Power Pivot so far has seemed to work best. I have looked at DOZENS of the Mr. Excel and ExcelisFun videos, but I have not been able to figure it out. I would rather not go into another lengthy explanation on that issue.
I hope you can help me with this issue. Thank you again!
