Need help tallying up certain workbook data

[tperk100]

Not sure where to ask this question so here I am. I am mostly an Excel novice with abilities to create basic sheets, and have created a 10 sheet workbook. I need help creating a summary sheet that will search certain workbook columns for all member names, and the items assigned to those names, and then create an alphabetized summary listing of names and assignments.

I have attached the workbook, so I can be more specific.

Look at the sheet labeled "Assigned"

On this new "Assigned" sheet I want to show all names alpha sorted by last name from all columns labeled "Owner" from every other sheet. If a new name in inputted under "Owner" on any sheet then I want that it to automatically show up in the Assigned sheet under the column labeled "Member". For multiple entries of the same name I only want one entry for that name on the Assigned sheet.

Now I also need automatic tallying of all the "Stand #" columns from all sheets to show all specific "Assigned Stands" for each Member. I have manually entered a John Smith example of what I am trying to automatically accomplish on the Assigned sheet.

Any help will be greatly appreciated.

[mikeTRON]

So what you have done is created a data storage mechanism that is more like a reporting mechanism.

Ideally the best way to store data is in ONE table, then you can report off that ONE dataset as many ways as you want (i.e. create all the worksheets to report) and then you can have ONE pivot table on that data. This would all be very easily attainable with simple formulas or pivot tables.

Now for what you have created you have to FIRST aggregate all the data to one spot THEN report on it like you want for the Assigned sheet. It is doable, but exponentially more work and that much more difficult to manage as changes are necessary in the future.

In processes like these in the past where the owner/user doesnt want to change the way they are recording data (or cant, as they are given the data from third parties in nonsensical manners) I have created automated ways to aggregate the data but then it requires someone to manage the VBA scripting as well as the spreadsheet in case of any needed changes.

[tperk100]

Mike,

Thanks much for your response, but you are talking WAY over my head. As I mentioned I am a novice.

[tperk100]

Duplicate. Cannot figure out how to erase this.

[candybg]

Your problem has several parts, as explained by MikeTRON.
To maybe help you break this down into novice language:
It would be best if all your data was in a single sheet. You could recreate your data in a single sheet or use a "code" that can do it. If you aren't familiar with VBA, it might be best if you could get your data into a single sheet. I would be glad to suggest a template or list of columns to use. Then other formulas or a simple Pivot Table could pull your info into the format you want.


Hope this helps!

[mikeTRON]

Your problem has several parts, as explained by MikeTRON.
To maybe help you break this down into novice language:
It would be best if all your data was in a single sheet. You could recreate your data in a single sheet or use a "code" that can do it. If you aren't familiar with VBA, it might be best if you could get your data into a single sheet. I would be glad to suggest a template or list of columns to use. Then other formulas or a simple Pivot Table could pull your info into the format you want.


Hope this helps!

^what Candy said.

Essentially your problem is actually a very complex one where a standardized dataset would be MUUUUCH easier for you to manage.

[tperk100]

Candy,

Thank you and I accept your offer to help. I created this book, but I have no idea of how to go about doing what you fellows have suggested. What, if anything do you need from me?

Thanks again.

[candybg]

I added a DATA sheet with columns that I think gather all the data. I formatted the data as a TABLE (to make future calculations simpler). Just add data below the last row (currently row 34). I copied and pasted the data from your first two tables, and added the header info (guessing they might be called "Lease" and "Straw Boss").

There are a few rows that were blank - I highlighted yellow. There didn't seem to be any Stand listed. You may or may not need these rows (guessing they were placeholders to keep data lined up ). If these do not reflect actual data, they should be deleted.

[candybg]

For some reason the "paperclip" to attach the file is not working, so at this moment I'm unable to attach my version 2 of your file.

Here are the column headings I used in row 3 (A3 to I3):

Lease Straw Boss Stand # Marked GPSd Trail Marked GPSd2 Owner Comments

[tperk100]

Could you send me the file at tperk(10x10 without parenthesis)AAATTTT Gmail Dot Com?

[candybg]

file has been sent

[AliGW]

How to attach a sample workbook:

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[candybg]

Thanks. Maybe it will show up as attached this time.