Need Assistance with Pivot Tables

[Jankysoft]

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!

[#2]

Do you want to send through an example of what you have now and what you want?

[#3]

@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!

[#4]

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

[#5]

@Yea_So 

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! 

[#6 · Accepted solution]

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

[#7]

Thank you for your reply once again!

Tried pivot tables and it didn't give me a problem this time for some reason!

Thanks again!

[#8]

Hi @Jankysoft ,

Power pivot and power query is like peanut butter and jelly, they compliment one another, i can create a dynamically (automatically) updating pivot table by leveraging power query and data model.  By using those two features in excel I can have a power pivot dashboard summary automatically update without recreating the pivot table from scratch, which is made possible by power query.  Power Query was designed to clean and prep data for use in data model and power pivot. 

cheers

[#9]

I do not see options to use Power Query without importing in a file still though. However, I am interested if this is not the case. Do you know a good article or LinkedIn / Youtube video to view on Power Query?

[#10]

Highline Excel 2016 Class 20: Power Query Import Multiple Excel Files & PivotTable Show Values As
https://youtu.be/90QdbiUTq7k

[#11]

Thank you so much! You have been great, sorry for all this back and forth!

[#12]

Hi @Jankysoft ,

Put your cell pointer anywhere in your table then click the icon in the red square:

[#13]

Hi @Jankysoft ,

no worries, don't hesitate to reach out if you have questions.

cheers

[#14]

Yeah, I already am using that for Pivot Tables. Thanks anyways again!

[#15]

Well since you are still on..one last question...hopefully quick. How do I skip over blanks in pivot tables? Filtering seems to screw things up so it isn't a good option.

[#16]

The video has downloadable practice files fyi

[#17]

Yeah thanks. If I ever work for a place that thinks Excels is a viable solution for what I will be doing, then I will definitely keep this in mind. As a person who will not be doing finance ends, hopefully not!

[#18]

Hi @Jankysoft ,

The power query editor, you can filter out any rows blank rows null values, you can also replace values...etc.

[#19]

I finally figured out the issue. The editor is not available on my operating system.

Thank you for all your help though. I will look into this on a Windows computer later!

[#20]

Oh you're using a mac? do you have o365 or stand alone program?