Give count when exact computer config is found

[Noir]

I am tracking/counting complete computer configurations and all components contained within them. When i receive a workbook from a co-worker (eg:Book2), i want to search through Sheet1, B2:B1000 to find how many computer configurations exactly match my main configuration. In other words, In Sheet1, Col. A of my workbook (Book1), i have a listing of my main computer configuration. I need to be able to look into another workbook (eg:Book2, Sheet1, B2:B1000) and receive a count (eg:0,1,2,3,4,etc.) for the number of "exact" computer configurations found that completely match my main configuration in Book1. An example of a main configuration is as follows;

(Listed in my Book1, Sheet1, Column A)

A2 - System Chassis
A3 - System Memory
A4 - Hard Disk Drive
A5 - CD Writer
A6 - NIC Card
A7 - Monitor
A8 - Ext. Tape Backup

Luckily, we all cut and paste the same text to populate our configurations so, extra dashes, spaces, etc will not throw-off the search procedure.
The answer should appear in Sheet1, B2 of my Book1. A VBA solution is preferred but, any solution is greatly appreciated!

Thx guys,
Noir

 

[Aladin Akyurek]

On 2002-09-04 13:04, Noir wrote:
Aladin,
Your formula works pretty well, the only problem is, it see's each line individually instead of collectively. So if i go to Book2 and delete a line (eg:System Chassis) from a configuration, your formula will adjust to reflect the new qty for that line only. I would like the formula to reflect a count of "0" since the complete config was not found. Thanks for your suggestion

Noir

I proposed a global count, not a count in terms of 7 consecutive parts.

What happens if Book2 does not follow the sequence:

System Chassis
System Memory
Hard Disk Drive
CD Writer
NIC Card
Monitor
Ext. Tape Backup

but has 7 consecutive entries like:

System Chassis
System Memory
Hard Disk Drive
NIC Card
CD Writer
Monitor
Ext. Tape Backup ?
This message was edited by Aladin Akyurek on 2002-09-04 13:39

 

[Noir]

Hianupam,
I entered your code into a new module and ran it but, was unable to get it to return a count number. Right now it only returns a "0". Please advise what i am doing wrong.

Noir

 

[hianupam]

On 2002-09-04 13:26, Noir wrote:
Hianupam,
I entered your code into a new module and ran it but, was unable to get it to return a count number. Right now it only returns a "0". Please advise what i am doing wrong.

Noir

Noir you need to modify the if statements and the array assignment. It would help if you can send me an actual sample with the structure but fictional data

 

[Noir]

Dave,
Can you give me more detail on your "Lookup Table" suggestion?

Noir

 

[hianupam]

On 2002-09-04 13:34, hianupam wrote:

On 2002-09-04 13:26, Noir wrote:
Hianupam,
I entered your code into a new module and ran it but, was unable to get it to return a count number. Right now it only returns a "0". Please advise what i am doing wrong.

Noir

Noir you need to modify the if statements and the array assignment. It would help if you can send me an actual sample with the structure but fictional data

Noir here is the full code. I checked it works
Sub doit()
Dim blnFoundMatch As Boolean
Dim counter As Integer
Dim arrMyconfig(1 To 7) As String
Dim strSheetname As String
Dim strMySheetname As String
blnFoundMatch = False
counter = 0
strMySheetname = "Sheet1"
strSheetname = "Sheet2"
Sheets(strMySheetname).Select
arrMyconfig(1) = Range("A2")
arrMyconfig(2) = Range("A3")
arrMyconfig(3) = Range("A4")
arrMyconfig(4) = Range("A5")
arrMyconfig(5) = Range("A6")
arrMyconfig(6) = Range("A7")
arrMyconfig(7) = Range("A8")
Sheets(strSheetname).Select
For i = 2 To 78 Step 7
If Range("B" & i).Value = arrMyconfig(1) Then
If Range("B" & i + 1).Value = arrMyconfig(2) Then
If Range("B" & i + 2).Value = arrMyconfig(3) Then
If Range("B" & i + 3).Value = arrMyconfig(4) Then
If Range("B" & i + 4).Value = arrMyconfig(5) Then
If Range("B" & i + 5).Value = arrMyconfig(6) Then
If Range("B" & i + 6).Value = arrMyconfig(7) Then
blnFoundMatch = True
End If
End If
End If
End If
End If
End If
End If
If blnFoundMatch Then
counter = counter + 1
blnFoundMatch = False
End If
Next i
Sheets(strMySheetname).Select
Range("B2").Value = counter


End Sub

 

[Noir]

Aladin,
Each line item has a rank. The lines will always appear in the same sequence/order. Just for the sake of argrument, lets say someone did jumble the order around, i would classify that situation as valid for a "0" match result. (I would handle that issue internally)

Noir

 

[Noir]

Sorry Hianupam,
Still getting a "0" count result with the new code. Please advise.

Noir

 

[Noir]

Hianupam,
By any chance, did you write the code for "Sheet2" instead of "Book2"??

Noir

 

[hianupam]

On 2002-09-04 14:01, Noir wrote:
Hianupam,
By any chance, did you write the code for "Sheet2" instead of "Book2"??

Noir

Yeah that's what it is . I was trying it on the same workbook in two different sheets. Chage the refernece to Book1 and it should work

 

[hianupam]

On 2002-09-04 14:07, hianupam wrote:

On 2002-09-04 14:01, Noir wrote:
Hianupam,
By any chance, did you write the code for "Sheet2" instead of "Book2"??

Noir

Yeah that's what it is . I was trying it on the same workbook in two different sheets. Chage the refernece to Book1 and it should work

change sheet2 to [Book2.xls]Sheet1