This is about to drive me nuts. I have over 20,000 rows of data to analyze
and it's far to big to do by hand. I've received a spreadsheet that tells
where products are made. Some are made at a single location. Others are
made at 2 to 6 locations. Each product-location combination is an array,
separated by space. I want to somehow group these or handle these in such a
way as to ignore all the single location materials.

Example:

PRODUCT LOCATION
AAA 1000
AAA 2000
AAA 3000

BBB 1000


CCC 2000
CCC 3000
CCC 5000

The spaces in the above example are empty rows in Excel. I want something
that gives me info on AAA and CCC and their corresponding locations but skips
over BBB because it's made at a single location. Even something that would
just visually flag these groups would be great!! Something that allowed a
sort by the number of members within a group would serve me well too.

Is there a way to have a SUM function run in a pre-existing set of groups
within a column such as the above. I have blanks beneath each group where a
sum could be entered but I don't know how to write a macro that finds the
next blank then sums the contiguous data-containing cells above it, then
moves on to the next blank etc.

Is there something than could do a running count? in the above example 3,
1, 3; with these numbers being pasted in an nearby column?

I've tried to think of any way possible to avoid doing this by hand. If you
have any suggestions I will truly appreciate it.

Thanks.

I'd use a helper column to count how many times each of the entries showed up.

So if the products were in column A, then I'd put this formula in B2:
=if(a2="","",countif(a:a,a2))
and drag down.

Since this is a one time thing, I'd convert the formulas to values
select column B
edit|copy
edit|paste special|values
(fewer formulas will speed up excel)

Then I could apply data|filter|autofilter to that column.
And use a custom filter to look for values greater than 1.

ConfusedNHouston wrote:

This is about to drive me nuts. I have over 20,000 rows of data to analyze
and it's far to big to do by hand. I've received a spreadsheet that tells
where products are made. Some are made at a single location. Others are
made at 2 to 6 locations. Each product-location combination is an array,
separated by space. I want to somehow group these or handle these in such a
way as to ignore all the single location materials.

Example:

PRODUCT LOCATION
AAA 1000
AAA 2000
AAA 3000

BBB 1000

CCC 2000
CCC 3000
CCC 5000

The spaces in the above example are empty rows in Excel. I want something
that gives me info on AAA and CCC and their corresponding locations but skips
over BBB because it's made at a single location. Even something that would
just visually flag these groups would be great!! Something that allowed a
sort by the number of members within a group would serve me well too.

Is there a way to have a SUM function run in a pre-existing set of groups
within a column such as the above. I have blanks beneath each group where a
sum could be entered but I don't know how to write a macro that finds the
next blank then sums the contiguous data-containing cells above it, then
moves on to the next blank etc.

Is there something than could do a running count? in the above example 3,
1, 3; with these numbers being pasted in an nearby column?

I've tried to think of any way possible to avoid doing this by hand. If you
have any suggestions I will truly appreciate it.

Thanks.

--

Dave Peterson