Eliminating Duplicate GROUPS of Styles

[Greg Purnell]

Dear MVPs and fellow Excel Enthusiasts:

I have a conundrum that I'm sure I could eventually figure out on my own,
but you guys and gals are the best, and I need to get this done ASAP, so I
figured I'd see if anyone had a macro or idea that I could implement right
away instead of wasting company time educating myself (at least this time).

Here's the problem. We manufacture athletic uniforms, and each style has a
corresponding style number, as any company would have. Now, the style
number is the first 4 characters of the field, and the following characters
represent the color that the uniform is. What I need to do is make a
spreadsheet that contains just one color in each style so that our
operations manager can go through and change the description of the
operation(s) that are performed (Column B - RTSEQDES). But he doesn't want
to go through and make duplicate changes for each color when our IT guy here
(a SQL guru and Great Plains cut-over manager) can apply the changes made to
just one color in each style to ALL of the colors in that style, since the
operations don't vary by color, only by style.

I hope this is making sense. Anyway, here's how it works: As you can see
by the table posted below, the first style is 0100 (with the color being the
25C). Fortunately, that style only comes in one color, so on to the next
one. As you can see, the 0102's start as 010201C, but change to ...05C.
That one again is not that complicated, but then we get to the 0103's which
start with ..16C and go to ..18C, ..19C, ...21C,...22C, ...23C until we get
to the 0105's which again do the same thing. The spreadsheet as it is (with
all color-ways) is just shy of 15,000 rows, so I really need to skinny this
down.

All I need is one series of each style, be it 10 rows or 3 rows, that
doesn't change with the color, just the style, and like I said, the
operations stay the same for each style, so our guy can apply the new
descriptions to ALL of the colorways, saving us a LOT of time and repetitive
data-entry.

Can anyone point me in the right direction? See below.

Thanks,
Greg Purnell

Quality Assurance

HENSON COMPANY, INC.

d/b/a BRUTE Wrestling & NeuEdge Sportswear

8 Corporate Blvd.

Sinking Spring, PA 19608

Phone ~ 610.898.1721 (direct)

Fax ~ 610.898.1761 (direct)

Email ~ (e-mail address removed)



ITEM NUMBER RTSEQDES
010025C Make Marker
010025C Spread
010025C Cut
010025C Bundle
010201C Make Marker
010201C Spread
010201C Cut
010201C Bundle
010201C Ovlk/All Seams
010202C Make Marker
010202C Spread
010202C Cut
010202C Bundle
010205C Make Marker
010205C Spread
010205C Cut
010316C Make Marker
010316C Spread
010316C Cut
010318C Make Marker
010318C Spread
010318C Cut
010318C Bundle
010319C Make Marker
010319C Spread
010319C Cut
010321C Make Marker
010321C Spread
010321C Cut
010322C Make Marker
010322C Spread
010322C Cut
010322C Bundle
010323C Make Marker
010323C Spread
010323C Cut
010526C Make Marker
010526C Spread
010526C Cut
010526C Bundle
010532C Make Marker
010532C Spread
010532C Cut
010532C Bundle
010533C Make Marker
010533C Spread
010533C Cut
010533C Bundle
010573C Make Marker
010573C Spread
010573C Cut
010573C Bundle
010582C Make Marker
010582C Spread
010582C Cut
010582C Bundle
010625C Make marker
010625C Spread
010625C Cut
010625C Bundle

 

[Max]

.. the style number is the first 4 characters of the field,

.. All I need is one series of each style, be it 10 rows or 3 rows, that
doesn't change with the color, just the style,

Assuming source item numbers in A2 down,
put this in say C2: =LEFT(A2,4)
Copy C2 down to cover the extent of data in col A
That should produce the result that's wanted in col C

---

 

[Greg Purnell]

Yes, I've done that already, but how do eliminate all colors but one...I
only need one color to represent each style as the "B" column just needs to
be edited and then our DB guy can input the correct information in those
fields and apply it to all colors.

Basically what I need is a spreadsheet that has one group for each style
number (first 4 characters). It can be 3 operations, or it can be 300, but
what I don't want is that same 3 or 300 operations repeating again for every
color, to go through and edit the "B" column for each row would be redundant
when we can [after the fact] apply the same changes to each color within
that given style.

Does that make more sense?

Thanks for the help Max. much obliged.

Greg

 

[Max]

Let's start over, Greg <g>. I'll presume what you're after is to dynamically
extract a uniques list of the source item numbers into a col in another
sheet. Here's one way to get it up ..

Assume the source data is in a sheet: X, with item numbers running in A2
down.

For easy test, just rename your a copy of your actual source sheetname to X.
After getting it up and working, you can always restore the sheetname later,
and leave it to Excel to adjust the formulas accordingly.

In a new sheet: Y,

Put in A2:
=IF(X!A2="","",IF(COUNTIF(X!$A$2:A2,X!A2)>1,"",ROW()))
(Leave A1 blank)

Copy A2 down to cover the max expected extent of data in X's col A, say to
A15500?

Then put in B2:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(X!A:A,MATCH(SMALL(A:A,ROW(A1)),A:A,0)))

Copy B2 down by the smallest extent sufficient to cover the max unique item
numbers expected, say down to B1000? Col B will return a dynamic list of
unique item numbers from X's col A. (Hide away col A)

Based on your sample data, col B returns:

010025C
010201C
010202C
010205C
010316C
010318C
etc

 

[Max]

The uniques list in Y's col B will be neatly bunched at the top ..

---

 

[Max]

Greg,
Just a closure before this thread fades away. In the absence of further
feedback from you, I'm not sure whether the 2nd guess did it here. Either
way, let us know. Others may have alternative insights to offer you.

---

 

[Max]

... well, afterall you did take the effort to articulate your problem. Just
thought you'd be naturally interested in pursuing it to a satisfactory
resolution, no <g>?

---