# Show names that dont appear on the summary sheet

#### Swifey

##### Active Member

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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### BrianB

##### Well-known Member
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

##### Active Member
I never thought of approaching it that way thank you

##### MrExcel MVP
=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
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

##### MrExcel MVP
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?

Thank you yes

Replies
0
Views
91
Replies
1
Views
211
Replies
5
Views
925
Replies
6
Views
337
Replies
7
Views
953

1,186,166
Messages
5,956,335
Members
438,247
Latest member
UZev

### 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.

### Which adblocker are you using?

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

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