Sales Report

[Andri]

Dear All,

I have a database in sheet A. the fields such as Sales Name, Product, Unit
Price, etc.
(E.g. the database contains 100Records, where Sales Name are repetitive
depends on the product sold). More spesific, there are 9 Sales Name in that
databse.

I would like to make the Sales based on Sales Name in Sheet B.

So i need list down those 9 Sales Name using FORMULA (i dont want to use
Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9???

Thank you for your kind attention and help.

Respectfully,
andri

 

[Bob Phillips]

'Sheet B'!A2: ='Sheet A'!A2
'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet
A'!$A$2:$A$20&""),0)),"",
INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet
A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter,
and copy down

 

[Andri]

Dear Bob,

The result is 0. I have use the Evaluate Formula, the final condition before
0 is "=IF(FALSE,#N/A,{"Andri"}. Which is the result should be "Andri" instead
of 0. why?

Please guide further if i revise the scenario as follows:

A1: A6, contains Sales Name as follows: "Andri",
"Jeffrey","Jeffrey","Katarina","Wina","Wina".

How to use the previous formula, so the result as follows B1:B4
"Andri"
"Jeffrey"
"Katarina"
"Wina"

Still think your formula

Thank you for your kind help in this matter.

Respectfully,
Andri

 

[Andri]

Dear Bob,

I got it....thank you for the help.
I miss the first Criteria "'Sheet B'!A2: ='Sheet A'!A2"

Thank you for your excellent formula... i amazed of it.

Respectfully,
andri

 

[Bob Phillips]

Andri,

You can also use Data>Filter>Advanced Filter which has an option to filter
unique values only.

 

[Andri]

Dear Bob,

Yes, but i need the Formula solution in preparing the report.

Thank you.

Respectfully,
Andri

 

[Bob Phillips]

I thought that might be the case, but I just thought to mention it.

 

[Andri]

Dear Bob,

thank you for your concern and help.

respectfully,
andri