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

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.

UNABLE TO ADD ANYTHING> NO IMAGES> NO DOCS

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!!!

re: "UNABLE TO ADD ANYTHING> NO IMAGES> NO DOCS"

Can you say why?

You've clicked the Go Advanced button and clicked the Manage Attachments button?

There's some guidance here: http://www.excelguru.ca/forums/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

If you can't attach stuff (there may be a minimum post count of 5) then post a few brief messages here until you can. I'll delete those messages later.

…oh, and I see you've cross posted without links:
http://www.mrexcel.com/forum/excel-...s-w-matching-criteria-into-fewer-numbers.html
amerikanzero, for your information, you should always provide links to your cross posts.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

Take, this, seriously.

Hey p4cal,

Thank you for responding to my request. I honestly did not even think I would actually get one, what with all of the others the flood these forums!

Plea forgive me for my naiveté. I was unaware that I had cross posted as referenced in your first link.

Also, you are correct in that it does indeed look like I need 5 posts in order to be able to upload any documents.

I will do as you instructed in your email and then I will add the links to the other forms that I posted in as well as a document you can pay with.

You could at least add links when you are asked to.
Also cross posted at http://www.excelforum.com/showthread.php?t=1140896&p=4396002&highlight=#post4396002

…and at http://www.mrexcel.com/forum/excel-...s-w-matching-criteria-into-fewer-numbers.html
amerikanzero, it's not the cross posting which is irksome, but the doing so without links.

It's the same rules at MrExcel and ExcelForum and nearly all the others.

For your information, you should always provide links to your cross posts.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

Hmmm... I was unable to locate an EDIT POST button anywhere for my original posting.

If you would be so kind as to tell me how to add to my original post I will gladly do so.

You act as if I am willfully disregarding the forum rules, which I am not.

If I am to add them here I will.

Crosslinks to other forums:

http://www.excelforum.com/showthread.php?t=1140896&p=4396002&highlight=#post4396002

http://www.mrexcel.com/forum/excel-...s-w-matching-criteria-into-fewer-numbers.html

Out of curiosity, before offering any useful assistance, is it customary to check other similar forums for the same posting? I am not being sarcastic, just honestly curious.

I'm sorry, I was being a bit automaton-like in msg#9. There isn't a requirement here to edit your original message as at other sites - the main reason being - you can't, as you've found. I only ask that if you have cross posted somewhere not among the links already provided by anyone at all in a thread, to provide those links - usually in the next message you post.

Now to:

amerikanzero said:

Out of curiosity, before offering any useful assistance, is it customary to check other similar forums for the same posting? I am not being sarcastic, just honestly curious.

Click to expand...

I doubt it. Often I just come across cross-posts later. However, seasoned responders like myself (yuk!) may do so in some cases:
1. If the poster has a low post count, it's often the case that (a) they haven't read the rules and (b) they want an answer quickly and want maximum exposure (nothing wrong with that).
2. If the question is one that'll require significant time to answer I will check to see if they've cross posted in any of the major forums, so that I don't embark on a long solution that may have been already supplied elsewhere.
Just occasionally, and it was the case with this thread when you said "UNABLE TO ADD ANYTHING> NO IMAGES> NO DOCS", I look for cross posts where the poster might have successfully attached a file.

