4 Replies Latest reply: Oct 5, 2013 11:58 PM by Barry
jwoods007 Level 1 Level 1 (0 points)
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)
  • Barry Level 7 Level 7 (29,210 points)
    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.
    Vendor list

    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
  • jwoods007 Level 1 Level 1 (0 points)
    You guys are amazing! Thank You
  • keyboardman3244 Level 1 Level 1 (0 points)

    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.

  • Barry Level 7 Level 7 (29,210 points)

    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