My home banking App can export to Numbers. It will give me a table with date, category, description and amount. I want to extract all the categories automatically in a second sheet. How can I do this?
Example screen:
How to select or autofill all distinct elements from a column in Numbers
numbers
Related Solutions
If a field in a CSV file begins with an apostrophe ('), both Excel and Numbers will treat the field as text, and not render it with any numerical formatting.
Your CSV files appear to try to force text rendering through enclosing number fields in double quotes, but Numbers and Excel don't seem to take the hint that double quote numbers should be treated as strings. The trick seems to be to modify the CSV files so they use the "starts with a single apostrophe" trick rather than the "contained in double quotes" trick for numeric fields, but keep the double quotes for text fields involving punctuation (including commas, quotation marks, line breaks, etc).
To process your CSV files so they do this, you can create an Automator application.
In Automator, create a new Application.
It will have a single action: Run Shell Script (passing input as arguments). Here is the script:
for f in "$@"
do
perl -pi -e "s/\"\"([0-9A-Za-z: \.\-+]+)\"/'\1/g" "$f"
done
Save the resulting application on your Desktop. Drop any CSV files you want to use in Numbers on the application icon, and they will be converted so Numbers should keep the numbers in the fields as literal numbers, and not format them or throw away information.
Back up your data before trying this; it is possible that a particularly oddly constructed string in a record field could throw off the results here.
Here is the solution that worked for me. The original solution was provided in a question I posted in Apple Support Communities.
I am posting it here, as an answer to anyone the might arrive here looking for the same solution.
The 2 Cell Functions:
=IF(COUNTIF(A$1:A2, A)=1, MAX(B$1:B1)+1, "")
=LOOKUP(COLUMN(), First::$B, First::$A)
Original Answer: https://discussions.apple.com/thread/5960924
Related Question
- How to set Numbers to automatically copy a row of data from one sheet to another sheet
- Apple Numbers: how to change a date format to a number format
- Numbers: Creating a Summary table based on values from different table
- Numbers Sum of all values in a column where another cell within the row matches text
- How to interact with a Numbers sheet via AppleScript
Best Answer
I can't do it in Numbers. Numbers has some interesting layout tools,but its utterly pathetic for anything signficant.
If you export as an excel sheet, save to google drive, then open in google sheets it's easy.
Cut and paste seems to work between Numbers and Google sheets too. Values only, AFAIK.
To get a list of unique categories: In Sheet 2 enter the following formula at the top of the column:
=Unique(sort(Sheet1!B:B))This will autofill down. Now if you want them as columns instead:Create a third sheet. in A1 enter the formula
=Transpose(Sheet1:A:A)If this happens a lot, leave it in place.
If it's a oneshot, then select, copy, paste special, paste values only.