Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 41
Default Counting Continguous Cell Entries

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Counting Continguous Cell Entries

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting cell entries jc132568 Excel Worksheet Functions 4 December 5th 07 09:23 PM
Counting entries Dos Equis Excel Worksheet Functions 4 November 20th 06 04:24 AM
Counting Entries Jimbo Excel Worksheet Functions 6 April 29th 05 08:27 PM
Average non continguous cells, excluding zero's Keithlearn Excel Worksheet Functions 2 April 27th 05 01:22 PM
Counting Entries in a Cell Sh0t2bts Excel Worksheet Functions 0 February 15th 05 11:08 AM


All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"