Give count when exact computer config is found

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
I look forward to the responses on this thread, there should be a way to do this without resorting to VBA.
 

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
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

New Member
Joined
Mar 28, 2002
Messages
37

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Active Member
Joined
Mar 24, 2002
Messages
362

ADVERTISEMENT

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

Thx,
Noir
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Mar 24, 2002
Messages
362
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

New Member
Joined
Mar 28, 2002
Messages
37
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.
 

Forum statistics

Threads
1,144,312
Messages
5,723,650
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top