Copy cell contents to another table without Duplications

Hi jandscanada,

It is possible to do what you are asking. But if I were approaching this problem I would find it easier to separate data entry from the display tables, rather than mixing them all together. It could look something like this:

There are no formulas in the Data table. Then there is only one formula in the body of each of the other tables that extract views of your data.

'Player by week' table, in B2 copied right and down:

=SUMIFS(Data::$D,Data::$C,$A2,Data::$A,B$1)

'Participant by week' table, in B2 copied right and down:

=SUMIFS(Data::$D,Data::$B,$A2,Data::$A,B$1)

'Participant by player - week' table, in B2 copied right and down:

=SUMIFS(Data::$D,Data::$B,B$1,Data::$C,$A2,Data::$A,$A$1)

You can clone this table and enter different week numbers in A1.

etc., for other views of your data.

To get a list of distinct player names (i.e. no duplicates) you could do something like this:

Formula in B2, copied down:

=COUNTIF(A$2:A2,A2)

Formula in C2, copied down:

=IF(B2=1,A2,"")

SG

Here's another way to get a list of distinct values.

First sort the list with duplicates alphabetically.

The formula in A2 of the Distinct Table simply refers to A2 of the Players table. It "seeds" the first value of the list of distinct values.

Then in A3, use this formula, and copy down:

=IFERROR(T(INDEX(Players::A,MATCH(A2,Players::A,1)+1)),"")

This only works properly if the orginal list with duplicates is sorted ascending.

SG

Hi James,

You have a nice looking spreadsheet! However, you might actually save time in the end by setting things up from scratch so that you: 1) separate data input as much as possible from your display tables, 2) avoid merged cells (which are trouble).

