+ Reply to Thread
Results 1 to 4 of 4

adding additional products to individual loc from a division source

  1. #1
    Registered User
    Join Date
    04-19-2010
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    92

    adding additional products to individual loc from a division source

    I have a spreadsheet #1 as follows:

    column A - Loc
    column B - Division
    Column C - item name
    Column D - Sales units

    I have another spreadsheet #2 as follows:

    Column A - Division
    Colum B - Item name
    Column C - Sales units

    I am trying to add any items not already listed on spreadsheet 1 from spreadsheet 2.

    IF spreadsheet 1 column b = Spreadsheet 2 column A, if Spreadsheet 2 col b is not on Spreadsheet 1 column C, add to Spreadsheet 1 column C item name and Sales units(column d).

    I can work with just adding the item names missing from spreadsheet 1 and vlookup sales.

    Cant get to work?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,615

    Re: adding additional products to individual loc from a division source

    I think it will be not especially easy formula, as you mix two types of conditions:
    div exual to one of already present,
    name <> for any name for (I expect for THAT division, not just different for any name listed)

    (side comment: a sample file with expected output and carefully prepared representative input data would be quite helpful).

    I think it would be much easier to do with macro rather than formula. See for example:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    04-19-2010
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: adding additional products to individual loc from a division source

    I cannot attach an example but your macro only added one location missing division items, I need to add for 5,000 locations from 277 divisions.

    Below is the spreadsheet I am trying to upload



    loc div item sls
    12 239 a 27.183123
    12 239 b 64.640295
    12 239 c 66.593562
    12 239 d 66.245799
    12 239 e 75.393198
    12 239 f 37.667073
    15 239 a 27.183123

    I want formula to yeild:


    loc div item sales
    12 239 a 27.183123
    12 239 b 64.640295
    12 239 c 66.593562
    12 239 d 66.245799
    12 239 e 75.393198
    12 239 f 66.593562
    12 239 g 27.183123
    12 239 h 66.245799
    12 239 i 75.393198
    15 239 a 27.183123
    15 239 b 64.640295
    15 239 c 66.593562
    15 239 d 66.245799
    15 239 e 75.393198
    15 239 f 66.593562
    15 239 g 27.183123
    15 239 h 66.245799
    15 239 i 75.393198


    spreadsheet 2:

    div Package sales
    239 a 27.183123
    239 b 64.640295
    239 c 66.593562
    239 d 66.245799
    239 e 75.393198
    239 f 66.593562
    239 g 27.183123
    239 h 66.245799
    239 i 75.393198
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,460

    Re: adding additional products to individual loc from a division source

    This works for the data that is shown in the file:
    1. Make a unique list of locations (F3:F8) using: =IFERROR(INDEX(A$2:A$8,MATCH(0,INDEX(COUNTIF(F$1:F1,A$2:A$8),,),)),"")
    2. Count the number of items in the table on sheet 2 (F11) using: =COUNTA('2'!A2:A15)
    3. Populate the Locations (F14:F31) using: =INDEX(F$2:F$8,ROUNDUP(ROWS(A$1:A1)/F$11,0))
    4. Populate the Div, Package and Sales columns (G14:I31) using: =IF($F14="","",INDEX('2'!A$2:A$10,COUNTIFS($F$14:$F14,$F14)))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filtering the highest revision number for a individual products
    By bob.m in forum Excel General
    Replies: 8
    Last Post: 12-16-2019, 03:28 AM
  2. [SOLVED] I need to add additional criteria to an array Sumif that counts individual values
    By dc0822 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-20-2015, 04:24 PM
  3. Adding up duplicate products with additional value.
    By thelongboardstore in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-14-2015, 07:28 PM
  4. Replies: 1
    Last Post: 09-16-2013, 04:49 PM
  5. [SOLVED] Adding two countifs with division
    By Lifeseeker in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-07-2012, 04:07 PM
  6. Adding additional source data to a chart
    By Mavis Cruet in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 10-21-2011, 03:51 AM
  7. Adding Products
    By carsch in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2006, 12:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1