VBA help needed

  • Thread starter Thread starter Eric @ BP-EVV
  • Start date Start date
E

Eric @ BP-EVV

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