Count Number Of Rows With Unique Entries In 2 Columns

weaholt · Oct 10, 2006

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.

hatman · Oct 10, 2006

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 · Oct 10, 2006

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 · Oct 10, 2006

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 · Oct 10, 2006

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 · Oct 10, 2006

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 · Oct 10, 2006

did that formula give the desired result?

fairwinds · Oct 10, 2006

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 · Oct 10, 2006

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 · Oct 10, 2006

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:

Book1