TwoSocksWrites
Level 1
(8 points)
Q: Automatically make lists
I want column A on sheet 1 to automatically fill in a list of each date that appears one or more times in column A on Sheet 2.
I was able to do this in Google Doc's using an array. How is this accomplished in Numbers?
iMac, iOS 10
Barry
Level 7
(32,714 points)
Hi TSW,
You can do this with a formula or with a script.
With the formula, you will need an auxiliary column (which may be hidden) in which to build an index of the distinct dates that are in column A. I've put the index into column B below, but it can go into a column at the far right of your table if you wish. The index is used to tell LOOKUP which dates to retrieve to build the list of distinct dates shown in column D. If desired, this lis can be constructed on a separate table.
There are two formulas used on the table, each entered into row 2 of its column, then filled down to the end of the table.
The formula in column B is used to create the index list:
B2: =IF(COUNTIF(A$1:A2,A2)=1,MAX(B$1:B1)+1,"")
The formula in D2 uses LOOKUP to retrieve the indexed copy of each date:
D2: =IF((ROW()−1)>MAX(B),"",LOOKUP(ROW()−1,B,A))
The first part of this formula is a switch that determines if the formula has reached the last indexed data, and turns the calculation part off at that point.
If you would refer to use a Script (saved as an Automator Service), you will find a link to download one written by SGIII in his post in . Don't be put off by the discussion's title/question—the service does not remove anything from the document; it makes a list omitting repeats of any values in the list, then places the list on the Clipboard, ready to be pasted into a column in the same document or elsewhere.
Regards,
Barry
