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

 

[Dave Patton]

What additional info did you want on the Array/Lookup?

the range named rI

is
CD Writer 1
Ext. Tape Backup 2
Hard Disk Drive 3
Monitor 4
NIC Card 5
System Chassis 6
System Memory 7

These add to 28; thus if each is included once, that group would total 28.
This message was edited by Dave Patton on 2002-09-08 03:31

 

[Noir]

Hianupam,
I made the changes to reference Book2 but, code still not working. Also, i moved my configuration data from Book2, Sheet2 to Book1, Sheet2 just to see if the code would find matches that way. It did work however, it only found (1) configuration. My test contained (3) exact config matches.

Would you mind just editing your "doit()" code to reflect the required modifications?

Thx,
Noir

 

[hianupam]

On 2002-09-05 05:50, Noir wrote:
Hianupam,
I made the changes to reference Book2 but, code still not working. Also, i moved my configuration data from Book2, Sheet2 to Book1, Sheet2 just to see if the code would find matches that way. It did work however, it only found (1) configuration. My test contained (3) exact config matches.

Would you mind just editing your "doit()" code to reflect the required modifications?

Thx,
Noir

Noir,
I have modified the code to reflect the changes. Please make sure that both the work book are saved in the same directory. Also in the procedure that I had sent you earlier, I had the counter in the for loop going till 78, you need to make it go till 1000 if you have 1000 rows. I have changed it in this iteration. Let me know if this works. Also if possible send me an email add so that I can mail my test files to you and you can then point out if I am making any wrong assumptions. hope this 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"
'This is my data workbook
Workbooks("Book1.xls").Activate
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")
'This is the woorkbook you get from you co -workers
Workbooks("Book2.xls").Activate
Sheets("Sheet1").Select
For i = 2 To 1000 Step 7 'You will have to change the counter base on how many rows you have (1000 for 1000 rows in Book 2)
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
Workbooks("Book1.xls").Activate
Sheets(strMySheetname).Select
Range("B2").Value = counter


End Sub
This message was edited by hianupam on 2002-09-05 07:36
This message was edited by hianupam on 2002-09-05 07:37

 

[Noir]

Thanks. I'll let you know how it goes.

Noir

 

[Noir]

Hianupam,
I will send you an e-mail so you can reply with your example.

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

Although a bit late, see:

http://www.mrexcel.com/board/viewtopic.php?topic=22916&forum=2&2

 

[Noir]

Thanks Aladin, good formula.

Noir

 

[Noir]

By the way, here is the code that finally did the trick. It will use the data from your source workbook, column A, sheet 1 as the source (only input one config at a time). It will look at whatever workbook, sheet, column you choose (once you edit the code) and not only count the number of configurations found. But it will also highlight the entire matching configuration. "That Anupam is awsome"

Here is the code;
(right-click on sheet tab, click on view code, paste this entire code their)


Sub countconfigs()
Dim wksMaster As Worksheet
Dim wksSlave As Worksheet
Dim FoundIt As Boolean
Dim strChassis As String
Dim lngMasterLastRow As Long
Dim lngSlaveLastRow As Long
Dim c As Range
Dim CurrRow As Long
Dim FoundCount As Long
Dim FirstAddress As String
Dim SetStart As Long
Dim i As Integer

Application.ScreenUpdating = False

' point to the appropriate worksheets
Set wksMaster = ActiveWorkbook.ActiveSheet
' Set wksSlave = Workbooks("config match engine.xls").Sheets("Sheet1") ' for testing
Set wksSlave = Workbooks("vde component detail recon.xls").Sheets("vde inventory")


' Last row on the Master sheet
lngMasterLastRow = wksMaster.Range("A65536").End(xlUp).Row
' Last row on the Slave sheet
lngSlaveLastRow = wksSlave.Range("B65536").End(xlUp).Row

' Get the chassis description
strChassis = wksMaster.Range("A2").Value

With wksSlave.Range("B1:B" & lngSlaveLastRow)
FoundIt = False
Set c = .Find(strChassis, LookIn:=xlValues, LookAt:=xlWhole) ' search for the chassis
If Not c Is Nothing Then ' at least one match is found
FirstAddress = c.Address
Do
FoundIt = True ' assume its found
CurrRow = c.Row + 1
SetStart = c.Row ' store the location of the first record of the set
For i = 3 To lngMasterLastRow
If wksMaster.Range("A" & i).Value <> wksSlave.Cells(CurrRow, 2).Value Then
FoundIt = False
End If
CurrRow = CurrRow + 1
Next i
If FoundIt = True Then
FoundCount = FoundCount + 1
wksSlave.Range("B" & SetStart & ":B" & CurrRow - 1).Font.ColorIndex = 3
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
wksMaster.Range("B2").Value = FoundCount
End With

Application.ScreenUpdating = True


End Sub

 

[Noir]

PS, Many thanks to Jim North for his edits and modifications that made this code the "BEAST" that it is!!!

Noir

 

[Yogi Anand]

Hi Noir:

You could also use the DCOUNTA function to count the complete configurations.

See the worksheet simulation ...

y020921.xls
	A	B	C	D	E	F	G
1						Component
2				TRUE		SystemChassis
3						SystemMemory
4		Component				HardDisk
5		SystemChassis		3		CDWriter
6		SystemMemory				NICCard
7		HardDisk				Monitor
8		CDWriter				Ext.Tape
9		NICCard
10		Monitor
11		Ext.Tape
12		SystemChassis			sourcedata
13		SystemMemory			criterion
14		HardDisk			computedresult
15		CDWriter
16		NICCard
17		Monitor
18		Ext.Tape
19		SystemChassis
20		SystemMemory
21		HardDisk
22		CDWriter
23		NICCard
24		Monitor
25		Ext.Tape
Sheet7

</SPAN>

Regards!

Yogi