Sales Report
 

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

Sales Report
 

'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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

Sales Report
 

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

"Bob Phillips" wrote:


'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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

Sales Report
 

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" wrote:


'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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

Sales Report
 

Andri,

You can also use DataFilterAdvanced Filter which has an option to filter
unique values only.

--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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" wrote:


'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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

Sales Report
 

Dear Bob,

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

Thank you.

Respectfully,
Andri

"Bob Phillips" wrote:

Andri,

You can also use DataFilterAdvanced Filter which has an option to filter
unique values only.

--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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" wrote:


'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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

Sales Report
 

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

--
__________________________________
HTH

Bob

"Andri" wrote in message
...
Dear Bob,

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

Thank you.

Respectfully,
Andri

"Bob Phillips" wrote:

Andri,

You can also use DataFilterAdvanced Filter which has an option to
filter
unique values only.

--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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" wrote:


'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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

Sales Report
 

Dear Bob,

thank you for your concern and help.

respectfully,
andri

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Andri" wrote in message
...
Dear Bob,

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

Thank you.

Respectfully,
Andri

"Bob Phillips" wrote:

Andri,

You can also use DataFilterAdvanced Filter which has an option to
filter
unique values only.

--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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" wrote:


'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
--
__________________________________
HTH

Bob

"Andri" wrote in message
...
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