Complex Comparisons

[wdverner]

hi All,
I have a spreadsheet with 5 rows and 10 columns.

Each row details which 10 parts makes up that product. Each cell has a numeric input.

What i want to do is compare Product 1 (Row 1) to Product 2 (Row 2) and COUNT the number of different parts between the two Products. I want to record this say in Worksheet 2. i.e. Row 1....2= 5 Parts different.

I then want to run this comparison again between Row 1&3, 1&4, 1&5.

I will then have a list of comparison between each.

How can this be done?

Its puzzling me somwhat!

 

[Bong]

You want to compare Row1 to the other rows and not, say, Row2 to anything but Row1? And, to be clear, the difference is a single number equal to the sum of the column differences?

_________________
Bob Rashkin

 

[wdverner]

I wish to compare Row 1 to Row 2.
Then Row 1 to Row 3
Then Row 1 to Row 4
Then Row 1 to Row 5.

Each row contains 10 part numbers. What i want to do is compare the 10 columms of Row 1 to the 10 columms of Row 2 and count the number of differences between each:

For example
Row 1: 1 2 3 4 5
Row 2: 1 2 3 4 6

This would result in an output of 1: As in Row 1 Column 5 Part 5 is used, however in Row 2, Column 5 Part 6 is used. The remaining are all the same, so 1 is recorded alongside which rows were compared.

A loop would then go back to check Row 1 to Row 3.

This is starting point anyway. I trust this is the clarification you sought, apologies for the ambiguity!

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wdverner]

Hi PHV,
I have tried various attempts at simply comparing one row to the next:


=IF(A$1=A2,0,1)

AND

=IF(ISERROR(SMALL(J:J,ROW(B1))),"",
INDEX(C:C,MATCH(SMALL(J:J,ROW(B1)),J:J,0)))

to discover differences and then simply counting them.

I currently have the num of rows and columns counted and began to construct a for loop to repeat until it equals my total number fo rows?

 

[PHV]

A starting point:
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

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Your data is not normalized.

I'd start by restructuring the data as...
[tt]
Product Component
[/tt]
In your particular case (thus far) each Product ONLY contains 10 components. VERY strange indeed, in the manufacturing world.

Once you data is thus restructured, it will be a snap to use any number of differrent Excel features to do this data analysis.



Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[SkipVought]

FYI...

check out

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[wdverner]

Skip,
Many thanks for your input.

The data is normalised now, 1 Product has Many Components (10 in this example).i'll not bore you with why this is!!

What I want to ultimately achieve is to discover the best way to schedule making these 10 products..

If i know that Part 1 and Part 9 have 8 Components the same, then I will make thse together, rather than make parts 1 and 2 together which have only 2 Components the same.

Comparing which components are the same will reduce the process of making the 10 Parts.

I trust you see the angle of where I am coming from, to deem why this is necessary?

 

[Bong]

But shouldn't you then have 10 comparison results for each comparison? Just getting a single number doesn't tell you which parts are the same, right?

_________________
Bob Rashkin

 

[wdverner]

Hi Bong,
All i want to know is the number of different parts are different, what parts they are doesnt matter.

From the number of differences i can work out downtime etc.

 

[Bong]

Then isn't what you're trying to do the same as comparing the sum of the parts? If so, just add a column summing the total parts and sort on that column.

_________________
Bob Rashkin

 

[wdverner]

Hi Bong,
Not really, as each cell contains a PartID, with parts going up to 250, summing them will not work?

 

[SkipVought]

You ought to be able to use MS Query via Data/Get External Data to query the SHEET (Table) that has your normalized data.
[tt]
SELECT
A.Parent
, B.Parent
, Count(Component)

FROM
`D:\My Documents\MyStuff\dbBOM`.`Sheet1$` A
, `D:\My Documents\MyStuff\dbBOM`.`Sheet1$` B

Where A.Parent<>B.Parent
and A.Component=B.Component

Group
By A.Parent
, B.Parent
[/tt]
gives a list like...
[tt]
ParentA ParentB Common Part Count
p1 p2 1
p1 p3 1
p1 p4 2
p1 p5 3
p2 p1 1
p2 p4 1
p2 p5 2
p3 p1 1
p3 p4 1
p3 p5 1
p4 p1 2
p4 p2 1
p4 p3 1
p4 p5 2
p5 p1 3
p5 p2 2
p5 p3 1
p5 p4 2
[/tt]


Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[tektipdjango]

if you have 2 ranges in which you want to know how many cells are the same try the

=NB(EQUIV(range1;range2;0)) confirm by ctl+enter to get a matricial formula

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[tektipdjango]

Oops this is a french formula

In english this must be

=COUNT(MATCH(range1;range2;0)

Hope this help !

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[wdverner]

Skip,
Mnay thanks for the heads up on Query. Its not something I have used before, it would be much appreciated if you can point me where I am going wrong...

in order for your query you shown me to work how was the spreadsheet constructed?

Like this?

Col A Col B
1 1
2 2

etc?

 

[wdverner]

Hi Skip/All,
Ignore my previous post, I have been playing about with it...

I now get the error in MS Query:

Could not add the table ''c:\tool\Book1'.Sheet5$''.

any idears....?