how to transfer data from one table to another without duplicates

Question

Level 1

0 points

how to transfer data from one table to another without duplicates

This is what I use to quote bathroom remodels. The first thing I do is completely fill in the Master Item List with every Product or Service we can think of to do the project. The Type column has a pop-up menu which includes Combo because some Contractors don't separate the Product from Labor. The Category Totals Table is useful but since some Contractors provide Product and Service at the same time on one invoice I don't get accurate breakdowns for generating my invoices.

What I would like is to transfer all Vendor names included in the project to the Vendor Totals Table but without any duplicates. Some Contractors might perform services in several categories. I've read many listings in the forum and am just not seeing the answer.

I have a sample file ready to send. I don't see a way to send it with this message. First Post! Sorry!
Thank You
Jeff

MacBook, Mac OS X (10.6.3)

Posted on Apr 10, 2011 10:37 PM

Reply

Answer 1

Barry

Level 9

59,867 points

Apr 11, 2011 12:27 AM in response to jwoods007

jwoods007 wrote:
What I would like is to transfer all Vendor names included in the project to the Vendor Totals Table but without any duplicates.

Hi Jeff,

Welcome to Apple Discussions and the Numbers '09 forum.

If your Vendor names (including duplicates) are all in the same column of the source table, it shouldn't be difficult to transfer them to a second table using the technique described below.

For the example, the source table is named Main and has one header row, the Vendors are listed in column C (starting at C2), and column B is used as an index column. Note that to use VLOOKUP, the index column must be to the left of the Vendor column.

The index is generated using the following formula in B2, and filled down to the end of the column:

=IF(COUNTIF(C$1:C2,C)=1,MAX($B$1:B1)+1,"")

On the second table, Vendor List, the following formula is used in Column A (starting at A2) to collect the indexed vendors from the Main table"

=IFERROR(VLOOKUP(ROW()-1,Main :: B:C,2,FALSE),"")

IFERROR is used to trap the "couldn't find" error in the 'empty' rows of Vendor List.
'FALSE' displays as "exact-match" in the formula, and prevents the rpeated listing of the last vendor that would be created by 'close-match'.

Regards,
Barry

Reply

Answer 2

jwoods007 Author

Level 1

0 points

Apr 11, 2011 5:53 PM in response to Barry

You guys are amazing! Thank You

Reply

Answer 3

Oct 5, 2013 8:09 PM in response to Barry

Hi Barry, I'm trying to do the same thing but my table has two columns which needs to be transferred. I've tried doing this by replacing your "COUNTIF" with COUNTIFS but no luck. What am I missing. Any help would be greatly appreciated.

Reply

Answer 4

Barry

Level 9

59,867 points

Oct 5, 2013 11:58 PM in response to keyboardman3244

Hi kbm,

Moving to a two column issue is a sufficiently different question to merit a new discussion. Could you use "Start a new discussion," and post a more detailed description of what you're trying to do? A screen shot of your original table and of a mockup table showing the expected result would also be useful.

Regards,

Barry

Reply