Spreadsheet vs Database

[lisagravy]

Is there a way to summarise items / sort items in a worksheet in which the data is imported from an excel spreadsheet, rather than created from the drawing as a database? 

 

When I'm trying this, the sort / summarise / sum items all appear to be greyed out.

[Pat Stanford]

There is no built in way to sort spreadsheet data, only database rows.

 

Could you sort the data in Excel before importing?

 

A script could be written that would be able to sort spreadsheet rows. Would a single column sort be acceptable? Moving entire rows? Or do you need to be able to reorder just a portion of a row?

[lisagravy]

@Pat Stanford I don't think I can do it in excel either, though I'm not an excel whizz and I maybe could with some research!

A quick google tells me I need to learn PIVOT tables, which look awful 😂

 

I have 200+ entries / rows, where one of the columns is text responses to a consultation.

Many of the text responses are the same, so I'd like to summarise the duplicates, so that on the drawing I just get each response once. 

 

I've done this before with the database function lots and it's really helpful.

[Pat Stanford]

Excel for Mac 365.  Data Menu or Data bar.  Select the area you want to sort. Choose Sort.  The A->Z and Z->A options are the most basic. The ZA/AZ Sort button lets you select one or more columns to sort by.

 

 

[Tamsin Slatter]

Hi Lisa

I, too, have a terror of pivot tables, but our UK team accountant assures me they are really not that scary!

https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

 

When you've sussed it, perhaps you could teach me???

[lisagravy]

@Pat Stanford Thanks! I can sort A>Z in Excel, just can't summarise unfortunately.

 

@Tamsin Slatter Thanks for the link! I'll give it a go 🙈 My brother's an accountant so I'll maybe see if he'll do it for me for the price of a beer!

Edited September 21, 2020 by lisagravy

[Pat Stanford]

That is true. You will have to use Pivot Tables to Summarize.

 

I am not certain if that summarization will carry over. It probably will is you are careful and only copy the text instead of the formulas.

[lisagravy]

Pivot summary imported fine! 😊