+ Reply to Thread
Results 1 to 6 of 6

formula to extract unique data and adding drinks pack size description only

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,049

    formula to extract unique data and adding drinks pack size description only

    I have a workbook with 2 sheets calculations and summary .

    The summary sheet is a consolidation of column of K as criteria of column C, D,E,F of calculations sheets.

    On summary sheet from columns J to O is a summary sheet of same data from column A to F but what differs is the packsize Description of column L .

    I have take example itemised no 1 which on autofilter on calculation sheet is listed twice in packing size description but product reference are not same

    HTML Code: 
    
    22087000	LIQUEURS	6*11*70CL	FR
    Looking a formula to extract data in packsize header to read 12*11*70cl all data remain same as per dummy file .

    Note it should only take data only if there data in packsize header and sum but if there is not data in packsize header formula should ignore e.g toasted bread , crackers ,biscuits .

    Note the data will be scattered in the sheets.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: formula to extract unique data and adding drinks pack size description only

    You have lost me completely !! Can you try to explain again?
    Glenn



  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,049

    Re: formula to extract unique data and adding drinks pack size description only

    All data on calculation and summary sheet remain same , the only additional data that need to be extracted are from J to O on summary J1 to O1 are headers same as A to F headers.

    The only difference come from columns L pack size description .

    If you make autofilter on calculation sheet column Itemised column L no 1 you see two items appear as per screen shoot

    the second screen shot is to add the packsize

    6*11*70cl twice but reference product are different in column A of calculation sheet =12*11*70 cl reflected in column L .

    It involves only drinks products in which the packing size is shown on column E on calculation sheet and columns L of summary sheet

    If packing size description of columns E is blank on calculation sheet and columns C of summary sheet then it should not be taken in account as shown in column L of summary sheet
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: formula to extract unique data and adding drinks pack size description only

    Is that a different explanation? It still doesn't make a lot of sense to me... so I have made a guess. I have deleted some clutter, so that I can see only what I need to see. In my opinion, you are making this MUCH more difficult by using * as the size separator. I have changed them all to x. That may/may not cause you a problem. I have added a blank row above the result area. This is needed. Don't remove it.

    So, is this what you had wanted? One helper column required. I can explain if needed, but don't see the point in explaining a blind guess!! Different shades = different formulae.
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,197

    Re: formula to extract unique data and adding drinks pack size description only

    This is also with helper column
    please see the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,049

    Re: formula to extract unique data and adding drinks pack size description only

    @Glenn & nflsales thank you for help, the formulas achieve the expected results .

    I will try to understand the approaches and revert back if i need additional info for the formulas as it is complex .

+ 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] formula or vba to extract complex specific data in transpose mode on summary sheet
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2017, 08:52 PM
  2. [SOLVED] Formula to Extract Unique Values/Remove Duplicates Across Multiple Columns of Data
    By Lanceh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2017, 09:07 AM
  3. Formula to Extract Unique Data
    By Legalhustler in forum Excel General
    Replies: 20
    Last Post: 07-16-2015, 11:23 AM
  4. [SOLVED] Pull stock description after tile size extract
    By makinmomb in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2015, 02:06 PM
  5. [SOLVED] Pull stock code , description and pack from sheet1
    By makinmomb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2014, 03:23 PM
  6. [SOLVED] Help with Formula to Distribute evenly based on pack size of item...
    By blue84rain in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2014, 05:53 PM
  7. Excel 2007 : Changing Quantity according to pack size
    By Navneet in forum Excel General
    Replies: 12
    Last Post: 05-19-2010, 07:45 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