That link (http://www.excelguru.ca/content.php?184) says it all.

Now, you've got the post count you need here, how about posting that file? Later tomorrow, I'm off to an area with scant internet connectivity for a few days (Bank Holiday here in UK), so the sooner the better.

No worries, I totally get the reasoning behind the methodology.

Honestly, I have never posted anything on any of these before. I generally tend to just grind it out until I find the solution myself. I did find a solution, but as you will see, there has got to be an easier approach.

Thanks again for the response, now here is my document!

p45cal said:

I'm sorry, I was being a bit automaton

Click to expand...

I can get you to a sensible list of unique matches of the parameters you want to consolidate with a pivot table very quickly. In the attached is said pivot table. One new Style ID per row on the pivot. If you add Style ID to the Values section of the pivot it will give you a count of them. If you then double-click the number, you'll get a new sheet with just the rows comprising those rows matching.
I agree with you about the dodginess of column I, but with the pivot idea you might not need it.
You can sort the pivot in a whole variety of ways.

If you now move the Style ID from the Values area to join the others in the Row Labels area you'll get a full list of existing Style IDs for each group, but now of course there are more rows in the pivot than new Style IDs needed.

You can use simple formulae outside of the pivot to consolidate Style IDs once the pivot table is in a convenient order, and if there is an added column of names (Starwars etc.), that can be in the pivot too. You can of course copy/paste values the table elsewhere if you want further manipulation/added or inserted columns.

If you prefer to see all the cells filled in the pivot, if you go to PivotTable Tools on the ribbon, Design tab, Layout section, click on Report layout, choose Repeat all Item Labels.

Change the original data? Then refresh the pivot.

A starting point?

Dude! (please forgive the American slang here)

Cheers! Bravo! I like it. It is definitely a good start. Thank you for showing me this workaround

I have played around a bit with pivot tables in the past, but without any formal instruction I have been reluctant to use them to their obvious potential.

Would you mind assisting me with this to the end? By that I mean suggesting a formula that would work to allow me to have a final list like the one in the attached document titled Final Output for Data Entry?

Here is a quick explanation. As I stated previously, I often have thousand or more numbers that need to be keyed or entered into a form within the system that I work in. This form has a certain order or "process" that the information needs to be entered into the system.


In order to avoid an errors as well as to save a ton of time I use a program called AutoHotkey. I have written a script that enters the information into the system for me.


Here is basically how it works. I ignore any of the "To" numbers where they would go into themselves since they will remain untouched how they are. I will actually remove them from my spreadsheet completely since all I am concerned with are entering numbers where the "From" and "To" are different as detailed in columns "N" and "O" on the newly added spreadsheet.


Notice that all of the cells containing an "*" are either the first number in the list and have been designated as the "to" number and therefore would remain as they are or are numbers where there is no matching criteria and would neither be going into nor would any number be going into it.At this point I would remove the asterisks * and be left with a list of all the From and To styles as seen in "Q" and "R".


What we see in "Q" and "R" is really the ultimate goal. At this point I simply load the Auto Hot Key script and let it fly!

p45cal said:

I can get you to a sensible list of unique matches of the parameters you want to consolidate with a pivot table very quickly. In the attached is said pivot table. One new Style ID per row on the pivot. If you add Style ID to the Values section of the pivot it will give you a count of them. If you then double

Click to expand...

In the attached, is a new sheet with:
Columns A:G, your raw data (where column F labelled Column 1 which I assume is your Style ID)
Columns J a pivot table as suggested earlier
Columns R:S simple formulae looking at the pivot table
Columns U:V manually created Advanced Filter of columns R:S, filtered for unique items and manually deleted the single row with asterisks
Columns X:Y table copied over from your Output to AHK sheet for comparison where you seem to have missed out a single entry (in red)

At the moment, the Final To Style IDs are the lowest/earliest existing Style ID in each group which is just because that's how the pivot table had it sorted. Is there to be some logic as to which Style ID should be the one to represent the whole group?

Awesome. This looks fantastic. Seriously, I can't begin to tell you how time this will save.

Is there a way to add a rep point, give you some BitCoin or something to show the community how much you helped me out?

To answer your final question in regards to "logic as to which Style ID should be the one to represent the whole group", I have not been given any detail on that. Prior to my taking this over, the whole process was usually handled by a group of people that took about a week. There are a lot of moving parts that happen before this point that I have worked to streamline as well. If I get any details Will let you know.

Seriously, this will help me a ton and I really do appreciate your taking the time to help me.

Final Thoughts:

1.) Could you give me your Pivot settings as to how you got it to the way it does? I am pretty sure I am close, but just want to be certain.

2.) So can I, with 99.9% certainty be comfortable that these will be correct. By that I mean,I shouldn't have to worry about any duplicates down the line to throw it off?
3.) I had mentioned in an earlier correspondence that I often have special circumstances where there is an added column with Names. These names are given to me and are usually somethng that our Buyers use as a personal description. It coul be the name of a certain Vendor e.g., Doc Marten or Hello Kitty. These would be "Special Out" within their own group. Would these show, I mean will a Pivot Table recognize text?


See attached Document.


Also, you have given me a much clearer picture of the usefulness of Pivot Tables as well as the boost that I needed to learn more about them. Thanks again!

amerikanzero said:

Is there a way to add a rep point…

Click to expand...

I don't thnk so. You can rate the thread I think, if you Go Advanced, or it might be under Rate This Thread top right of the thread.

amerikanzero said:

1.) Could you give me your Pivot settings as to how you got it to the way it does? I am pretty sure I am close, but just want to be certain.

Click to expand...

Add the columns to the Row Labels section in a sensible order (category, sub-category etc. where the more encompassing categories are to the left.
In the ribbon, in the PivotTable tools|Design tab, Layout section, Subtotals = Do not show, Grand Totals = Off for rows and columns, Report layout = tabular form

amerikanzero said:

2.) So can I, with 99.9% certainty be comfortable that these will be correct. By that I mean,I shouldn't have to worry about any duplicates down the line to throw it off?

Click to expand...

Personally, I think it's more robust than trying to create a unique ID column.
Be aware of the garbage in, garbage out rule though; your raw data should be good. For example, don't have different versions of Chelsea F.C. (extra/trailing/leading/internal spaces, different full stop use etc.) While you're building the pivot you'll probably notice what look like duplicates but aren't because of such minor differences.
Make sure that each column in the raw data has consistent data type in a column (in your original file you had some numbers as text, and some numbers as numbers (B17:B19) in the same column.
If you change the raw data, don't forget to refresh the pivot.

amerikanzero said:

3.) I had mentioned in an earlier correspondence that I often have special circumstances where there is an added column with Names. These names are given to me and are usually somethng that our Buyers use as a personal description. It coul be the name of a certain Vendor e.g., Doc Marten or Hello Kitty. These would be "Special Out" within their own group. Would these show, I mean will a Pivot Table recognize text?

Click to expand...

Yes, see attached for two pivots.

Hey there,

I wanted to say thank you again for your assistance with this. I was able to make it work flawlessly.

You are a gentleman and a scholar!

May I message you again if I find something else that may be worthy of your expertise?

amerikanzero said:

May I message you again if I find something else that may be worthy of your expertise?

Click to expand...

What I would prefer is that you post your query in the appropriate public forum here and if you want me to know of it you can Private Message me giving a link to the thread.

Of course, of course. That is actually what I meant.

I just wanted to be sure that it was okay to PM you if I posted anything.