Nevertheless, in Numbers 3 there is a way to simplify the entry of player earnings so you only have to do that once and the values are read into your Table 1 in various places. It could look like this (I've put letters and numbers in where you have headings to make the ranges easier to follow:

The formula in A1 of the Earnings Input table, copied down, is:

=INDEX(UNION.RANGES(0,Table 1::$C$2:$C$7,Table 1::$F$2:$F$7,Table 1::$I$2:$I$7),ROW())

This formula (which works in Numbers 3 but uses a function that Numbers 2 doesn't have) reads the three ranges in your Table 1 for event 1, and lays out the values in a vertical list in the order that it finds them, including duplicates.

The formula in B1, copied down, is =COUNTIF(A$1:A1,A1). This is similar to the "Occurrence" column in a previous post.

The formula in C1, copied down, is =IF(B1=1,A1,""). This shows the player name if this is the first occurrence, otherwise inserts a blank.

Column D is where you fill in the player earnings that you want copied to various places in Table 1.

Then, in cell D2 of Table 1, you could put a lookup formula like this, copied down to D7, and also into G2:G7, and J2:J7:

=VLOOKUP(C2,Earnings Input::$C:$D,2,0)

This looks up the values from the last two columns of the Earnings Input table.

This should save you some headaches with duplicate data entry.

When you have time, though, you might consider using a simpler and more flexible data table plus summary tables approach. You can find more examples in the templates via File > New.

SG

Maybe someone here knows how to do a UNION.RANGES equivalent in Numbers 2.

Of course, there is also a semi-manual method that can still save you some time.

Instead of the formula in column A, just copy and paste successively from C2:C7, F2:F7, I2:I7 into the column. Then use the formulas in B and C to "dedupe." Then enter the values in D, and the VLOOKUP will pull them into Table 1.

Once you've got them as you want in Table 1, select the range, command-c to copy, then shift-command-v to Paste Formula Results, removing the VLOOKUP formula.

SG

I think what I would do is create a Table 2 that has all the names from Table 1. It looks like there is a pattern to where the names are in Table 1 so it is possible to create a formula that would populate Table 2 with those names.

With all names in one long list, you can use one of the methods mentioned in various other posts to get the unique names with no duplicates into Table 3. I would think a search of the word Duplicate on this forum would result in some of those posts.

If you enter the earnings in Table 3, you can use LOOKUP functions throughout Table 1 to look up the earnings for each player. If, instead, you enter the earnings in Table 1, you can use LOOKUP functions in Table 3 to bring over the scores for each name. In this second case, though, if there is a duplicate name in Table 1 and they have different earnings, only one will be looked up for Table 3.

Hi James,

I'd do a bit of reorganization here. One of the better working principals is to enter data once, then distribute it using formulas.

Start with a data entry table; one header row, three header columns (P(layer )No., Name and Total earnings), and one column for each week's event.

The second table is your Table 1. Here's a reduced version for demonstration purposes.

The only entries made 'manually' in this table are those in the header rows (except the two totals), in the header column, and in the column of numbers in the two narrow columns under the participants names.

On my table, I used a stepper cell, range 0-100 to set these numbers, then a VLOOKUP formula to collect the player Name and Winnings associated with that number from Table 1. One caution here—I found it fairly easy to miss the stepper controls and accidently open the Formula Editor and affect the formulas in the column to the right of the stepper.It might be adviseable to place a buffer column to the right of each column of stepper cells to lessen the likelihood of this happening.

I moved the weekly totals out of the column containing the winnings for the individual players to simplify the calculations of the current winnings totals for each participant. If you want to keep them in the same column as the individual player winnings, =SUM(D)/2 should give correct results for James without a lot of fuss.

Instructions below all refer to my sample tables, Cell positions will require revisions for your similar table.

Formulas:

Table 1 has one formula, entered in C2 and filled down: =SUM(2:2)

Table 2 Has several formulas:

B2: =SUM(B)

E2: =SUM(E)

B4: =IFERROR(VLOOKUP(C,Table 1 :: $A:$B,2,FALSE),"Choose ->>")

Confirm the entry by clicing Accept (green checkmark), then Copy the cell.

Click E4 and Paste. The result will be the formula below:

E4: =IFERROR(VLOOKUP(F,Table 1 :: $A:$B,2,FALSE),"Choose ->>")

I Filled these down through the rest of columns B and E, then replaced the copies in the Week n Total rows (7 and 11 in my example) with the ones below.

B7: =SUM(D4:D6)

As above, copy the cell after confirming the formula, then Paste into cells B11, E7 and E11.

D4: =IFERROR(VLOOKUP(C,Table 1 :: $A:$F,4,FALSE),"-")

As above, copy the cell, then Paste into cells G4, D8, G8, D12 and E12.

The C in the formula will automatically adjust (to F) for the formulas in column G. The 4, which determines the column from which the amounts are retrieved, must be edited for each group of formulas.

D2 and G2: Leave the 4 as is. Fill down to row 6 (last row for week 1)

D8 and G8: Change 4 to 5. Fill down to row 10 (last row for week 2)

D12 and E12: Change 4 to 6. Would be filled down to row 14. (last row for week 3)

Note: While it is not intended that the entry table (Table 1) be sorted, it is possible to do so without breaking the formulas, provided the player numbers in column A are fixed values.

Your two leader boards appear to be working. The only suggestion I'd have there is that the two of them could be combined into a single three column table.

Regards,

Barry

Hi James,

Here's another plug for reorganizing and separating data input as much as possible from output, removing merged cells and using a simple grid structure in tables, and letting the spreadsheet interface do as much of the work as possible to reduce the need for a web of lookup formulas.

Here is a bare bones view of how this approach might work, using Barry's example dataset and incorporating the Canadian "idiosyncracy".

'Player Earnings' and 'Picks' are input tables. Just enter a new line as needed with the relevant information in the unshaded columns. This may seem less compact and efficient than a row-column input matrix but simplifies life and allows you to apply filters efficiently to look at your data. You'll see that approach used in some of the Apple templates.

The shaded column in 'Player Earning's calculates the "Canadian special" adjusted earnings with a formula like this, in E2, filled down:

=IF(D2="CC",C2×4,IF(D2="CNC",C2×2,C2))

The shaded column in 'Picks' contains this formula in D2, filled down:

=SUMIFS(Player Earnings::$E,Player Earnings::$B,B2,Player Earnings::$A,C2)

From those two input tables you can pull out data in many different ways into output tables.

Here, in 'Participants by Week', the formula in B2, filled right and down, is:

=SUMIFS(Picks::$D,Picks::$B,$A2,Picks::$A,B$1)

In 'Participants Ranking' the formula in C2, filled down, is:

=SUMIFS(Picks::$D,Picks::$A,B2)

To get the ranking, just Sort Descending on column D. I have =ROW()−1 in A2, copied down, to keep the numbers in the Place column in order after a sort.

With the data set up like this, many other output tables become possible without much trouble, and can be gussied up as needed.

And, taking advantage of capabilities built into the spreadsheet user interface, to get a list of players in, say, week 2, just filter on the Week column, etc., etc.

I did this in Numbers 3, but this approach should work equally well in Numbers 2.

SG

To get the ranking, just Sort Descending on column D.

I meant column C in 'Participants Ranking', not the non-existent column D. Sorry for the typo.

SG

Hi Jmes,

Just dropping in to say I've not forgotten this thread. Too many other things going on here to get back to in in the last couple of days. Maybe by the weekend.

Regards,

Barry

Hi James,

Looking good.

Question #1: Can I "lock" the Player and Earnings cells so the formulas aren't accidentally deleted? Barry mentioned a "buffer column" but I wondered if there was another way around that.

In Numbers you can lock a table but not just specific cells within a table. Hence the value of separating data input and output as much as possible.

Question #2 (Don't put too much thought into this one as it is WAY out there) Could I somehow have a script (?) fill those amounts into my Players Earnings Table from the website?

Depending on the format of the web page you can get data and paste it into Numbers. If the data is in table format you can try copy and paste. If it is not in table format there are ways to "scrape" the data and put it in table format for importing into Numbers. One way is to use a library called Beautiful Soup (a free download) with a Python script (Python is already on your Mac). With Numbers 2 you could further automate the fetch and import using AppleScript. With Numbers 3 that (so far) is not a possible. All of this, of course, takes time to implement so you're probably best off with the existing manual method, unless, of course, the web page happens to have a table that you can just select, copy and paste.

SG