Show names that dont appear on the summary sheet

[Swifey]

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.

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

Regards

 

[BrianB]

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")

 

[Swifey]

I never thought of approaching it that way thank you

 

[Aladin Akyurek]

=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]

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]

...

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?

 

[Swifey]

Thank you yes