Show names that dont appear on the summary sheet

Swifey

Active Member
Joined
Jan 16, 2003
Messages
421
Could you please help what I want to do is:-

if a customers name on my main summary sheet is on my worksheet then say “Previous” or if a name appears on my worksheet and not on my summary sheet I then ant to show the customers name on my summary sheet.

For example:-

“Previous” Joe Bloggs = Name appears on worksheet and Summary
“Previous” Fred Bloggs = Name appears on worksheet and summary
“Previous” John Bloggs = Name appears on worksheet and summary
Sid Bloggs = Name isn’t on the summary but appears on the worksheet
James Bloggs = Name isn’t on the summary but appears on the worksheet


I tried the following formula but failed miserably - I couldnt return the name from the worksheet.


Code:
=IF(ISNUMBER(MATCH(B4,'Previous- worksheet'!A:A,0)),"Previous",IF(ISNUMBER(MATCH('Previous- worksheet'!A3,Summary!B:B,0)),"Paid",0))

Regards
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Yo need to have your summary sheet as a lookup table and use a formula something like :-
=IF(ISERROR(VLOOKUP(A1,summary!$A$1:$H$38,2,FALSE)),A1,"Previous")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=IF(ISNUMBER(MATCH(B4,'Previous- worksheet'!A:A,0)),"On Previous","@")&IF(ISNUMBER(MATCH(B4,Summary!B:B,0))," and Summary","@")

Note that "@@" means absent on both.
 

Swifey

Active Member
Joined
Jan 16, 2003
Messages
421

ADVERTISEMENT

I am still having problems in returning the name. I have tried index and matching and the formula’s you kindly suggested. I may not have explained my query perfectly.

I have two identical layout worksheet as follows
One is called Previous which contains the previous months sales.
The other is called Summary which contains the new monthly sales figures.
Both sheets have identical layouts:- Column B contains the name and Column C to F contain sales figures.

What we would like achieve is on the summary sheet is to point out in column A if the customer has had sales in the previous month and state “previous sales” also if possible to list all the customers who have had sales in the previous month but don’t appear in the current summary sales data.


Regards
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Swifey said:
...

What we would like achieve is on the summary sheet is to point out in column A if the customer has had sales in the previous month and state “previous sales”

This part is easy.

Supposing that the data on Summary starts in B2...

In A2 on Summary enter & copy down:

=ISNUMBER(MATCH(B2,Previous!B:B,0))

would tell you that: if the result is 1, the customer is also on Previous; if the result is 0, the customer is not on Previous.

also if possible to list all the customers who have had sales in the previous month but don’t appear in the current summary sales data...

This can't be done from within Summary.

However, you can determine whether a custome on Previous is also on Summary...

In A2 on Previous enter & copy down:

=ISNUMBER(MATCH(B2,Summary!B:B))

1 means: the customer is also on Summary, 0 the opposite.


Is this what you're looking for?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,113
Members
425,261
Latest member
JW00

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