Forum Discussion
Need Assistance with Pivot Tables
I am trying to create a directory of businesses for a category of social services for a local NPO that I am volunteering at, and I would also like to include a Spanish section due to Spanish-speaking needs in the population. After initially giving up after trying to create a data sheet which would then be transposed onto two worksheets (and just manually doing it through Word), I saw Pivot Tables and have renewed hope.
HOWEVER, the problem that I ran into while creating is that Tabular is the best mode, BUT it automatically groups like what is attached (FYI: Things like Nos being grouped as one big row of No).
I have been looking for a solution to this for over 30 minutes, but I haven't found any solutions that don't create additional issues. Another solution that I am open to is just using formulas within another field to give an asterisk and/or dagger for yes and no questions (as they may fit better on paper). I am also looking at trying to group or sort by zip code or City, but that doesn't seem to work as well.
Is there an easier way to create a spreadsheet for directory that is feasible to print and to distinguish if there is bilingual staff which then can be printed out for clients and staff. Or is a Pivot Table in English then in Spanish my best solution and if so, how do I overcome the problems illustrated above? (Due to limitations and regulations with regard to federal and state laws for this type of NPO, coding and macros are NOT a viable solution.)
Thanks!
Hi Jankysoft ,
How are you going to shoehorn into a solution that involves an additional file, when you can do a query on the same table that is already in your workbook?
You can do a query by Table/Range, image below I have a table/range with 53,000+ rows:
I remove the columns I do not want in the report or pivot table, I can rearrange the columns, I can filter out any rows I do not want to be in the report, and if there is additional information, all I have to do is paste it at the bottom of the table and the excel table will just dynamically (automatically) add it into the table and automatically propagate into the report (via the refresh) button and bam! it's included the next time I have to print the report. Does that not sound like convenience to you?
cheers
25 Replies
Hi Jankysoft
You might be looking at power query for a solution, you can remove and add columns, and once you have the power query set up once, it can repeat the process just by pressing the refresh button, depending on whether you'll be doing a query by text/csv, or by folder where you just drop the file in a folder or replace the source file in the folder to update your query to generate the final list.
cheers
I saw that as a solution but that looks like the most overkill as it looks like I would be shoehorning in a solution by incorporating at least an addition file to compensate for Excel's lack of features.
Thank you for your assistance, but I think that this all just confirmed to me that Excel is heavily limited to...not sure what but I am trying to be diplomatic as my username already says what I think about the company.
Again, thank you anyways!
Hi Jankysoft ,
How are you going to shoehorn into a solution that involves an additional file, when you can do a query on the same table that is already in your workbook?
You can do a query by Table/Range, image below I have a table/range with 53,000+ rows:
I remove the columns I do not want in the report or pivot table, I can rearrange the columns, I can filter out any rows I do not want to be in the report, and if there is additional information, all I have to do is paste it at the bottom of the table and the excel table will just dynamically (automatically) add it into the table and automatically propagate into the report (via the refresh) button and bam! it's included the next time I have to print the report. Does that not sound like convenience to you?
cheers
- Do you want to send through an example of what you have now and what you want?
StoneKiwi Yes, thanks!
So I basically want to take this:
And have it automatically input into printable version like this: (with asterisks representing a conditional yes and nos)
As well as conditional for Spanish-speaking staff to output a table like this: (with asterisks representing a conditional yes and nos)
I want to do it in Excel, but the problem with using conditional formulas is that the Excel workbook becomes too clunky to use, especially at the NPO where they use all virtual machines.
So I wanted to see if I could use Excel through Pivot Tables or something else, and what would be the best way to go about that.
Thanks again because the tutorials online have created more problems than they have solved!
