Excel MAtching Data

Solved/Closed
Report
-
 Kamal -
Hello,

I have two rows of data in excel one has the name of all members about 100 and the other list is of members who have paid their dues about 50.

how do i match the name or separate the two so i know who hasnt paid.

Thanks

Shak

2 replies

Posts
5
Registration date
Tuesday February 24, 2009
Status
Member
Last seen
February 25, 2009
19
The simplest way is to put the Match formula next to your list of all members. Use the column right next to the all list.
=MATCH(Lookup_Value,Lookup_array,[Match_Type])
As your lookup value, use your member from the All Members list. As your Lookup array, use the paid list. The result will be a number of the row in your lookup array, anything with a #N/A will not have paid. Copy down the whole All member list. Make sure that if you do this, your Lookup_array is absolute valued by either shooseing the entire column, or using dollar signs. (E:E or $E:$E) as in my example below.

The resulsts will have an #N/A nest to those who havnt paid. You can then make it more fancy my using an IF(ISERROR formula which you can tell Excel to tell you if someone has paid or not...see below.
IF(ISERROR(MATCH(B4,E:E,0)),"Not Paid","Paid")

B C D E
All List Paid List
4 Member 1 Not Paid Member 3
5 Member 2 Not Paid Member 6
6 Member 3 Paid Member 9
7 Member 4 Not Paid
8 Member 5 Not Paid
9 Member 6 Paid
10 Member 7 Not Paid
11 Member 8 Not Paid
12 Member 9 Paid
13 Member 10Not Paid
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
5
Registration date
Tuesday February 24, 2009
Status
Member
Last seen
February 25, 2009
19
Sorry:  my Table didn't come out...Should look Like this:
	B	C	D	E
	All List			Paid List
4	Member 1	Not Paid		Member 3
5	Member 2	Not Paid		Member 6
6	Member 3	Paid		Member 9
7	Member 4	Not Paid		
8	Member 5	Not Paid		
9	Member 6	Paid		
10	Member 7	Not Paid		
11	Member 8	Not Paid		
12	Member 9	Paid		
13	Member 10Not Paid		
Its Simple...

Create a Pivot tabel using the data and filter the "Paid"..

You could see the result as u wished

How to create Pivot table:
Select the data
Click Insert tab
Click Pivot table
Now filter the data

You're done