VBA help needed
 

I think I'm making this much harder than it should be so I thought I'd ask
this forum for some advice. I've got a spreadsheet that looks something like
this:

location item# weight
A 1 0.75
B 1 0.75
C 1 0.925
A 2 1.5
B 2 1.5
C 2 1.5
A 3 2.25
A 4 5.75

There can be up to 20 location codes and any number of items, with weights
that may or may not match. What I am trying to do is loop through the data
to determine for all item # if the weight identical for all locations. If
true (or if the item exists at only one location) I want to copy that data to
tab in the workbork named "weight OK" - if false I want to copy that data to
a tab in the workbook named "weight exception". The data is sorted already
by Item#, and I'm using a CountIf formula to determine how many rows of data
there are for each item #.

I seem to be suffering from some sort of "writers block" on this...I know I
need to loop through the data until I reach EOF, but where I seem to be stuck
is figuring out how to cycle through the data for each grouping of item #s in
order to complete the analysis and copy data to the appropriate
workbook....can
anyone provide some suggestions ?

Thanks !

VBA help needed
 

You could use a column of formulas - let's say that you table is in columns A through C, with the
headers in row 1. In cell D2, enter the formula

=SUMPRODUCT(($B$2:$B$10000=B2)*1)=SUMPRODUCT(($B$2 :$B$10000=B2)*($C$2:$C$10000=C2))

and copy that formula down. Then you could apply a filter to column D to show just True or False
values - True for items whose weight is constant, False for items whose weight varies.

HTH,
Bernie
MS Excel MVP


"Eric @ BP-EVV" wrote in message
...
I think I'm making this much harder than it should be so I thought I'd ask
this forum for some advice. I've got a spreadsheet that looks something like
this:

location item# weight
A 1 0.75
B 1 0.75
C 1 0.925
A 2 1.5
B 2 1.5
C 2 1.5
A 3 2.25
A 4 5.75

There can be up to 20 location codes and any number of items, with weights
that may or may not match. What I am trying to do is loop through the data
to determine for all item # if the weight identical for all locations. If
true (or if the item exists at only one location) I want to copy that data to
tab in the workbork named "weight OK" - if false I want to copy that data to
a tab in the workbook named "weight exception". The data is sorted already
by Item#, and I'm using a CountIf formula to determine how many rows of data
there are for each item #.

I seem to be suffering from some sort of "writers block" on this...I know I
need to loop through the data until I reach EOF, but where I seem to be stuck
is figuring out how to cycle through the data for each grouping of item #s in
order to complete the analysis and copy data to the appropriate
workbook....can
anyone provide some suggestions ?

Thanks !

VBA help needed
 

Bernie,

Thanks for the great suggestion...SUMPRODUCT...I had only used that once
before in my entire Excel life and in that case I inherited it from a
predecessor and still to this day am not sure I fully understand what
SUMPRODUCT can do or how it does it....but in this case I think you solved my
dilema. I'm playing around with my test case data and it seems to be
providing me with the results I expect every time !

AWESOME ! I really appreciate the assist !

Eric


"Bernie Deitrick" wrote:

You could use a column of formulas - let's say that you table is in columns A through C, with the
headers in row 1. In cell D2, enter the formula

=SUMPRODUCT(($B$2:$B$10000=B2)*1)=SUMPRODUCT(($B$2 :$B$10000=B2)*($C$2:$C$10000=C2))

and copy that formula down. Then you could apply a filter to column D to show just True or False
values - True for items whose weight is constant, False for items whose weight varies.

HTH,
Bernie
MS Excel MVP


"Eric @ BP-EVV" wrote in message
...
I think I'm making this much harder than it should be so I thought I'd ask
this forum for some advice. I've got a spreadsheet that looks something like
this:

location item# weight
A 1 0.75
B 1 0.75
C 1 0.925
A 2 1.5
B 2 1.5
C 2 1.5
A 3 2.25
A 4 5.75

There can be up to 20 location codes and any number of items, with weights
that may or may not match. What I am trying to do is loop through the data
to determine for all item # if the weight identical for all locations. If
true (or if the item exists at only one location) I want to copy that data to
tab in the workbork named "weight OK" - if false I want to copy that data to
a tab in the workbook named "weight exception". The data is sorted already
by Item#, and I'm using a CountIf formula to determine how many rows of data
there are for each item #.

I seem to be suffering from some sort of "writers block" on this...I know I
need to loop through the data until I reach EOF, but where I seem to be stuck
is figuring out how to cycle through the data for each grouping of item #s in
order to complete the analysis and copy data to the appropriate
workbook....can
anyone provide some suggestions ?

Thanks !