I have been working through a project and have hit a bit of a snag. I have looked in the forum search but can’t locate an answer for this issue…
The project that I have been working on takes raw data from a database and organizes it to summarize specific categories (salary, travel, materials, ect). I can’t figure out how to attach an example spreadsheet on this forum so I will try to explain the structure.
The spreadsheet has a linked data table on one sheet (called SummaryData) and a series of account tabs. Each account has their own tab. Within the account sheet, there is a column for each month of the year and a row for each of the categories. I use the sumproduct calculation to parse through the SummaryData information and populate the corresponding month and category. This works great! FYI – I will share if you shoot me your email address…
Unfortunately, I am now being asked to provide detail for each of the summarized lines. I have inserted lines between the categories and have grouped the rows accordingly (you cant see the grouping below but it is comprised of the lines that have orange in them), leaving the summary row for now (checks and balances reasons). As the existing data extract does not have this level of detail, I have had to find the correct data table -- which is now done. The issue I am having is this. For the salary area (as an example), the extract contains hundreds of names. I need a way to parse through the extract and then auto-extract the unique names on the spreadsheet in the appropriate cells. Here is an image to show you the raw data (left) and where it is going (right). The names themselves will become a parameter for the sumproduct calculation – hence my need to make this an auto-extract.
[/URL][/IMG]
I have over 1600 accounts and require an automatic way to populate the names of the people who are being paid by account.
If you have an idea of how I can grab the unique values automatically and drop them in the appropriate cells, I would be grateful.
Jim
The project that I have been working on takes raw data from a database and organizes it to summarize specific categories (salary, travel, materials, ect). I can’t figure out how to attach an example spreadsheet on this forum so I will try to explain the structure.
The spreadsheet has a linked data table on one sheet (called SummaryData) and a series of account tabs. Each account has their own tab. Within the account sheet, there is a column for each month of the year and a row for each of the categories. I use the sumproduct calculation to parse through the SummaryData information and populate the corresponding month and category. This works great! FYI – I will share if you shoot me your email address…
Unfortunately, I am now being asked to provide detail for each of the summarized lines. I have inserted lines between the categories and have grouped the rows accordingly (you cant see the grouping below but it is comprised of the lines that have orange in them), leaving the summary row for now (checks and balances reasons). As the existing data extract does not have this level of detail, I have had to find the correct data table -- which is now done. The issue I am having is this. For the salary area (as an example), the extract contains hundreds of names. I need a way to parse through the extract and then auto-extract the unique names on the spreadsheet in the appropriate cells. Here is an image to show you the raw data (left) and where it is going (right). The names themselves will become a parameter for the sumproduct calculation – hence my need to make this an auto-extract.
I have over 1600 accounts and require an automatic way to populate the names of the people who are being paid by account.
If you have an idea of how I can grab the unique values automatically and drop them in the appropriate cells, I would be grateful.
Jim
