Count Number Of Rows With Unique Entries In 2 Columns

weaholt

New Member
Joined
Oct 9, 2006
Messages
18
I have posted this query on Ozgrid, but not having much success so MR Excel please rescue me!!

I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.

I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry out 3 checks on one customer number, which would result in the staff ref number being entered 3 times (there is 1 check per row).

I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number entered in the adjacent column.

The checks are spread across several worksheets - one for "full check", one for "Percenatge Check" etc....

The reply received on Ozgrid was to use -

=SUM(--(FREQUENCY(IF(B1:B100="Bill",MATCH(A1:A100,A1:A100,0)),ROW(INDIRECT("1:"&ROWS(A1:A100))))>0))

where the staff member was called Bill - comitted with CTRL & Shift & Enter

this works on one worksheet, but I do not know how to amend it to work across all the worksheets and give me a total.

thanks for aking the time to read this

xx

All the cust numbers are unique.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Why wouldn't you simply enter the formula once in each sheet, then sum the results across all sheets... you could even perform the last step using a 3-D reference...
 

weaholt

New Member
Joined
Oct 9, 2006
Messages
18
I tried summing the formulas, but realised that if I have a staff check for Bill on sheet1 relating to customer A, and a staff check for Bill on sheet2 relating to the same customer, it calculated it as 2 checks when I want it to show just 1 check as it is for the same customer.

....I am not familiar with a 3D reference!

x
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Good point... and a 3-D reference wouldn't help you out of that problem.

The problem is that you can't span sheets with an array formula. How many rows of data are you searching through in all sheets? Will this number change over time?

And one last piece of advice: if you are going to cross-post, here is an article with the etiquette that should be followed.
 

weaholt

New Member
Joined
Oct 9, 2006
Messages
18

ADVERTISEMENT

OOP! Sorry - I did mention that I had cross posted but didn't include the link - which is - http://www.ozgrid.com/forum/showthread.php?t=58409 - apologies again for this slip up.

I would normally wait a lot longer for a reply, but I'm on a tight deadline and under a bit of pressure. Rest assured I won't do it again.

In answer to your questions about my spreadsheet - Each worksheet has a different number of rows - although I could make these uniform if necessary. The number of rows could change over time - although I suppose I could add on X number of rows to ensure that whoever is completing it will never reach the end....would that help?

thanks so much for your time!
x
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
here is what I would do:

In a new sheet, call it BTC (Behind The Curtains) enter the following formula in cell A1:
Code:
=IF(ROW()>COUNTA('full check'!A:A),IF(ROW()>COUNTA('full check'!A:A)+COUNTA('quality check'!A:A),IF(ROW()>COUNTA('full check'!A:A)+COUNTA('quality check'!A:A)+COUNTA('percentage check'!A:A),"",INDEX('percentage check'!A:A,ROW()-COUNTA('full check'!A:A)-COUNTA('quality check'!A:A))),INDEX('quality check'!A:A,ROW()-COUNTA('full check'!A:A))),INDEX('full check'!A:A,ROW()))
Copy it into cell b1, then copy it down past the end of your data. This will concatenate all of your data into one sheet, and dynamically pick up any new entries in any of the three sheets. Then use your original formula to do your check in the BTC sheet only...

I would also write a formula on sheet BTC that says:
Code:
=COUNTA(A:A)>COUNTA('full check'!A:A)+COUNTA('quality check'!A:A)+COUNTA('percentage check'!A:A)
, and if it becomes false either a) Provide a warning to the user to copy down the formulas in sheet BTC, or b) A piece of VBA code that does the copy paste automatically.

Anyway... it's ONE solution, and NOT [edit] necessarily the best.
 

beccy

Board Regular
Joined
Jul 22, 2004
Messages
116

ADVERTISEMENT

did that formula give the desired result?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

If your customer numbers are true numbers you could use something like:

=COUNT(1/FREQUENCY(IF(CHOOSE({1,2,3},Sheet1!B1:B10,Sheet2!B1:B10,Sheet3!B1:B10)="Bill",CHOOSE({1,2,3},Sheet1!A1:A10,Sheet2!A1:A10,Sheet3!A1:A10)),CHOOSE({1,2,3},Sheet1!A1:A10,Sheet2!A1:A10,Sheet3!A1:A10)))


Confirmed with Ctrl + shift + enter.
 

weaholt

New Member
Joined
Oct 9, 2006
Messages
18
oooh - that looks complicated!

I'm just trying to get me head round it - in the formula for the BTC sheet why are some of the worksheets mentioned more than others? Sorry if it's a thick quesiton - I'm trying to learn!!! I can usually see a pattern within the formula, but I'm stumped at this one!
x
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Make sure you give Fairwinds suggestion a review too: his looks like it will operate without the need to make a dynamic copy of the data, which would keep your file size and processor load to a minimum... my solution is pretty taxing, but effective.

As far as an explanation of my formula:

It's a dynamic data concatenation. It has 3 nested If() functions corresponding to your 3 sheets. From the ouside in:

1) Is the current row number greater than the total rows of data on the first sheet? If no, then extract data from the first sheet: INDEX('full check'!A:A,ROW()) where ROW() is the row number to get data from the first sheet. If YES then
2) Is the current row number greater than the total rows of data on the first 2 sheets? In no, then extract data from the second sheet INDEX('quality check'!A:A,ROW()-COUNTA('full check'!A:A)) where ROW()-COUNTA('full check'!A:A) is the row number to get data from on the second sheet. If YES then:
3) Is the current row number greater than the total rows of data on all three sheets? If YES, then return a logical blank. If NO then extract data from the third sheet: INDEX('percentage check'!A:A,ROW()-COUNTA('full check'!A:A)-COUNTA('quality check'!A:A)) where ROW()-COUNTA('full check'!A:A)-COUNTA('quality check'!A:A) is the row number to extract data on the third sheet. Here is a breakdown into to multiple components to help illustrate:
 

Forum statistics

Threads
1,136,272
Messages
5,674,756
Members
419,525
Latest member
helensesc

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