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

 

[stevebausch]

I look forward to the responses on this thread, there should be a way to do this without resorting to VBA.

 

[hianupam]

Noir, Can you mail me a sample spreadsheet at dasa@paceglobal.com so that I can have a look.

 

[Noir]

Hello Hianupam,
Unfortunately, i cannot send you my spreadsheets. They have sensative data on them.

Please try and use the example i've given

Noir

 

[hianupam]

Noir assuming that the same info i.e 7 rows is repeated in the worksheet that your co workers send you you can use something like this
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")
.....
.....
sheets(strSheetname).Select
for i = 2 to 1000 step 7
if Range("B" & i).Value = arrMyconfig(1) then
if Range("B" & i+1).Value = arrMyconfig(2) then
.....
'Nested if for all the seven elements
blnFoundMatch = true

End if
End if

if blnFoundMatch Then
Counter = Counter + 1
blnFoundMatch = False
End if
Next i
Sheets(strMysheetname).Select
Range("B2").Value = Counter

 

[Aladin Akyurek]

On 2002-09-04 11:19, Noir wrote:
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

In B2 in Sheet1 in Book1 enter and copy down to B8:

=COUNTIF([Book2.xls]Sheet1!$B$2:$B$1000,A2)

In B1 enter:

=MIN(B2:B8)

 

[Noir]

Do i place "all" code in a sheet module? Should i give it a sub name? Please advise

Thx,
Noir

 

[Dave Patton]

I tried to post a Countif solution solution that would work in some instances depending on mix of parts. The following considers the groups.

You could create a lookup table for the group ( I named it rI) and second column with the number 1 to 7. Ensure the first column is sorted.

Array enter the formula that follows

=SUM(LOOKUP(A2:A8,rI)))

Copy the formula down or fill it down to the bottom of the list.

Sum the column to ignore #N/A use

=SUMIF(C2:C1000,28)

Adjust the ranges and columns as necessary.

This Board is now so slow I never know if edits or submissions actually are completed.
Too slow to use.
This message was edited by Dave Patton on 2002-09-08 03:28

 

[Noir]

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

 

[hianupam]

I was trying alladin's solution that seemed very elegent but came across the same problem as you ;it does not see it collectively. Noir you can put it in all the code in one module and run it or assign it to a command button.