+ Reply to Thread
Results 1 to 9 of 9

How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

  1. #1
    Registered User
    Join Date
    08-10-2015
    Location
    USA
    MS-Off Ver
    Window 7
    Posts
    4

    How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Good morning all,

    I am reaching out to the community for some assistance or guidance in regards to a fairly lengthy, involved document that I am working on. I have used this forum countless times and have found it to be a great source.

    I am self taught and this is my first post, so please be kind! I hope that I have detailed my predicament satisfactorily.

    I know that there is a quicker, more efficient method than what I am doing.

    Here is a breakdown of the of the task and attached is an example of the spreadsheet involved. This is related to retail and SKU #’s or “Stock Numbers”. I will refer to said stock numbers as “Style ID” as that is how they are represented on the spread sheet

    After an amount of time it becomes necessary for my company (we are a very large company) to do some house cleaning in regards to the number of SKU’s that exist within our system. All goods that we sell have an eight (8) digit code (can include zero’s) associated with them that we refer to as a “Style ID”. Each Style ID falls under a “Sub Class” which falls under a “Class” that is under the Department.

    Here is an example of the structure:
    Dept. ID
    Class ID: 08
    Sub Class ID: 8001
    Style ID: 00000367

    Within the Style ID there are a number of other Key “parameters” or “conditions” such as size range, pack quantities, and price.

    Style ID: 00000367
    Size Range ID: 000
    Inner Pack Qty: 2
    Perm Retail Price: 2.49

    Here is an example scenario:

    Lets say that I have one hundred (100) styles that are all within Department: 01 Class: 80 and Sub Class: 8001.
    Each style has a set of varying parameters (Size, Pack Qty, Price) within as stated above. My goal is to “merge” or “consolidate” these into as few numbers as possible. The key is that the parameters of the “From” Style and “To” Style MUST match. There may be cases where a number has no match and therefore is standalone. In other cases there may be any given amount, it could be ten (10) to one-hundred plus (100+) styles that could possibly be consolidated into a single Style ID.

    Attached is what I have so far. I cant add a document so I included an image link. The first list is the Raw Data (Blue). The second list is where I am at presently (Green) and you can see that I have indeed identified the “From” and “To” numbers, but how I there seems far too involved and overly complicated.

    http://imgur.com/a/5CIBI

    What I do is take the original list and throw it into Power Query. I then Sort in this order:

    1.) Department ID
    2.) Class ID
    3.) Subclass ID
    4.) Size Range ID
    5.) Inner Pack ID
    6.) Perm Retail Price

    I then add add 3, 4, 5, and 6 parameters together and multiply by the inner pack (I do not include the Style ID) to create a unique number for my identifier (I know there has to be a better way). I then do a countif to find my first instance, which I designate as the “To” style. I then have more IF’s so that each style that falls below the To style on my list is added under “To” style in a neighboring column. So, in the end I may be able to push something like three-thousand (3000) Style ID’s into two-hundred fifty (250) or one hundred thirty-five (it varies) Style ID’s. it basically ends up looking something like this:

    To me this seems far too involved an convoluted. We usually do this quarterly and it is very time consuming, so you can see why any help I can get simplifying this process would be amazing. In the end I would love to create a working copy and paste template with just a couple macros if necessary.

    I have an example document if needed.

    Thank you in advance!!!

    Crosslinks:

    http://www.mrexcel.com/forum/excel-q...r-numbers.html
    http://www.excelguru.ca/forums/showt...ed=1#post24657
    Last edited by amerikanzero; 05-26-2016 at 11:14 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Yes, sounds very convoluted. For me to try and help automate this, you WOULD have to provide a sample workbook, the sample would need to be MUCH shorter than your real data, but long enough to demonstrate the variability of the data. I'm guessing it would need to only include:

    1) An example of a single row that doesn't merge into anything else
    2) An example of a small set of rows that would merge
    3) An example of what you think is close to the largest possible size of data that would merge

    This data would go in the same as a single data set in a BEFORE sheet.

    4) An AFTER sheet that shows the results you would want from that small sampling, manually mocked up.

    The goal here is to merely have you work through the small data from BEFORE to AFTER and make sure all the hurdles are represented in the data.

    As you manually create the AFTER sheet, make clear logic notes to explain the results if they are not patently obvious from the visual representation.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Yes, sounds very convoluted. For me to try and help automate this, you WOULD have to provide a sample workbook, the sample would need to be MUCH shorter than your real data, but long enough to demonstrate the variability of the data. I'm guessing it would need to only include:

    1) An example of a single row that doesn't merge into anything else
    2) An example of a small set of rows that would merge
    3) An example of what you think is close to the largest possible size of data that would merge

    This data would go in the same as a single data set in a BEFORE sheet.

    4) An AFTER sheet that shows the results you would want from that small sampling, manually mocked up.

    The goal here is to merely have you work through the small data from BEFORE to AFTER and make sure all the hurdles are represented in the data.

    As you manually create the AFTER sheet, make clear logic notes to explain the results if they are not patently obvious from the visual representation.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

  4. #4
    Registered User
    Join Date
    08-10-2015
    Location
    USA
    MS-Off Ver
    Window 7
    Posts
    4

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Hey there J,

    Thanks for the quick response!

    When I click on the paperclip the box that shows up is too small to insert anything into. Also when I click on Manage Attachments nothing happens?

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Perhaps another browser?

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    http://www.excelguru.ca/forums/showt...ed=1#post24659
    http://www.mrexcel.com/forum/excel-q...r-numbers.html

  7. #7
    Registered User
    Join Date
    08-10-2015
    Location
    USA
    MS-Off Ver
    Window 7
    Posts
    4

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    Hello Pepe Le Mokko,

    Thank you kindly for your response. I apologize, I thought that I had added them at the end. Mr. Excel will not even let a new user add a link of any sorts unless they have 5 posts. I have since remedied that as well.

    I will reedit my first post to comply with the forum rules.
    Last edited by amerikanzero; 05-26-2016 at 11:42 AM. Reason: mistype

  8. #8
    Registered User
    Join Date
    08-10-2015
    Location
    USA
    MS-Off Ver
    Window 7
    Posts
    4

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    I have edited my original post

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers

    I sort of feel bad. Once people see the same question posted in multiple places, we tend to move on, no one wants to waste time on a question that may be solved elsewhere, you know?

+ 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. Replies: 0
    Last Post: 03-01-2015, 11:34 PM
  2. Replies: 19
    Last Post: 07-20-2014, 04:55 PM
  3. [SOLVED] Matching Unique numbers if a Group of cells
    By gtbear in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-25-2012, 07:31 AM
  4. Need help matching unique sets of numbers in two columns (uploaded example)
    By pavlic2002 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-25-2012, 02:51 PM
  5. Replies: 0
    Last Post: 07-16-2012, 04:01 PM
  6. Replies: 0
    Last Post: 01-04-2005, 08:32 AM
  7. Replies: 0
    Last Post: 01-03-2005, 06:53 PM

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