COUNTIF - is this the solution to my problem?
Posted by Lynnsey on February 04, 2002 3:59 AM
Hi all,
I am trying to do the following:
I have a list of guests on one worksheet. One of the columns is the guest's name and another column is the table number that the guest has been assigned to. On another worksheet I have the table numbers in a row across the screen and I would like the names of the guests assigned to those tables to appear below. I *think* I need to use COUNTIF but I haven't been able to figure out how - any help would be appreciated.
Thanks!
Posted by Aladin Akyurek on February 04, 2002 4:24 AM
Lets say that A1:B25 in Sheet1 houses the guests (column A) and table numbers (column B) with A1:B1 containig labels like Guests and Tables.
Lets also assume that A2:J2 houses "the table numbers in a row across the screen" in Sheet2.
In A3 enter: =INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0))
Copy this across to J2.
============================
Posted by Lynnsey on February 04, 2002 5:35 AM
Thanks Aladin, more info please!
Could you please explain how this works (if you don't mind) because it's doing *sort-of* what I need but not quite, and I don't know how to tweak it.
Also, J2 is one of my "table number" cells so surely I should not copy the formula into that cell?
Posted by Aladin Akyurek on February 04, 2002 6:12 AM
Re: Thanks Aladin, more info please!
> Could you please explain how this works (if you don't mind) because it's doing *sort-of* what I need but not quite, and I don't know how to tweak it.
THE formula matches (with MATCH) the table number in A2 (Sheet2) to the values in column B of Sheet1. MATCH returns, if successful, the row number of the value that matches the lookup value from A2 (Sheet2). INDEX returns the guest from column A in that row in Sheet1.
> Also, J2 is one of my "table number" cells so surely I should not copy the formula into that
cell?
NO. I meant: Copy the formula in A3 (Sheet2) to J3.
Apparently, you're looking for something different. Care to describe what it is?
Posted by Lynnsey on February 04, 2002 6:53 AM
MATCH keeps finding the same item
Hi Aladin,
The formula you gave is working almost, but not quite. The problem I'm having is that it's repeating names, because the MATCH is picking up the first item that it matches, which means that it keeps repeating the same one. I have tried to use OFFSET to auto-increment but it doesn't seem to be achieving the desired results (as you can see I have learnt a bit from reading the help files on the functions you used!).
My first spreadsheet looks like this:
Name Table
Bob and Jane 1
Ted and Sue 1
Joe and Tim 2
Peter and Sue 3
Ian and Marge 2
H and K 1
L and G 3
U and F 2
D and M 3
Then on sheet #2 I want this:
Table 1 Table 2 Table 3
Bob and Jane Joe and Tim Peter and Sue
Ted and Sue Ian and Marge U and F
H and K L and G D and M
I really appreciate your help - thank you!
Lynnsey
Posted by Aladin Akyurek on February 06, 2002 8:49 AM
Re: MATCH keeps finding the same item
Lynnsey --
I didn't realize that a table could be assigned to multiple people.
In Sheet2:
Insert en empty row. You'll now have in A2:C2 the labels Table 1, Table 2, and Table 3.
In A1 enter: =MATCH(9.99999999999999E+307,x!B:B)
In B1 enter: =ROW(3:3)
In A3 enter: =IF(COUNTIF(Sheet1!$B:$B,RIGHT(A$2)+0),INDEX(Sheet1!$A:$A,MATCH(RIGHT(A$2)+0,Sheet1!$B:$B,0)),"")
Copy this across to C3.
In A4 enter: =IF(COUNTIF(Sheet1!$B:$B,RIGHT(A$2)+0)>ROW()-$B$1,INDEX(Sheet1!$A:$A,MATCH(RIGHT(A$2)+0,OFFSET(Sheet1!$B$1,MATCH(A3,Sheet1!$A:$A,0),0,$A$1,1),0)+MATCH(A3,Sheet1!$A:$A,0)),"")
Copy this first across to C4 then down until no more results appear.
================
