output to cells

[wdverner]

Hi all,
I have this macro:

Dim r As Integer, c As Integer, n As Integer
For r = 2 To 5
n = 0
For c = 1 To 10
If Cells(1, c) <> Cells(r, c) Then n = n + 1
Next c
If n Then MsgBox "Row 1...." & r & "=" & n & " Parts different"
Next r

Rather than output it in a MsgBox is it possible to output the results into cells in the worksheet:

A1 A2
Row 1....2 10 Parts different
Row 1....3 5 Parts different

It will loop down the rows until all rows are output?

Many thanks for suggestions....

 

[dhulbert]

use the line

cells(1,c).value = "Row 1..." & r
cells(2,c).value = n & " Parts Different"

or a variation on that depending on whcih cells you are going to use.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[GlennUK]

Ah, there you are ( this query was in the wrong forum earlier folks ).

outputcounter=0
For r = 2 To 5
  n = 0
  For c = 1 To 10
    If Cells(1, c) <> Cells(r, c) Then n = n + 1
  Next c
  If n Then 
     outputcounter=outputcounter+1
     Sheets("myoutputsheet").Cells(outputcounter,1).Value ="Row 1...." & r & "=" & n & " Parts different"
  End If
Next r

have a counter of how much output you've done, and put the results in a different sheet. I've used a sheet called "myoutputsheet" which you can change to whatever sheet you want the output on.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[wdverner]

Hi Glenn,
Thanks for the tip!

Sorry to ask, but am I going about this in the right way?

To explain what I want to achieve is this:

I have 10 products(rows) in a spreadsheet with 10 columns. Each cell contains an integer to identify a part which makes up that product. I want to find the fastest way to manufacture the ten products.

To do this I compare how many of the same parts are used in each product.

e.g. I would compare product 1 (starting product) to all the other 9 products to see which has the highest amount of parts the same. I would then put this in second position. I would then compare product 2 to the remaining 8 products and perform another comparison to get what product goes in third position and so on and so forth...

Have you/anyone any input into the best way of achieving this? its not something that I have ever seen done anywhere.

many thanks for your time/patience/advice.

 

[Bong]

For the sake of clarity, let's say you had 2 columns instead of 10. Your first row (product) uses 5 partA's and 5 partB's, say. Now your 2nd row has 6 partA's and 4 partB's. Then, your 3rd row has 5 partA's and 7 partB's. Which of them (row 2 or row 3) is nearer the baseline in your scheme?

_________________
Bob Rashkin

 

[wdverner]

Hi Bong,
Each product has 10 UNIQUE parts....

So if Prod_A uses 1,2,3,4,5
Prod_B uses 1,2,3,4,6
Prod_C uses 1,3,6,7,8

Id make Prod_B after Prod_A as they share 1,2,3,4 whereas Prod_C only uses 1,3.

if you catch my drift.....

 

[Bong]

So each product uses only 1 of each part? Or does it not matter how many of each part, only that they use some of the same parts?

_________________
Bob Rashkin

 

[wdverner]

Each product uses 10 unique parts. All that I am intested in is comparing the differences between the 2 sets of 10 numbers.

It needs to loop through comparing all nine products to product A (starting point) and output this. It will then find the lowest difference and copy this product into second position. It will then compare THIS product to the remaining 8 products and count the differences between each products set of 10 parts and so on...

i hope this is somewhat clearer to understand Bong, apologies for any ambiguity?

 

[Bong]

Well, I'm still confused. If Product A uses 5 of Part A. If another product uses 6 of Part A and yet another uses 4 of Part A, which is closer?

_________________
Bob Rashkin

 

[wdverner]

Product A
1
2
3
4
5

1,2,3,4,5 are unique IDs to identify a part name.

e.g. 1= Front Grey
2= Back grey
3= Red front
4= Black Sides
5= White Front

These 5 parts make up Product A.

Product B
1
2
3
4
6

Part 6= Yellow Back

If we compare Product A to Product B there are FOUR similar parts used in each product= 1, 2, 3, 4.

Product A uses part 5 which product B doesnt. Product B uses part 6 which product A doesnt.

Therefore we can say to go from Product A to Product B there is only ONE change required: To go from making Part 5 to making Part 6 instead.

Now Product C is different. It uses parts 1,3,6,7,8

To go from Product A to Product C is THREE changes:

Parts 1 & 3 are the same.
Product A has parts 2,4,5, product B has parts 6,7,8 instead.

So 2 are the same, 3 are different.

Now we know this information:

Product A to Product B= ONE CHANGE
Product A to Product C= THREE CHANGES.

Therefore it makes more sense to make Product B next after Product A as only ONE change is required.

The next step would be then to compare Product B's parts with Product C, D, E, F etc etc.

dos this clarify Bong?

Sorry about the confusion, and MANY thanks for your time and effort.

 

[Bong]

OK, then. I think I get it. The key to evaluating the comparison is the state of zero- or non-zeroness of the 10 columns. I would build the algorithm as follows:

define a 2-dimensional Boolean array where the first index is product ID and the second index is part number.

for each product, if the part is used the value of that element is 1, if not, 0.

now you have a set of 10- (or is it 9-) bit arrays.

initialize 2 integer arrays: "similarities" and "differences"
starting with your baseline product, compare each of the product arrays to it: if the elements are equal, increment "similarities"; if they are unequal increment "differences".

now you have to decide how to order the products based on the similarities and diffences. That is, if product N, say, has all the same parts as product A, but has 4 additional parts, too, what does that mean.

_________________
Bob Rashkin